1、关于锁等待
当两个应用程序竞争资源时，第一个应用程序在资源上加锁后，
第二个应用程序企图加锁时发现希望增加的锁与资源现有的锁不兼容，就出现锁等待，
第二个应用程序挂起。为捕获锁等待事件，MON_LOCKWAIT 不能设置为 NONE，
当锁等待时间超过 MON_LW_THRESH 指定的值时则事件监视器捕获该事件
要观察锁等待，设置这个参数，让锁一直等待着，便于分析
db2 update db cfg for bkdb1 using LOCKTIMEOUT -1
2 锁等待模拟
db2 connect to dbname模拟两个APP
2.1 APP1更新数据
401号APP
[db2inst1@aspDB01 ~]$ db2 connect to bkdb1
Database Connection Information
Database server = DB2/LINUXX8664 9.7.2
SQL authorization ID = DB2INST1
Local database alias = BKDB1
[db2inst1@aspDB01 ~]$ db2 +c "update tab1 set name='xxx' where id=1"
DB20000I The SQL command completed successfully.
未提交
2.2 APP2更新同一行数据
402号APP
[db2inst1@aspDB01 ~]$ db2 connect to bkdb1
Database Connection Information
Database server = DB2/LINUXX8664 9.7.2
SQL authorization ID = DB2INST1
Local database alias = BKDB1
[db2inst1@aspDB01 ~]$ db2 +c "update tab1 set name='yyy' where id=1"
2.3 等待信息
[db2inst1@aspDB01 ~]$ db2 "select count(*) from lockwaitevm"
1
-----------
4
1 record(s) selected.
使用db2pd查看APP状态
[db2inst1@aspDB01 ~]$ db2pd -apinfo 401 -db bkdb1
Database Partition 0 -- Database BKDB1 -- Active -- Up 0 days 00:02:00 -- Date 11/18/2016 16:20:32
Application :
Address : 0x0000000201030080
AppHandl [nod-index] : 401 [000-00401]
TranHdl : 9
Application PID : 21127
Application Node Name : aspDB01
IP Address: n/a
Connection Start Time : (1479457188)Fri Nov 18 16:19:48 2016
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 105
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 2
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : UOW-Waiting
Application Name : db2bp
Application ID : *LOCAL.db2inst1.161118081948
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
CollectActData: N
CollectActPartition: C
SectionActuals: N
--------------------------------------------------------------------------------
[db2inst1@aspDB01 ~]$ db2pd -apinfo 402 -db bkdb1
Database Partition 0 -- Database BKDB1 -- Active -- Up 0 days 00:02:03 -- Date 11/18/2016 16:20:35
Application :
Address : 0x0000000200FD0080
AppHandl [nod-index] : 402 [000-00402]
TranHdl : 10
Application PID : 20949
Application Node Name : aspDB01
IP Address: n/a
Connection Start Time : (1479457194)Fri Nov 18 16:19:54 2016
Client User ID : db2inst1
System Auth ID : DB2INST1
Coordinator EDU ID : 106
Coordinator Partition : 0
Number of Agents : 1
Locks timeout value : NotSet
Locks Escalation : No
Workload ID : 1
Workload Occurrence ID : 3
Trusted Context : n/a
Connection Trust Type : non trusted
Role Inherited : n/a
Application Status : Lock-wait
Application Name : db2bp
Application ID : *LOCAL.db2inst1.161118081954
ClientUserID : n/a
ClientWrkstnName : n/a
ClientApplName : n/a
ClientAccntng : n/a
CollectActData: N
CollectActPartition: C
SectionActuals: N
List of active statements :
*UOW-ID : 1
Activity ID : 1
Package Schema : NULLID
Package Name : SQLC2H21
Package Version :
Section Number : 203
SQL Type : Dynamic
Isolation : CS
Statement Type : DML, Insert/Update/Delete
Statement : update tab1 set name='yyy'
where id=1
可以看到第2号APP处于lock-wait状态
Blocked/Blocking Agent Chain |
-------------------------------------------------------------|
| 401->402
不过以上信息很难抓取到 ；
下面我们通过事先建好的锁事件监控器查看锁等待信息
3 提取监控数据
3.1 刷新关闭事件监控
[db2inst1@aspDB01 ~]$ db2 flush event monitor lockwaitevm
DB20000I The SQL command completed successfully.
[db2inst1@aspDB01 ~]$ db2 set event monitor lockwaitevm state 0
DB20000I The SQL command completed successfully.
3.2 格式化监控表
[db2inst1@aspDB01 ~]$ db2 "call EVMON_FORMAT_UE_TO_TABLES ( 'LOCKING', NULL, NULL, NULL, NULL, NULL, 'RECREATE_FORCE', -1, 'SELECT * FROM lockwaitevm ORDER BY event_timestamp')"
Return Status = 0
3.3 查看事件
[db2inst1@aspDB01 ~]$ db2 "select substr(event_id,1,2) id,substr(XMLID,1,30) as xml_id,char(EVENT_TYPE,12) type from lock_event"
ID XML_ID TYPE
-- ------------------------------ ------------
11 db2LockEvent_11_LOCKWAIT_2016- LOCKWAIT
1 record(s) selected.
lock_event中有一条记录，事件类型就是LOCKWAIT
3.4 查看事件参与者
[db2inst1@aspDB01 ~]$ db2 "select substr(XMLID,1,25) as xml_id,
substr(PARTICIPANT_NO,1,3) as p_no,
PARTICIPANT_TYPE as p_type,
substr(PARTICIPANT_NO_HOLDING_LK,1,3) as p_lk,
substr(APPLICATION_HANDLE,1,3) as p_name,
substr(TABLE_NAME,1,1) tabname,
substr(lock_mode,1,3) lock_mode,
substr(LOCK_MODE_REQUESTED,1,3) LOCK_MODE_REQ,substr(LOCK_OBJECT_TYPE,1,3) LOCK_OBJECT_TYPE from LOCK_PARTICIPANTS"
XML_ID P_NO P_TYPE P_LK P_NAME TABNAME LOCK_MODE LOCK_MODE_REQ LOCK_OBJECT_TYPE
------------------------- ---- ---------- ---- ------ ------- --------- -------
db2LockEvent_11_LOCKWAIT_ 1 Requester 2 402 T 5 5 ROW
db2LockEvent_11_LOCKWAIT_ 2 Owner - 401 - - - -
2 record(s) selected.
LOCK_PARTICIPANTS中有两条记录
一条记录的 PARTICIPANT_TYPE 为 Requester，应用程序句柄为402 即 APP2，LOCK_OBJECT_TYPE为ROW，表示为行锁，
LOCK_MODE_REQUESTED为5，表示请求的锁类型为排他锁（X 锁）
而行上目前加的锁 LOCK_MODE 为 5 表示排他锁（X 锁）
表 LOCK_PARTICIPANTS 另一条记录 PARTICIPANT_TYPE 为 Owner，应用程序句柄为401 即 APP1。这意味着 APP1 为锁的拥有者，在行上持有了 X 锁导致应用程序2锁等待
3.5 查看事件活动
查询事件参与者的活动：
[db2inst1@aspDB01 ~]$ db2 "SELECT PARTICIPANT_NO, ACTIVITY_TYPE, substr(STMT_TEXT,1,40) sql_text FROM LOCK_PARTICIPANT_ACTIVITIES"
PARTICIPANT_NO ACTIVITY_TYPE SQL_TEXT
-------------- ------------- ----------------------------------------
1 current update tab1 set name='yyy' where id=1
1 past update tab1 set name='xxx' where id=1
2 record(s) selected.
很明显，past就是已经执行的命令 持有锁，阻塞了current当前执行的命令，导致current等待；
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29023300/viewspace-2128771/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29023300/viewspace-2128771/
665
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
