What is Optimistic Locking vs. Pessimistic Locking

What is Optimistic Locking vs. Pessimistic Locking

from http://www.dbasupport.com/forums/archive/index.php/t-7282.html

 

These are methodologies used to handle multi-user issues. How does one handle the fact that 2 people want to update the same record at the same time?

1. Do Nothing
- User 1 reads a record
- User 2 reads the same record
- User 1 updates that record
- User 2 updates the same record
User 2 has now over-written the changes that User 1 made. They are completely gone, as if they never happened. This is called a 'lost update'.

2. Lock the record when it is read. Pessimistic locking
- User 1 reads a record *and locks it* by putting an exclusive lock on the record (FOR UPDATE clause)
- User 2 attempts to read *and lock* the same record, but must now wait behind User 1
- User 1 updates the record (and, of course, commits)
- User 2 can now read the record *with the changes that User 1 made*
- User 2 updates the record complete with the changes from User 1
The lost update problem is solved. The problem with this approach is concurrency. User 1 is locking a record that they might not ever update. User 2 cannot even read the record because they want an exclusive lock when reading as well. This approach requires far too much exclusive locking, and the locks live far too long (often across user control - an *absolute* no-no). This approach is almost *never* implemented.

3. Use Optimistic Locking. Optimistic locking does not use exclusive locks when reading. Instead, a check is made during the update to make sure that the record has not been changed since it was read. This can be done by checking every field in the table.
ie. UPDATE Table1 SET Col2 = x WHERE COL1=:OldCol1 AND COl2=:OldCol AND Col3=:OldCol3 AND...
There are, of course, several disadvantages to this. First, you must have already SELECTed every single column from the table. Secondly, you must build and execute this massive statement. *Most* people implement this, instead, through a single column, usually called timestamp. This column is used *for no other purpose* than implementing optimistic concurrency. It can be a number or a date. The idea is that it is given a value when the row is inserted. Whenever the record is read, the timestamp column is read as well. When an update is performed, the timestamp column is checked. If it has the same value at UPDATE time as it did when it was read, then all is well, the UPDATE is performed and *the timestamp is changed!*. If the timestamp value is different at UPDATE time, then an error is returned to the user - they must re-read the record, re-make their changes, and try to update the record again.

- User 1 reads the record, including the timestamp of 21
- User 2 reads the record, including the timestamp of 21
- User 1 attempts to update the record. The timestamp in had (21) matches the timestamp in the database(21), so the update is performed and the timestamp is update (22).
- User 2 attempts to update the record. The timestamp in hand(21) *does not* match the timestamp in the database(22), so an error is returned. User 2 must now re-read the record, including the new timestamp(22) and User 1's changes, re-apply their changes and re-attempt the update.