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
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
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
orRID
) built-in function: This built-infunction
can be used in theSELECT
list or predicates statement. In a predicate, for example,WHERE RID_BIT(tab)=?
, theRID_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 someunique 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 theALTER 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 anINSERT
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
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 theRID_BIT()
andRID()
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
, andDELETE
from the table (for example, use isolation level cursor stability or uncommitted read). - Perform a searched
UPDATE
orDELETE
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 originalSELECT
statement. - If the row has changed, the
UPDATE
operation will fail and the application logic must handle the failure. For instance, the application retries theSELECT
andUPDATE
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