Personal tools
You are here: Home DB2 Problem Resolution Locking errors Lock Wait and Lock timeout
Navigation
Log in


Forgot your password?
 
Document Actions

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.
Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls