Tags

, , , , ,

Optimistic Locking vs. Pessimistic Locking

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?

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.

 

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 (ROW CHANGE TIMESTAMP column). 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.

There are further implications and considerations, but this is enough of a dissertation for now 🙂

 

When optimistic locking will be used

Well, for programs that use updateable static scrollable cursors DB2 can use optimistic locking as long as the plan/package is bound specifying ISOLATION (CS). If you have this situation, DB2 will deploy optimistic locking to reduce the duration of locks between consecutive FETCH operations and between fetch operations and subsequent positioned UPDATE or DELETE operations.

Remember, though, DB2 cannot use optimistic concurrency control for dynamic scrollable cursors. With dynamic scrollable cursors, the most recently fetched row or page from the base table remains locked to maintain position for a positioned UPDATE or DELETE.

 

Enabling optimistic locking

Since the new SQL expressions and attributes for optimistic locking can be used with no DDL changes to the tables involved, you can easily try optimistic locking in your test applications.

Note that without DDL changes, optimistic locking applications may get more false negatives than with DDL changes. An application that does get false negatives may not scale well in a production environment because the false negatives may cause too many retries. Therefore, to avoid false negatives, optimistic locking target tables should be either:

  • Created with a ROW CHANGE TIMESTAMP column
  • Altered to contain the ROW CHANGE TIMESTAMP column

These are a basic steps to be performed in order to enable optimistic locking support in your applications:

  • In the initial query, SELECT the row identifier (using the RID_BIT() and RID() built-in function) and row change token for each row that you need to process.
  • Release the row locks so that other applications can SELECT, INSERT, UPDATE, and DELETE from the table (for example, use isolation level cursor stability or uncommitted read).
  • Perform a searched UPDATE or DELETE on the target rows, using the row identifier and row change token in the search condition, optimistically assuming that the unlocked row has not changed since the original SELECT statement.
  • If the row has changed, the UPDATE operation will fail and the application logic must handle the failure. For instance, the application retries the SELECT and UPDATE operations.

After running the above steps:

  • If the number of retries performed by your application seems higher than expected or is desired, then adding a row change timestamp column to your table to ensure that only changes to the row identified by the RID_BIT function will invalidate only the row change token, and not other activity on the same data page.
  • To see rows that have been inserted or updated in a given time range, create or alter the table to contain a row change timestamp column. This column will be maintained by the database manager automatically and can be queried using either the column name or the ROW CHANGE TIMESTAMP expression.
  • For row change timestamp columns only, if the column is defined with the IMPLICITLY HIDDEN attribute, then it is not externalized when there is an implicit reference to the columns of the table. However, an implicitly hidden column can always be referenced explicitly in SQL statements. This can be useful when adding a column to a table can cause existing applications using implicit column lists to fail.

For More details about enabling optimistic locking, Please follow the link below

http://www.ibm.com/developerworks/data/library/techarticle/dm-0801schuetz/

 

You may also like to look at:

Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
DB2 SQL Query to read COMP (COBOL) data stored in CHAR column
DB2 Performance – Using SET vs SYSDUMMY1 table
DB2 Performace – Predicates Processing Order
DB2 External Stored Procedures
DB2 UPDATE QUERY – UPDATE TABLE1 FROM TABLE2 DATA
DB2 PERFORMANCE ISSUE (When using EXISTS)
HOW DOES DB2 INTERNALLY (PHYSICALLY) STORE THE DATA
HOW DO YOU FIND WHO HAS ACTUALLY LOADED THE DB2 TABLE RECENTLY
TERMINATE A STOPPED DB2 UTILITY
SELECT UNIQUE RECORDS USING “GROUP BY”
DB2 and SQL INTERVIEW QUESTIONS
DB2 TIPS
Optimistic Locking vs. Pessimistic Locking
DB2 BIND OPTIONS and ISOLATION
Is SCHEMA name necessary for DYNAMIC QUERY
DB2 SQL – REPLACE CHARACTERS WITH ACCENTS (NON ENGLISH ALPHABETS)
Advertisements