Optimistic Locking of new features in DB2

This article will introduce you to the new Optimistic Locking feature of DB2 database 9, which enables DB2 to retrieve rows in a specific period of time and understand their last modification time, with good auxiliary effect.

DB2 for z/OS customers are adding new and sophisticated data warehouse tasks to traditional OLTP.

DBAs are challenging to support multiple systems and respond to application developers and supervisors who often lack insights. Fortunately, DB2 9 for z/OS provides an entirely new Optimistic Locking feature that improves system performance-and perhaps DBA (and application developers) is optimistic.

Database locking is an essential overhead and a core component of all DBMS. The lock maintains data integrity by preventing multiple transactions from changing the same data at the same time. However, the cost of obtaining and maintaining database locks may be very high, especially for complex systems, applications, or transactions.

Optimistic Locking now uses the new features defined in the DB2 table to reduce deadlocks, reduce the overall locking overhead, and improve system and application performance.

To use the new Optimistic Locking feature, you must use the new parameters (generated always, for each row on update, as row change timestamp) to define the new row change timestamp column in the DB2 table, as follows:

Listing 1. New features with Optimistic Locking

Create table beulke. PRODUCT_TBL (
PROD_NBR integer not null,
PROD_INVENTORY integer not null,
PROD_LAST_UPD NOT NULL
GENERATED ALWAYS
AS ROW CHANGE
TIMESTAMP );

These parameters tell DB2 to always fill in and pay special attention to timestamps and tables. The last updated timestamp has been embedded in some applications for many years; IBM now acknowledges and improves this technology.

These new features enable DB2 to retrieve rows within a specific period of time and understand their last modification time. DB2 not only pays attention to the row timestamp information, but also the record ID (RID) and change Mark information. By focusing on Row attributes, applications and users can query the database by timestamp and obtain a specific row or group of rows according to the WHERE timestamp subsentence standard.

The new column feature allows most applications to rebound, reducing the lock overhead. It also locks the configuration file from Repeatable Read (RR), Read Stability (RS), or Cursor Stability (CS) to Uncommitted Read (UR ). Uncommitted Read avoids database locking. Applications can maintain the integrity of database transactions by using the new timestamp column in the application update SQL statement. The new timestamp column provides the row Timestamp and record ID (RID), which DB2 can use to verify that no other application has changed the required data.

Another DB2 9 SQL phrase, SKIP LOCKED DATA, helps avoid locking because it does not use incompatible locks to retrieve or affect DATA rows. This phrase can be used in SQL statements such as SELECT, UPDATE, and DELETE to avoid deadlocks. Exercise caution when using isolated UR and skip locked data phrases. Although these technologies can significantly reduce the number of locks and improve performance, you need to thoroughly understand your applications. Before using this performance improvement technology, you should study each application in detail and read the DB2 manual to view all the details. Since these technologies can significantly reduce deadlocks and lock overhead-especially in data sharing environments, it is worth studying and implementing this.