Improve concurrency with DB2 9.5 optimistic locking

New optimistic locking feature to avoid maintaining long-lived locks

IBM® DB2®, Version 9.5 for Linux®, UNIX®, and Windows® provides enhanced optimistic locking support, a technique for SQL database applications that does not hold row locks between selecting and updating, or deleting rows. Gain an understanding of this enhancement, and learn how applications using this programming model benefit from this enhanced optimistic locking feature and gain improved concurrency.

Pessimistic vs. optimistic locking

Pessimistic locking

A pessimistic locking strategy assumes that the probability is high that another user will try to modify the same row in a table that you are changing. A lock is held between the time a row is selected and the time that a searched update or delete operation is attempted on that row (for example, by using the repeatable read isolation level or lock the table in exclusive mode). The advantage of pessimistic locking is that it is guaranteed that changes are made consistently and safely. The major disadvantage is that this locking strategy might not be very scalable. On a system with many users or with long-living transactions, or when transactions involve a greater number of entities, the probability of having to wait for a lock to be released increases.

Figure 1 illustrates the functioning of pessimistic locking. Transaction 1 reads a specific record and places a lock on that row. It takes some time to decide whether the row will be updated. In the meantime, transaction 2 wants access to that same row, but it has to wait until the lock is released by Transaction 1. Until then, transaction 2 will receive results from its SELECT and can continue with its business logic.

Figure 1. Pessimistic locking concept

Pessimistic locking concept

Optimistic locking
The main problem with a pessimistic locking approach is that transactions have to wait for each other. A way to avoid this is to follow an optimistic locking strategy and assume that it is very unlikely that another user will try to change the same row that you are changing. If the row does change, the update or delete will fail, and the application logic handles such failures by, for example, retrying the select. With this approach, no locks are held between selecting and updating, or deleting a row. But, consequently, this method requires a way to ensure that the data has not changed between the time of being read and being altered. Although more retry logic in the application is needed, the primary advantage of an optimistic locking strategy is that it minimizes the time for which a given resource is unavailable for use by other transactions and thus will be a more scalable locking alternative than pessimistic locking.

Figure 2 illustrates the idea behind optimistic locking. Similar to Figure 1, transaction 1 reads a specific record but then releases its lock. Transaction 2 is now not prevented from retrieving that same row. Before committing the transaction, both transaction 1 and transaction 2 must check whether the row has changed after the previous SELECT. If a change has occurred, the transaction must restart with a new SELECT in order to retrieve the current data. However, if that row has not been changed after the previous SELECT, the data can be successfully updated.

Figure 2. Optimistic locking concept

Optimistic locking concept

Enhanced optimistic locking with DB2 9.5

Optimistic locking in DB2 9.5 improves scalability by minimizing the time for which a given resource is unavailable for use by other transactions. Because the database manager can determine when a row is changed, it can ensure data integrity while limiting the time that locks are held. With optimistic concurrency control, the database manager releases the row or page locks immediately after a read operation.

DB2 9.5 for Linux, Unix, and Windows adds support for easier and faster optimistic locking with no false positives. This support is added using the following new SQL functions, expressions, and features:

  • Row identifier (RID_BIT or RID) built-in function: This built-in function can be used in the SELECT list or predicates statement. In a predicate, for example, WHERE RID_BIT(tab)=?, the RID_BIT equals predicate is implemented as a new direct access method in order to efficiently locate the row. Previously, thus called values optimistic locking with values was done by adding all the selected column values to the predicates and relying on some unique column combinations to qualify only a single row, with a less efficient access method.

  • ROW CHANGE TOKEN expression: This new expression returns a token as BIGINT. The token represents a relative point in the modification sequence of a row. An application can compare the current row change token value of a row with the row change token value that was stored when the row was last fetched to determine whether the row has changed.

  • Time-based update detection: This feature is added to SQL using the ROW CHANGE TIMESTAMP expression. To support this feature, the table needs to have a new generated row change timestamp column defined to store the timestamp values. This can be added to existing tables using the ALTER TABLE statement, or the row change timestamp column can defined when creating a new table. The row change timestamp column’s existence also affects the behavior of optimistic locking in that the column is used to improve the granularity of the row change token from page level to row level, which could greatly benefit optimistic locking applications.

  • Implicitly hidden columns: For compatibility, this feature eases the adoption of the row change timestamp columns to existing tables and applications. Implicitly hidden columns are not externalized when implicit column lists are used. For example a SELECT * against the table does not return a implicitly hidden columns in the result table and an INSERT statement without a column list does not expect a value for implicitly hidden columns, but the column should be defined to allow nulls or have another default value.

