Blog
DB2utor

Categories

January 07, 2008

Automate Row Change Timestamp Used for Optimistic Locking

Over the years database administrators have had to deal with locking issues and performance. IBM has
continued to improve DB2 by reducing the number of times applications take locks and or reduce the amount of time a lock needs to be held. The general term for this is optimistic locking. The purpose of optimistic locking is to minimize the time during which a given resource is unavailable for use by other transactions. Locks are obtained immediately before a read operation and released immediately after. Update locks are obtained immediately before an update operation and held until the end of the transaction.

One of the early techniques to curtail holding locks was to add a last update timestamp column to the table and use this value in an update’s where clause to ensure the data hasn’t changed. The success of this technique soon had DBAs changing shop standards to make this column type mandatory on all tables and having all applications perform updates with logic to ensure the row wasn’t changed since it was last read. This was especially important with non-conversational CICS transactions. Non-conversational really just means that a screen of data was presented to the end user and all locks and database work was released.

The problem with non-conversational data entry transactions is that no locks are being held to keep
someone from updating the same row of data you’re about to update. The data is fetched and displayed and all database locks are released. So while one end user is in the process of changing the data, the data may have already been changed by another user transaction. And in that case, if the first user didn’t check the last update timestamp, then that person could end up overlaying data with some inaccurate information. In some cases data may have been deleted, which would cause the update statement to fail because the row no longer exists.

Here’s an SQL sample that might be written to ensure the row hasn’t changed and to set the new update timestamp:

UPDATE table_name
SET column_name = :ws-host-variable
update_ts_column = current timestamp
WHERE key_column = :ws-key
AND update_ts_column = :ws-saved-update-ts

However, the problem with this technique is that you can easily forget to add the update_ts_column in the UPDATE SET statement. When you forget to set this value on your update, then other programs figure that the data hasn’t changed. This can cause data integrity issues and can be difficult to debug. So the burden is put on the application developer to remember to update the last update timestamp column when changing data. To help reduce the exposure the DBAs and/or development team leads may require code reviews. I’ve participated in these reviews and have found some of these problems, particularly with new developers. However, when you’re busy, tired and distracted, anyone can forget to set the last update timestamp. Fortunately, with DB2 9 the column is updated automatically using the column-generated ROW CHANGE TIMESTAMP feature. When a row is inserted and the column isn’t referenced, the timestamp will be set to the current timestamp of the insert. When the row is updated and the column isn’t referenced, then the timestamp is set to the current timestamp at the time of the update.

Defining as-row-change-timestamp column

To implement automatic row change timestamp, the table must contain a timestamp column defined in one of these ways:

DB2 Enforced Value:
column_name TIMESTAMP NOT NULL GENERATED ALWAYS
FOR EACH ROW ON UPDATE AS
ROW CHANGE TIMESTAMP

End User Override Value:
column_name TIMESTAMP NOT NULL GENERATED BY DEFAULT
FOR EACH ROW ON UPDATE AS
ROW CHANGE TIMESTAMP

I recommend using the generated by default version because this provides the most flexibility in your
application design. If you need to override the last update timestamp on a row you can. If you don’t
reference the column then DB2 will generate the value for you.

If you perform an insert or update and reference the column defined as GENERATED ALWAYS, you’ll see this error:

DSNT408I SQLCODE = -798, ERROR: A VALUE CANNOT BE
SPECIFIED FOR COLUMN ROW_CHG_TS WHICH IS DEFINED AS
GENERATED ALWAYS

You can only reference and override the value when you define the column as GENERATED BY DEFAULT.

Migration of existing programs

You cannot use the ALTER TABLE statement to convert a column on an existing table to use the
as-row-change-timestamp-clause. You must either drop the table and recreate with the column defined with the as-row-change-timestamp-clause or ALTER the table and add a new column with the
as-row-change-timestamp-clause. Here’s an example of the ALTER TABLE:

ALTER TABLE CSBP.EMPL_ROW_CHG_TS
ADD COLUMN ROW_CHG_TS TIMESTAMP NOT NULL
GENERATED BY DEFAULT
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP