Analyzing data for DB2 LUW Locktimeout

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

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.

DB2 V9.1 Lock Type Compatibility Table

AIX/Solaris/Linux/HP-UX/Windows

When a db2pdcfg -catch (or a db2pd -catch in DB2 V8.x) is set and you hit a SQL911 RC68 - LOCKTIMEOUT, a db2cos.rpt will be generated in the ~home/sqllib/db2dump directory. You should also be able to find messages in the db2diag.log similar to the following:



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.

For example:

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