DB2 查找引起锁等待或死锁的客户端进程和SQL语句
【简介】
      本文主要用于定位系统中导致锁等待或者死锁的原因.

【详细信息】
1. 首先要打开DBMS监控开关和快照开关：
db2 update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on
db2 update monitor switches using lock on buffpool on sort on uow on table on statement on

2.   获取锁相关的快照信息：
db2 get snapshot for database on <dbname> |grep -i lock



Locks held currently                       = 346

Lock waits                                 = 257

Time database waited on locks (ms)         = 0

Lock list memory in use (Bytes)            = 147136

Deadlocks detected                         = 5

Lock escalations                           = 0

Exclusive lock escalations                 = 0

Agents currently waiting on locks          = 0

Lock Timeouts                              = 0

Block IOs                                  = Not Collected

Pages from block IOs                       = Not Collected

Internal rollbacks due to deadlock         = 4

Number of MDC table blocks pending cleanup = 0

    Memory Pool Type                           = Lock Manager Heap



3. 如果存在锁等待或者死锁，找到锁的信息：

db2pd -db <dbname> -locks showlocks wait



Address            TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount Att ReleaseFlg

0x0770000020471B00 27         0002001B000000000001000252 Row        .NS W   28         1   0          0x00 0x00000004 TbspaceID 2     TableID 27     PartitionID 0 Page 1 Slot 2

0x0770000020471840 28         0002001B000000000001000252 Row        ..X G   28         1   0          0x00 0x40000000 TbspaceID 2     TableID 27     PartitionID 0 Page 1 Slot 2



可以查找出锁名称,锁的位置(使用tablespaceID，tableID, recordID 来标识)

其中列St