Troubleshooting
Problem
DB2 database deadlocks and SQLCODE -911 occur in the PersistentLockManager calls.
Symptom
The following exception is written in the profileRoot/profileName/logs/serverName/SystemOut.log file:
PersistentLoc E com.ibm.wbiserver.sequencing.lkmgr.PersistentLockManagerBean unlockAndGrantNext Error in unlock and grant next com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2
at com.ibm.db2.jcc.b.yg.b(yg.java:3046)
at com.ibm.db2.jcc.c.eb.h(eb.java:268)
at com.ibm.db2.jcc.c.eb.a(eb.java:229)
at com.ibm.db2.jcc.c.eb.c(eb.java:33)
at com.ibm.db2.jcc.c.u.a(u.java:34)
at com.ibm.db2.jcc.c.j.Ib(j.java:257)
at com.ibm.db2.jcc.b.yg.Q(yg.java:2896)
at com.ibm.db2.jcc.c.d.g(d.java:1444)
at com.ibm.db2.jcc.b.eb.a(eb.java:191)
at com.ibm.db2.jcc.b.yg.c(yg.java:274)
at com.ibm.db2.jcc.b.yg.next(yg.java:238)
at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:2468)
at com.ibm.wbiserver.sequencing.lkmgr.PersistentLockManagerBean
.unlockAndGrantNext(PersistentLockManagerBean.java:299)
at
com.ibm.wbiserver.sequencing.lkmgr.PersistentLockManagerBean
.unlock(PersistentLockManagerBean.java:225)
at com.ibm.wbiserver.sequencing.lkmgr.EJSLocalStatelessPersistent
LockManager_19c5d0a6.unlock(Unknown Source)
at com.ibm.wbiserver.sequencing.wcc.UnlockUtil.unlockOnly(UnlockUtil.java:103)
at com.ibm.wbiserver.sequencing.wcc.UnlockUtil.unlock(UnlockUtil.java:75)
The error indicates that a deadlock occurred in the DB2 database server. Following is the deadlock data that is captured from the DB2 database. This data comes from the formatted output of the DB2DETAILDEADLOCK event monitor, which is created and active by default for every DB2 database:
Deadlock ID: 889 (Deadlock detection time: 10/22/2009 11:07:26.772336)
Appl Id: 127.0.0.1.53545.091022091435 is requesting following lock
Lock Name : 0x0002000D000000000695003D52
Table of lock waited on : PERSISTENTLOCK
Schema of lock waited on : DB2INST1
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: NS - Share (and Next Key Share)
Lock object name: 110428221
A lock is held as shown in the following example:
Lock Name : 0x0002000D000000000698004252
Lock Object Name : 110624834
Object Type : Row
Table Name : PERSISTENTLOCK
Mode : X - Exclusive
Appl Id: 127.0.0.1.53525.091022091353 requesting following lock
Lock Name : 0x0002000D000000000698004252
Table of lock waited on : PERSISTENTLOCK
Schema of lock waited on : DB2INST1
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: NS - Share (and Next Key Share)
Lock object name: 110624834
Another lock is held:
Lock Name : 0x0002000D000000000695003D52
Lock Object Name : 110428221
Object Type : Row
Table Name : PERSISTENTLOCK
Mode : X - Exclusive
Application 1 requests a lock that is held by application 2 and vice versa. To release the lock, the DB2 database selects a target, which is rolled back. Both applications run the same query:
SELECT * from PERSISTENTLOCK WHERE LOCKID = ? AND SEQUENCEID = ?
Cause
On the Database server runstats script was not run on regular intervals. The following lines in the explanation show the last statistics update:
Name: PK_PERSISTENTLOCK
Type: Index
Time of creation: 2009-01-14-11.58.52.612756
Last statistics update: 2009-01-20-10.36.40.039020
Name: PERSISTENTLOCK
Type: Table
Time of creation: 2009-01-14-11.58.52.582210
Last statistics update: 2009-01-20-10.36.40.039020
Another way to check the statistics timestamp is to run a simple query from the system catalog
views using the relevant tables that are shown by the SQL in the deadlock event monitor output::
select TABSCHEMA, TABNAME, CREATE_TIME, STATS_TIME from SYSCAT.TABLES where TABSCHEMA like 'DB2INST1' and TABNAME like 'PERSISTENTLOCK'
Resolving The Problem
Run the runstats script, which is a DB2 script, at regular intervals and set the script to gather RUNSTATS WITH DISTRIBUTION AND DETAILED INDEXES ALL.
In addition to running the runstats scripts regularly, you can perform the following tasks to avoid the problem:
- Use REOPT ONCE or REOPT ALWAYS with the command-line interface (CLI ) packages to change the query optimization behavior.
- In the DB2 database, change the table to make it volatile. Volatile tables indicate to the DB2 optimizer that the table cardinality can change significantly at run time (from empty to large and vice versa). Therefore, DB2 uses an index to access a table rather than a table scan.
Related Information
Was this topic helpful?
Document Information
Modified date:
23 June 2018
UID
swg21410155