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 来标识)

其中列Sts中：W 表示等待锁，G代表已经获得锁


4. 通过TranHdl的值查找等待锁的agentid.

db2pd -db <dbname> -transactions |grep 27


Address AppHandl [nod-index] AgentEDUID Priority Type State ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName

0x077000002032A300 20686 [000-20686] 27 5 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x000000DF81D2 1 0 n/a

AppHandl列的值表示等待锁的agentid.


5. 通过等待锁的agentid查找出等待锁的SQL和占有锁的agentid

db2 get snapshot for application agentid 20686


Blocking cursor = YES

Dynamic SQL statement text:

select trans_st from xxxdb.tbl_xxx_trans_log1_1 where sys_tra_no='004918'

ID of agent holding lock = 20687

Application ID holding lock = *LOCAL.xxxdb.110130082456

Lock name = 0x0002001B000000000001000252

Lock attributes = 0x00000000

Release flags = 0x00000004

Lock object type = Row

Lock mode = Exclusive Lock (X)

Lock mode requested = Next Key Share (NS)

Name of tablespace holding lock = USERSPACE1

Schema of table holding lock = XXX
Name of table holding lock = TBL_XXX_TRANS_LOG1_1
Data Partition Id of table holding lock = 0

Lock wait start timestamp = 01/30/2011 16:44:33.771482

还可以得到存在锁的数据库表名和schema。

6.通过占有锁的agentid找出引起锁等待的SQL和客户端进程
db2 get snapshot for application agentid 20687

Application handle = 20687

Application status = UOW Waiting

Status change time = 01/30/2011 16:44:31.200616

Application code page = 1386

Application country/region code = 1

DUOW correlation token = *LOCAL.xxx.110130082456

Application name = db2bp

Application ID = *LOCAL.xxx.110130082456

Sequence number = 00002

Dynamic SQL statement text:

update xxxdb.tbl_xxx_trans_log1_1 set trans_st='10000' where sys_tra_no='004918'


7. 关闭快照监控和DBMS监控开关
db2 update monitor switches using lock off buffpool off uow off sort off table off statement off
db2 update dbm cfg using DFT_MON_LOCK off DFT_MON_STMT off