Applications using this programming model will benefit from the enhanced optimistic locking feature. Note that applications that do not use this programming model are not considered optimistic locking applications, and they will continue to work as before.

Figure 3 illustrates the functioning of DB2 9.5 optimistic locking. Both transaction 1 and transaction 2 read the same row, including the RID_BIT and the ROW CHANGE TOKEN value. Then transaction 1 updates the row after ensuring that the row has not changed after the previous SELECT by adding a RID_BIT and ROW CHANGE TOKEN predicate to the UPDATE statement. When transaction 2 now tries to update that same row using the same predicate as transaction 1, the row will not be found because the value of the ROW CHANGE TOKEN has changed regarding to the UPDATE of transaction 1. Transaction 2 has to start a retry in order to retrieve the current data.

Figure 3. Enhanced optimistic locking with DB2 9.5
DB2 optimistic locking concept

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 be created with a ROW CHANGE TIMESTAMP column or altered to contain the ROW CHANGE TIMESTAMP column.

     CREATE TABLE EMPLOYEE (EMPNO CHAR(6) NOT NULL,
              ......
              ROWCHGTS TIMESTAMP NOT NULL
              GENERATED ALWAYS
              FOR EACH ROW ON UPDATE AS
              ROW CHANGE TIMESTAMP)

     ALTER TABLE EMPLOYEE ADD COLUMN
              ROWCHGTS TIMESTAMP NOT NULL
              GENERATED ALWAYS
              FOR EACH ROW ON UPDATE AS
              ROW CHANGE TIMESTAMP

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.

Usage scenario

An employee got a new job responsibility and is now working for another department. Two managers (the manager of the old department called Manager1 and Manager2 from the new department) are using a personnel administration application to update employee records in the EMPLOYEE table in the SAMPLE database. There is a possibility that both managers try to update the same employee record at the same time.

The EMPLOYEE table contains a implicitly hidden ROW CHANGE TIMESTAMP column and is accessed by Manager1 and Manager2 simultaneously. Manager1 selects the data from the EMPLOYEE table and later tries to update the same data. However, between his select and his update, Manager2 updates the same data. Manager2’s update is successful, but Manager1’s update fails.

Result from the SELECT (Manager1 and Manager2)

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME PHONENO ROW CHANGE TIMESTAMP
x’0400400100000000
0000000000FA9023′
74904229642240 000010 CHRISTINE HAAS 3978 0001-01-01
00:00:00.000000
x’0500400100000000
0000000000FA9023′
74904229642240 000020 MICHAEL THOMPSON 3476 0001-01-01
00:00:00.000000
x’0600400100000000
0000000000FA9023′
74904229642240 000030 SALLY KWAN 4738 0001-01-01
00:00:00.000000

UPDATE statement (Manager2)


      UPDATE EMPLOYEE SET
            (FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092')
            WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND
            ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

Result from the UPDATE (Manager2)

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME PHONENO ROW CHANGE TIMESTAMP
x’0400400100000000
0000000000FA9023′
141285645885181032 000010 CHRISTINE HAAS 1092 2007-12-2011:55:45.593000
x’0500400100000000
0000000000FA9023′
74904229642240 000020 MICHAEL THOMPSON 3476 0001-01-01
00:00:00.000000
x’0600400100000000
0000000000FA9023′
74904229642240 000030 SALLY KWAN 4738 0001-01-01
00:00:00.000000

UPDATE statement (Manager1)

      UPDATE EMPLOYEE SET
            (FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092')
            WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND
            ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

Result from the UPDATE (Manager1)

The update from Manager1 is unsuccessful. Since the ROW CHANGE TOKEN has changed by the UPDATE of Manager2, the ROW CHANGE TOKEN predicate of Manager1’s UPDATE statement fails while comparing the token retrieved at the point of the SELECT and the current value after having been updated by Manager2’s application. Thus the UPDATE fails to find the specified row. A message “SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000” is returned.

More detailed information and usage scenarios

More details can and usage scenarios be found in an IBM DeveloperWorks article “Improve concurrency with DB2 9.5 optimistic locking” at http://www.ibm.com/developerworks/db2/library/techarticle/dm-0801schuetz