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 retry 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 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.
- 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 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.