The second method, referred to as optimistic locking, defers all locking up to the point right before the update is performed. In other words, we will modify the information on the screen without a lock being acquired.
We are optimistic that the data will not be changed by some other user; hence, we wait until the very last moment to find out if we are right.
This locking method works in all environments, but it does increase the probability that a user performing an update will lose.
That is, when that user goes to update their row, they find that the data has been modified, and they have to start over.
One popular implementation of optimistic locking is to keep the old and new values in the application and, upon updating the data, use an update like this:
Update table
Set column1 = :new_column1, column2 = :new_column2, ….
Where primary_key = :primary_key
And decode( column1, :old_column1, 1 ) = 1 And decode( column2, :old_column2, 1 ) = 1
…
Here, we are optimistic that the data doesn’t get changed. In this case, if our update updates one row, we got lucky; the data didn’t change between the time we read it and the time we got around to submitting the update.
If we update zero rows, we lose; someone else changed the data, and now we must figure out what we want to do to continue in the application.
Should we make the end user rekey the transaction after querying the new values for the row (potentially causing the user frustration, as there is a chance the row will have changed yet again)?
Should we try to merge the values of the two updates by performing update conflict resolution based on business rules (lots of code)?
The preceding UPDATE will, in fact, avoid a lost update, but it does stand a chance of being blocked, hanging while it waits for an UPDATE of that row by another session to complete.
If all of your applications use optimistic locking, then using a straight UPDATE is generally OK since rows are locked for a very short duration as updates are applied and committed.
However, if some of your applications use pessimistic locking, which will hold locks on rows for relatively long periods of time, or if there is any application (such as a batch process) that might lock rows for a long period of time (more than a second or two is considered long), then you should consider using a SELECT FOR UPDATE NOWAIT instead to verify the row was not changed, and lock it immediately prior to the UPDATE to avoid getting blocked by another session.
There are many methods of implementing optimistic concurrency control. We’ve discussed one whereby the application will store all of the before images of the row in the application itself. In the following sections, we’ll explore two others, namely:
•\ Using a special column that is maintained by a database trigger or application code to tell us the “version” of the record
•\ Using a checksum or hash that was computed using the original data
Leave a Reply