NYPL Labs

Lets say Alice and Bob are using a web interface to maintain data stored in a back end database.

First Alice brings up record 42 and begins reviewing it for potential corrections.

While she is working, Bob comes along, signs in at a different work station and reads the same record 42. He notices that the field named “B” says “Kilroy was here”, so he changes that to say “Bob was here” and saves the record back to the database.

Working carefully, Alice soon realizes that she should change the data in fhe field named “A”. She makes the change and saves the record.

A little while later, Bob’s boss, Zelda, brings up record 42. There in field “B” she sees “Kilroy was here”. Bob swears he changed that, Zelda is skeptical. Alice says “Hey, my edits are just fine, whats the fuss about?”

What happened?

Alice and Bob both saw the same original record data, and brought separate copies of the data into their local computer environments.

When Bob saved his work, he updated the copy in the database, but did nothing to Alice’s copy that ALice has in her local working storage.

When Alice saved her edits, she also restored the original (Kilroy was here) version of the data in field B which she did NOT edit.

When a record has multiple fields, as is the usual case, it is very common for the update code to be implemented the following steps:

1. Acquire the original record and pass all fields to the end user for update

2. When the user is finished, replace each and every field in the database with the version sent back to the updating program by the end user, whether the end user has in fact “dirtied” (changed) the fields or not.

Doing it this way in effect implements a contract with the user saying “I’ll give you the original data, You change it to the way you want it to be, and I’ll put all that back into the database that way. This relieves the update program from having to “know” which fields the user updated and which were left unchanged. For the sake of simplicity, all fields are written back to the database.

“Aha!”, I hear you say. “If only the update program had kept track of which fields were clean and which were dirty (i.e. which had been changed by ALice and which not) all would be well”.

Close but no cigar. In this case, where Bob had changed field B but ALice had changed field A, we would have escaped danger.

But suppose Bob had changed field A. Suppose field A had said “Kilroy was here” and Bob had changed that to “Bob was here”. Alice’s original version still say’s “Kilroy was here”. Had she changed that to “Alice was here”, the final version of the record would have said “Alice was here” and Zelda would have seen “Alice was here” when she expected to see “Bob was here”. Bob cant win.

The real problem is that by the time Alice came to the point where she wanted to commit her changes, her working copy had become obsolete, because Bob had refreshed the database without her knowing it.

The simple solution is to lock the record when it is selected for Update.

In this case (Called Pessimistic locking), we’ld lock the record when Alice reads it.

When Bob comes along, he’ll find that he cant aquire a lock on the same record, because Alice beat him to it. Worse yet, we said that Alice was working slowly and carefully and would hold the record lock for many minutes, so the “window” during which that record would be locked could be relatively long. So Bob could keep trying to get the record for that many minutes without success. Bob still cant win. He wont lose his edit, but now he’s got to wait for ALice to finish with it.

Many web implementation favor the following “Optimistic” locking scheme:

1. Store something with the record which indicates its timeliness. A “date last updated” will do, but some systems like to store a special “system commit number”. For this to work, each time a record is updated, the “date last updated” field or the System commit number field must be updated as well. The date field is set to the current date and time, the SCN field is an integer which is incremented for each update.

2. When Alice reads the record, she also reads the “As of” information (the date or the SCN).

3. When Bob reads the record, he also reads the “As of” information.

4. When Bob saves his data to the database, the update routine locks the record, and reads the “as of” information. If Bob’s “as of” data matches the current database version of the as of value, (in this case it does), then the update routine performs the update and releases the lock.

If the “as of data” had been a mis match, the update program would have simply released the lock without doing the update, and then would have informed Bob that he’s lost this round, he cant update his record because he was working from an old snapshot of the data.

(That doesnt happen to Bob in the scenario we’ve outlined because he was the first to change the data)

5. When ALice moves to save her data, she’s the one to get the “Sorry, too old” message. because when Bob saved his data, the “As of” information in the database became different from the “As of” information that Alice has stored in her local working memory.

Now ALice needs to re-aquire the record to see the latest and greatest data, to decide if it still needs updating.

Since ALice held the record longer, it is arguably fairer that she has a better chance of running into the “snapshot too old” error than Bob who quickly aquired the record, did his edit and saved the data.

There are some tools (Oracle’s Sql*Forms, for example) that do optimistic locking for you, behind your back. But if you are using some lower level tools, the onus is on you the developer to understand the locking issues and to implement an effective locking strategy.

Fortunately, “Optimistic Locking” is easy to implement and well suited to most web applications.