Protecting DB2 against Non Concurrent Application Code

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.