DB2 provides a better way to achieve optimistic locking

In IBM I 6.1, DB2 provides users with a new feature (row change column) that can improve their algorithms for applications that need to use optimistic locks. Optimistic locking is a widely used technology based on web database applications that are sure to read the data but are only likely to update the read data, but will not maintain a persistent connection to the database. Because applications do not maintain a persistent connection, users cannot rely on row-level locks to maintain data integrity for those rows that are subsequently updated. A common way to achieve optimistic locking is to record all the values of the corresponding columns for the row that the application might update later, and then use these values in the next UPDATE statement as a condition.

SELECT * from SCHEMA. TEST
C1 C2 C3 ...
10 12 33 ...
... UPDATE SCHEMA. TEST SET C1 = WHERE c1=10 and c2=12 and c3=33 ...

If another user has made an update to the current row, the UPDATE statement will fail because the selection criteria cannot match the value of the new row that was updated. Therefore, DB2 will not overwrite any previous changes. With optimistic locking, users of many applications can access data in parallel from the database while allowing a single update statement to occur without fear of loss of data or other concurrent users overwriting the updated data.

Storing each attribute of a row is inconvenient for the user, but the application needs a way to ensure that the current user does not change the value of any column in the row when other users make changes to the data. Fortunately, starting with the DB2 for I 6.1, there is an easier way to determine whether the database records have changed since the application read the record to the current date.

This is the DB2 for I of the new features optimistic locking really prominent place. Users can use this support using the new PREPARE property settings or the client application programming interface settings. With this new technique, DB2 will automatically add two columns in the returned result set, which uniquely identify the current row and the time of the last update.

The application does not have to assign a value for this update Time column, and the value of this particular column is automatically updated each time the row is updated.

Column row Change support

From version 6.1, an application can request that the row change column be returned in the result set of any query. The row change column consists of two separate binary columns, namely the row-changing feature (RCT) and the row ID (RID). Applications can decide whether to guarantee uniqueness through these two columns, or, in most cases, are sufficient to guarantee uniqueness (which will be explained separately below). These columns can be explicitly added to the list of selection statements by the application, or it is easier to add by passing in a property that prepares the statement:

STMT = ' SELECT * from SCHEMA. TEST ';
ATT = ' with ROW change COLUMNS ALWAYS DISTINCT ';
EXEC SQL DECLARE C1 CURSOR for S1;
EXEC SQL PREPARE S1 Attributes:att from:stmt
EXEC SQL OPEN C1;

Some application programming interfaces, such as JAVA Database connectivity (JDBC) and iaccess. NET provider, also provide some statement properties that require DB2 to add these columns to the prepared statement, so that the query itself does not have to change.

Row ID and Row change characteristics

A RID is a DB2 for I BIGINT type of value that is automatically added to each row. The RID identifies the position of the row in the table.

RCT is a derived value. If the application requires that the values generated by RCT and RIDs must be unique, then the table must have one column that is a timestamp type, and has a for every row on UPDATE as ROW change TIMESTAMP property:

ALTER TABLE TEST ADD COLUMN

RTS TIMESTAMP not NULL

Generated ALWAYS

For each row in UPDATE as ROW change TIMESTAMP

Implicitly HIDDEN

When a table contains columns of this particular timestamp type, the value of the RCT returned by each row is derived from the value stored in this particular column. The application does not need to assign a value to this column, and the value of the RCT column is automatically updated each time the row is updated. Like other columns, this column can be defined as a hidden column in versions 6.1 and later, which means that the column is not returned when a SELECT * query is made on the table.

Note that even with this additional column, the value stored by RCT itself is not guaranteed to be unique, because in some cases there may be multiple rows that return the same timestamp. However, the inclusion of RCT and RID two is guaranteed to be unique.

Some applications do not absolutely require the uniqueness of the value of the row change column returned, so you do not have to add a row to the table to the timestamp type column, and RCT will be generated internally in DB2.

Determining whether a table should increase the number of special timestamp types, one factor to consider is the amount of periodic update operations. For a table with heavy update activity, adding a column with a row change timestamp type may be worthwhile, reducing the omission that the application must handle (this is where the update operation for a row ends without being updated). On the other hand, if the update operation rarely occurs, you can save disk space and replace the RCT generated by the internal algorithm.

When you use DB2 to prepare properties, the RID and RCT are automatically added to the end of each row with the names of columns Qrid and Qtoken respectively, when the data is fetched from the cursor:

STMT = ' SELECT * from SCHEMA. TABLE ';

ATT = ' with ROW change COLUMNS ALWAYS DISTINCT ';

EXEC SQL DECLARE C1 CURSOR for S1;
EXEC SQL PREPARE S1 Attributes:att from:stmt
EXEC SQL OPEN C1; EXEC SQL FETCH C into ...

C1 C2 C3 ... Q TOKEN q RID

10 12 33 ... # $ $

... EXEC SQL UPDATE TEST SET C1 = WHERE ROW Change
TOKEN (Shema. TABLE) =#### and RID (SCHEMA. TABLE) =$$$$

Note: RCT and RIDs are returned as BIGINT types.