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.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Latest Articles