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
This article covers:
- Transaction Terms
- Locking Terms
- Commit and Locking Parameters
- Performance Problem *Slow Database*
- Database and Application Tuning
- 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.
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.
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.
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
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.
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.