SQLCODE -911 DB2 database deadlock in PersistentLockManager

Technote (troubleshooting)


Problem(Abstract)

DB2 database deadlocks and SQLCODE -911 occur in the PersistentLockManager calls in WebSphere Process Server.

Symptom

The following exception is written in the profileRoot/profileName/logs/serverName/SystemOut.log file of WebSphere Process Server:

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

DB2 Query Optimization
DB2 Infocenter
WPS Infocenter

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

WebSphere Process Server
Hangs

Software version:

6.1, 6.1.0.1, 6.1.0.2, 6.1.0.3, 6.1.0.4, 6.1.2, 6.1.2.1, 6.1.2.2, 6.1.2.3

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Software edition:

NA

Reference #:

1410155

Modified date:

2009-11-30

Translate my page

Machine Translation

Content navigation