Determine who is holding a lock in the database¶

The db2pd is used to demonstrate how to determine who is holding a lock in the database.

see also View the currently executing SQL statement.

1. we use the table, easypay_interface in the store database as an example
2. first execute the following command to find out if there's any lock wait:
   

   [db2inst1@cwyapp1 ~]$ db2pd -db store -locks wait showlock

   • lock wait means an application is acquiring the lock that held exclusively by another application, and it has to wait until the application release the lock or the timeout period is exceeded
   • Sample output:
     

     Database Partition 0 -- Database STORE -- Active -- Up 0 days 09:03:34 -- Date 11/01/2011 18:07:53
     
     Locks:
     Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
     0x13D01F90 2          02000D141E00116CF301000052 Row        .NS  W   8          1   0          0x00 0x00000001  TbspaceID 2     TableID 5133   PartitionID 0 Page 32730129 Slot 30
     

     • to obtain the name of the affected table, get the 2 value from the column, TbspaceID and TableID and execute:
       

       select substr(tabschema,1,9) as tabschema, 
                substr(tabname,1,12) as tabname, tableid, tbspaceid 
                from syscat.tables 
                where tbspaceid = <TbspaceID value> and tableid = <TableID value>
       
       

     • there are 2 possible values for the Sts column:
       • W - the transaction is in the waiting queue to acquire the lock
       • G - the lock is granted
3. based on the Owner column, the lock is currently held by TransHdl 8. And then, using the TransHdl 8 to find out which application triggered the transaction by executing the command:
   

   [db2inst1@cwyapp1 ~]$ db2pd -db store -transactions
   

   • Sample output:
     

     Database Partition 0 -- Database STORE -- Active -- Up 0 days 08:56:07 -- Date 11/01/2011 18:00:26
     
     Transactions:
     Address    AppHandl [nod-index] TranHdl    Locks      State   Tflag      Tflag2     Firstlsn       Lastlsn        LogSpace        SpaceReserved   TID            AxRegCnt   GXID     ClientUserID                   ClientWrkstnName               ClientApplName                 ClientAccntng
     ...
     
     0x138F6080 102      [000-00102] 8          3          WRITE   0x00000000 0x00000000 0x015B28C6AC39 0x015B28C6ACB5 184             383             0x00030A6F839F 1          0        n/a                            n/a                            n/a                            n/a
     
     ...
     
     

4. based on the AppHandl column, the application that currently holding the lock is 102. If you would like to know what the application is doing, refer to View the currently executing SQL statement
5. done