Enhancements to optimistic concurrency control and update detection

Enhancements to optimistic concurrency control provide a faster, more scalable locking alternative to database locking for concurrent data access. Also, a related enhancement provides a mechanism to detect recent (daily, weekly, or monthly) database updates.

Optimistic locking

Optimistic locking minimizes the time for which a given resource is unavailable for use by other transactions.

Because DB2® can determine when a row was changed, it can ensure data integrity while limiting the time that locks are held. With optimistic concurrency control, DB2 releases the row or page locks immediately after a read operation. To ensure data integrity, DB2 also releases the row lock after each FETCH operation and takes a new lock on a row only for a positioned update or delete.

A new row change timestamp column that you specify on the CREATE TABLE and ALTER TABLE statement lets you implement optimistic concurrency control. The column is defined with one of two options:
  • NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
  • NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP.
DB2 maintains the contents of the row change timestamp column. When you want to use this change token as a condition when making an update, you can specify an appropriate condition for this column in your WHERE clause.

Daily, weekly, and monthly update detection

Database administrators benefit from knowing the volume of updates within specific time ranges so that they can plan for data replication, create auditing scenarios, and so on.

A new expression, ROW CHANGE, returns a token or a timestamp that represents the last change to a row. Now, an application has the following options:
  • Determine when a row was last changed (or changed within a range of dates or number of days) by using the ROW CHANGE TIMESTAMP expression
  • Return a token as a BIGINT (big integer) value that represents a relative point in the modification sequence of a row by using the ROW CHANGE TOKEN expression