How to find the owner of a lock in DB2.

DB2 automatically guarantees the integrity of data by enforcing several locking strategies. These strategies permit multiple users from multiple environments to access and modify data concurrently. DB2 locks prevent one program from accessing data that has been changed, but not yet committed, by another program. Locking process is controlled by DB2’s IRLM (Inter System Resource Lock Manager). However, whenever practical, DB2 tries to lock pages without going to the IRLM. This type of lock is called a latch.

In this blog we will try to get information about which transaction is holding the lock by running the db2pd command with the -locks attribute. Look for the row that has a "W", meaning waiting, in the Sts (status) column. The Owner column indicates which transaction is holding the lock. In the following example TranHdl 3 is waiting on a lock held be TranHdl 4 (indicated by 1 below).

Syntax:

db2pd -d <database> -locks showlocks

[db2inst1@l00lnx2205 ~]$ db2pd -d SAMPLE -locks showlocks

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:09:17 -- Date 2022-09-14-12.47.45.740773

Locks:
TranHdl ...         Sts        Owner ...
3 ...                G           3 ...
4 ...                G           4 ...
3 ...                W        4   1  ...
4 ...                G           4 ...

Identify the application that is running the transaction that is holding the lock by running db2pd -transactions. In the example below we see that TranHdl 4(indicated by 1 ) is being run by AppHandl 14(indicated by 4 ).

Syntax:

db2pd -d <database> -tansactions transaction= < TranHdl >

[db2inst1@l00lnx2205 ~]$ db2pd -d SAMPLE -tansactions transaction=14

Database Partition 0 -- Database SAMPLE -- Active -- Up 00:10:02
Transactions:
... AppHandl     ... TranHdl      Locks ...
... 14   4                ... 4   1          42 ...
.... 16                     ... 2               7 ...

Identify the Agent ID that is running the application by running db2pd -agents.

Syntax:

db2pd -agents application=< AppHandl>

[db2inst1@l00lnx2205 ~]$ db2pd -agents application=14
Database Partition 0 -- Database SAMPLE -- Active -- Up 0:10:15
Agents:
Current agents: 3
Idle agents: 0
Active agents: 3
Coordinator agents: 3

... AppHandl       ... AgentPid       ... ClientPid        Userid             ClientNm ...
 ... 16                ... 784689          ... 375415           imjhachandan       db2bp ...
 ... 14   4           ... 946824          ... 487264           imjhachandan       db2bp ...
 ... 13                ... 675489          ... 247854           imjhachandan       db2bp ...

That's all in this post. If you liked this blog and interested in knowing more about IBM Db2. Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com.