Optimistic locking

Optimistic locking is a technique for SQL database applications that does not hold row locks between selecting and updating or deleting a row.

The application is written to optimistically assume that unlocked rows are unlikely to change before the update or delete operation. If the row does change, the update or delete will fail and the application logic handles such failures by, for example, retrying the select. One advantage of optimistic locking is improved concurrency, because other applications can read and write that row. In a three tier environment where business transactions have no correlation to database transaction, the optimistic locking technique is used, because locks cannot be maintained across the business transaction.

However, optimistic locking by values has some disadvantages:
  • Can result in false positives without additional data server support, a condition when using optimistic locking whereby a row that is changed since it was selected cannot be updated without first being selected again. (This can be contrasted with false negatives, the condition whereby a row that is unchanged since it was selected cannot be updated without first being selected again.)
  • Requires more re-try logic in applications
  • It is complicated for applications to build the UPDATE search conditions
  • It is inefficient for the Db2® server to search for the target row based on values
  • Data type mismatches between some client types and database types, for example, timestamps, prevent all columns from being used in the searched update
The support for easier and faster optimistic locking with no false positives has the following new SQL functions, expressions, and features:
  • Row Identifier (RID_BIT or RID) built-in function
  • ROW CHANGE TOKEN expression
  • Time-based update detection
  • Implicitly hidden columns

Db2 applications can enable optimistic locking by values by building a searched UPDATE statement that finds the row with the exact same values that were selected. The searched UPDATE fails if the row's column values have changed.

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.

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, so 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.
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 RID_BIT() and ROW CHANGE TOKEN. To support this feature, the table needs to have a new generated column defined to store the timestamp values. This can be added to existing tables using the ALTER TABLE statement, or the column can be defined when creating a new table. The column's existence, also affects the behavior of optimistic locking in that the column if it is used to improve the granularity of the ROW CHANGE TOKEN from page level to row level, which could greatly benefit optimistic locking applications. This feature has also been added to Db2 for z/OS®.
Implicitly hidden columns:
For compatibility, this feature eases the adoption of the RID_BIT and ROW CHANGE TOKEN 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
  • 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.
Note: Refer to the Db2 Glossary for the definition of optimistic locking terms, such as optimistic concurrency control, pessimistic locking, ROWID, and update detection.