DB2 Version 9.7 for Linux, UNIX, and Windows

RID_BIT() and RID() built-in function

The RID_BIT() and ROW CHANCE TOKEN can be selected for every row in a table. The SELECT can occur at any isolation level that the application requires.

The application can modify the same (unchanged) row with optimistic locking by searching on both:

The RID_BIT() to directly access (not scan) the target row
The ROW CHANGE TOKEN to ensure this is the same unchanged row

This update (or delete) can occur at any point after the select, within the same unit of work, or even across connection boundaries; the only requirement is having obtained the two values above for a given row at some point in time.

Optimistic locking is used in the "WebSphere-Oriented Programming Model". For example, Microsoft .NET uses this model to process SELECT statements followed by UPDATE or DELETE statements as follows:

Connect to the database server and SELECT the desired rows from a table
Disconnect from the database, or release the row locks so that other applications can read, update, delete, and insert data without any concurrency conflicts due to locks and resources held by the application (isolation "Uncommited Read" allows higher concurrency AND assuming other applications COMMIT their update and delete transactions, then this optimistic locking application will read the updated values and the optimistic searched update/delete will succeed)
Perform some local calculations on the SELECTed row data
Reconnect to the database server, and search for UPDATE or DELETE on one or more particular targeted rows (and, if the target row has changed, handle failed UPDATE or DELETE statements)

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.

RID_BIT() and RID() built-in function features

Following are the new features that will be implemented for enhanced optimistic locking and for update detection:

RID_BIT( <table designator> )

A new built-in function that returns the Record identifier (RID) of a row as VARCHAR(16) FOR BIT DATA.

Note: DB2® for z/OS® implements a built-in function RID with a return type of BIGINT, but that is not large enough for Linux, UNIX, and Windows RIDs. For compatibility, this RID() built-in function returns BIGINT, in addition to RID_BIT().

This RID() built-in function does not work in partitioned database environments, and does not include table version information. Otherwise, it works the same as RID_BIT. You should use it only when coding applications that will be ported to z/OS servers. Except where necessary, this topic refers only to RID_BIT.

RID_BIT() 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 FOR <table designator>

A new expression that 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.

ROW CHANGE TIMESTAMP column

A GENERATED column with default type of TIMESTAMP which can be defined as either:

    GENERATED ALWAYS FOR EACH ROW ON UPDATE 
    AS ROW CHANGE TIMESTAMP

or (suggested only for data propagation or unload and reload operations):

    GENERATED BY DEFAULT FOR EACH ROW ON UPDATE 
    AS ROW CHANGE TIMESTAMP

The data in this column changes every time the row is changed. When this column is defined, the ROW CHANGE TOKEN value will be derived from it. Note that when GENERATED ALWAYS is used, the database manager ensures that this value is unique within a database partition or within table partition to ensure that no false positives are possible.

To use the first two elements, RID_BIT and ROW CHANGE TOKEN, no other changes are need to the database schema. Note, however, that without the ROW CHANGE TIMESTAMP column, the ROW CHANGE TOKEN is shared by every row on the same page. Updates to any row on the page can cause false negatives for other rows stored on the same page. With this column, the ROW CHANGE TOKEN is derived from the timestamp and is not shared with any other rows in the table or database partition. See Granularity of row change tokens and false negatives.

Time-based update detection and RID_BIT(), RID() functions

The ROW CHANGE TIMESTAMP expression returns a timestamp value that represents the time when the row in the table identified by the table designator was last changed. Despite the inter-relation of the RID_BIT() and RID() built-in function and the time-based update detection feature, it is important to note that the usage of ROW CHANGE TOKEN and ROW CHANGE TIMESTAMP expressions are not interchangeable; specifically, that ROW CHANGE TIMESTAMP expression is not part of the optimistic locking usage.