Lock Wait and Lock timeout
An application that makes a request for a lock that is not compatible with the existing locks on the object, or a lock request not already satisfied will be placed into a lock request pending queue. The lock request will continue to be held for the waiting application until either timeout period is exceeded or a deadlock is the cause of the result.
A - Erros
Evidences:
- Low Performance of some applications.
B - Causes
A common user symptom of a locking problem is an application hang. A hang
usually appears as Lock Wait within the database engine. To confirm that an
application is in Lock Wait, please go to section C.
C - Investigation
C.1 - Check if there is any connection waiting for locks
db2 list application show detail | grep Lock
- if no rows, there is no lock waiting on this database.
- output example:
Output example: |
---|
ISSADMIN adapter 1554
G93E9922.O106.012F95144406 0049 1 0
6258776 Lock-wait 10/05/2007
10:44:45.482794 MMDB /db/db2data/mmdb/inst1/NODE0000/SQL00001/ SIVIEW mmserver77 1543 G93E9937.F41E.01EE95130114 0070 1 0 1597568 Lock-wait 10/05/2007 10:44:46.103053 MMDB /db/db2data/mmdb/inst1/NODE0000/SQL00001/ |
In bold is the agentid. This number uniquely identify a connection on database. We will use this info in the next steps.
C.2 - Find out who's causing the lock waiting
db2 get snapshot for locks for application agentid 1554
This command will list, among other things, the connection holding the lock needed by appl 1554.
output example: |
---|
ID of agent holding lock = 161 This means the connection #161 is the causing the lock. |
C.3 - Identify the status of connection holding the lock
db2 list application show detail | grep 161
- If Status = UOW Waiting: application is no longer executing, but still holding locks. Normally it indicates some problem. Check if is possible to kill it and follow to Step 4.
- If Status = UOW Executing: application is actually running a SQL statement. You can collect more data about this application running this command: db2 get snapshot for application agentid 161 and determine if this connection should be killed or keep running. Locks will be held untill this application finish the processing.
- If Status = Lock-wait: this application is waiting for another connection to release the locks. This is not the root cause of the lock wait. Get back to Step 2 using this agentid as parameter.
D - Resolution
D.1 - Kill application that is holding the lock
db2 "force application (161)"
This command will disconnect the application from database and release the locks. If the application is in Executing state, a rollback will be done, which may take some time.