DB2 查寻引起锁等待或死锁的客户端进程和SQL语句
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
【简介】
本文主要用于定位系统中导致锁等待或者死锁的原因.
【详细信息】
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