Protecting DB2 against Non Concurrent Application Code

August 14, 2003

Performance is one of the most important factors for any kind of database system. Different companies and especially banks spend a fortune on high-performance hardware because it is still cheaper than efficient application code. This works until the system finally breaks down. DBAs have learned that performance of an application can be influenced by many factors. In this article, I am going to explain and give real-world examples of an application that has a locking problem. The locking problem never happens alone; it is connected with the commit. A badly designed application will hurt database performance as it applies changes and releases locks and claims.

This article covers:

• Transaction Terms
• Locking Terms
• Commit and Locking Parameters
• Performance Problem *Slow Database*
• Database and Application Tuning
• Conclusion

Transaction Terms

• Transaction - a group of operations guaranteed to succeed fully or to be undone. Result of the transaction will never leave the table data in an inconsistent state. Intermediate results are not visible outside the transaction until commit.

• Distributed Transaction - transaction that spans more than one database over different machines. Distributed transaction function is controlled with two-phase commit and Distributed Unit of Work (DUOW) and guarantee integrity.

• Commit - confirm changes made by the completed transaction and releases all obtaining and holding locks on local or distributed resources. Commit functionality is integrated in DB2 code, but it is also left to end users to control as it needed.

Locking Terms

The DB2 engine will handle most locking activities internally. DB2 implements the ANSI standard isolation levels (RR, RS, CS and UR). With DB2 the default behavior is to serialize transactions such that each transaction sees the current committed data.

DB2 supports the following standard isolation levels:

• Repeatable Read (RR) - locks all the rows an application references within a unit of work, so that a SELECT statement within the same unit of work gives the same result each time.

• Read Stability (RS) locks only those rows that an application retrieves within a unit of work and that any row changed by another application process is not read until that process commits the change.

In the above picture, application A cannot read the row locked by application B, until application B commits, submits changes and releases held locks.

• Cursor Stability (CS) locks any row accessed by a transaction of an application while the cursor is positioned on the row. This lock remains in effect until the next row is fetched or the transaction is terminated.

• Uncommitted Read (UR) - allows an application to access uncommitted changes of other transactions and does not lock other applications out of the row it is reading.

When the application makes row changes, locking is made according to the following order: RR, RS, CS and UR. A database engine will try to use the lowest possible level to satisfy a request. If multiple applications try to lock the same record in exclusive mode (for update) then DB2 creates a linked list of these lock requests (first come first served) in memory. Default behavior within DB2 is that a transaction will wait for a lock release to see only committed data. From the viewpoint of read-only applications, UR is the best option for performance, followed by CS, RS and RR.