Optimistic Locking or “Dude, Where’s My Data? ”
May 9, 2008 by Lee HorowitzFiled in Uncategorized
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.
