Technical Blog Post
Abstract
75 ways to demystify DB2: #28: Techtip : How to resolve locking issue caused by indbout transactions in DPF environment
Body
Abstract:
In DPF environment, the lock timeout issue could be caused by indoubt transactions. The indoubt transactions cannot be automatically committed or rollback by database manager therefore whenever the application restarts the lock timeout issue occurs.
Troubleshooting:
Collect the locking data by:
db2pd -db <dbname> -alldbp -wlock -app -trans -repeat 5 10
Note that the above db2pd must have -alldbp option which runs on each logical node on one physical box, if there are multiple physical boxes, then db2pd needs to be run on *EACH* box.
From db2pd output, the lock holder has no apphandle but only has a transaction id and coordinator edu, appname,authid and appid are not available.
In below example, we may find a replication process is waiting for row lock held by the indoubt transaction.
2015-05-04-13.53.00.865487-240 E414885A584 LEVEL: Warning PID : 38077832 TID : 4371 PROC : db2sysc 5 INSTANCE: user1 NODE : 005 DB : SAMPLE EDUID : 4371 EDUNAME: db2dlock (SAMPLE) 5 FUNCTION: DB2 UDB, lock manager, sqlpldl, probe:1280 MESSAGE : ADM1838W An application is waiting for a lock held by an indoubt transaction. This will cause the application to wait indefinitely. Use the LIST INDOUBT TRANSACTIONS command to investigate and resolve the indoubt transactions
How to resolve:
1. First step is to find on which node it has the indoubt transactions.
A common mistake is just issuing command LIST INDOUBT TRANSACTIONS in the command line which *ONLY* retrieve the indbout transaction information on *CURRENT* node. You may see the returning message like below:
SQL1251W No data returned for heuristic query. SQLSTATE=00000
The correct way is to use db2_all "LIST INDOUBT TRANSACTIONS" to run this command on *ALL* logical nodes.
Then you may see the below output from particular node:
2. Deal with the indoubt transactions on particular node.
First you need to connect to the particular node which has indoubt transactions.
Use with prompting option in the indoubt transaction command. Then it shows the indoubt transaction info and lets you choose which action needs to be taken:
You may enter c 1 to commit the transaction number 1
Enter y to confirm the action.
UID
ibm11141084