Other Topics > Smart Access 1996-2006 > Sep-2002 > Record Locking and<br/> Updating Efficiencies

Record Locking and Updating Efficiencies

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Sep-2002 >

Record Locking and Updating Efficiencies

 

Peter Vogel

 

Creating an effective Update statement in SQL can make all

the difference in what data gets saved in a multi-user

environment and what sort of performance you’ll get from

your application. Peter Vogel discusses record locking and

SQL statements.

 

READER, beware! This article is a long, involved,

detailed, and tremendously boring discussion of

record locking. However, while this is more about

record locking than you ever wanted to know, I’ll also

discusses the most efficient way to handle updates in a

multi-user environment.

 

The problem

The problem with updates in a multi-user environment

occurs when two users access the same record. For my

first example, I’ll assume a simple record with three fields

(A, B, C) with the numbers 1, 2, and 3 in each field:

 

A B C

1 2 3

 

My first user retrieves the record and changes field A

from 1 to 4. While my first user is doing this, a second

user retrieves the record and changes field C from 3 to 5.

At this point, both users’ fingers are reaching for the

button that will start the updates. The second user is

faster and updates the record in the database:

 

A B C

1 2 5

 

A millisecond later, the first user presses the update

button and changes the same record. The result is:

 

A B C

4 2 3

 

As you can see, the second user’s change to field C

has been lost. The change was lost because the SQL

statement behind the button looks like this:

 

Update tblExample

Set A = Me.txtField1,

B = Me.txtField2,

C = Me.txtField3

Where PrimaryKeyField = Me.txtCustId

 

The SQL statement updates every field in the record,

regardless of whether or not the field was changed. How

can this problem be avoided?

 

Locking is evil

One solution is to lock records as you retrieve them.

Locking schemes basically fall into two patterns:

pessimistic and optimistic.

 

Pessimistic locking assumes that having multiple

users updating the same record is a frequent occurrence.

To prevent two users making changes at the same time,

the application attempts to lock a record against changes

when the user retrieves it. When a record is locked and

another user tries to access the record, the database

system or the application may simply have the user wait

(in hopes that the record will be unlocked), return an error

to the user, or display a read-only version of the record.

Optimistic locking assumes that it’s very unlikely that

two or more users will ever access the same record at the

same time: Locking the record when it’s retrieved is

unnecessary. In optimistic locking, a record is locked for

only the time that it takes to write out changes to it.

Records are locked when being written just to make sure

that two users don’t try to write the record at the same

time and that a user doesn’t retrieve a record with some

fields updated and other fields not yet updated. When a

user finds a record locked in these situations, the system

typically waits until the write operation is complete and

then accesses the record. Remember, though, that the

assumption is that two users will never access the same

record at the same time, so this situation should

never occur.

 

In optimistic-locking systems, during the update, the

application can check to see if the record has been

changed since it was retrieved. If the record has been

changed, the application can abandon the update and

notify the user (Access works this way, for instance).

Pessimistic locking is certainly the safe choice, but it

incurs at least three costs:

 

1. Pessimistic locking isn’t free. Attempting to lock a

record, locking a record, and notifying other

processes that a record is locked all take processing

time. Maintaining lock status information also

requires some space in memory, on disk, or both (for

 

Read More Here:

Record Locking and Updating Efficiencies