db2锁等待实验模拟与锁分析

db2版本：9.7.2
OS：CentOS6-64
实例和DB：db2inst1 bkdb1
要观察锁等待，设置LOCKTIMEOUT参数，不限制锁超时时间，便于分析
db2 update db cfg for bkdb1 using LOCKTIMEOUT -1

本地建立两个db2连接

目前我们有两个会话：401和402

会话1 401

[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.

此事务目前未提交

402会话更同一行数据

[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”

查看等待信息

[db2inst1@aspDB01 ~]$ db2 “select count(*) from lockwaitevm”

1

      4

1 record(s) selected.

通过db2pd查看
[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

能通过db2top也能看到锁的关系：

通过事件监控器提取监控数据

刷新并关闭事件监控
[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.

格式化监控表

[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

查看事件
[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

查看事件参与者

[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锁等待

查询事件参与者的活动：

[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.