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:
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:
In the above picture, application A cannot read the row locked by application B, until application B commits, submits changes and releases held locks.
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.