Analyzing data for DB2 LUW Locktimeout
This document explains how to debug a DB2 LOCKTIMEOUT- SQL0911N RC68, using a db2cos.rpt file.
SQL0911N The current transaction has been rolled back because of a deadlock or timeout.
Reason code "68". SQLSTATE=40001
The symptoms for a LOCKTIMEOUT may include the following:
- Applications receive SQL0911N RC68
- Infrequent commits within Unit Of Works leading to locking issues with the database
- Snapshot for applications show that several applications are in lock-wait mode
Resolving the problem
In most cases, DB2 LOCKTIMEOUT is caused due to the way the applications were developed. DB2 uses Cursor Stability as the default isolation level and its recommended that you review the lock-type compatibility in the below link when developing/coding your applications.
In the db2cos.rpt you will find valuable information on the transactions that actually timed out due to a lock-wait situation and also the erring dynamic SQL statements.
In the following section of the db2cos.rpt report, you can match the lock-wait status (indicated with W*) to a transaction handle; which can then be matched with the application handle and AnchId/StmtUID; leading to the dynamic statement section showing us the SQL statements that caused the lock-time outs. From this report we can see that a select statement is requesting for a NS lock while an insert statement is holding an X lock on the same row in table TEST. From the lock type compatibility table we know that an X lock is not compatible with a NS lock and hence the lock timeout occurred.
If you need further assistance with debuging a db2cos.rpt report, please contact DB2 Support at 1-800-IBM-SERV
More support for:
DB2 for Linux, UNIX and Windows
Performance - Lock-Latch
Software version: 8, 9.1, 9.5, 9.7
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Software edition: Enterprise Server, Express, Personal, Personal Developer's, Workgroup Server
Reference #: 1405231
Modified date: 01 October 2009