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
[[email protected] ~]$ db2 connect to bkdb1
Database Connection Information
Database server = DB2/LINUXX8664 9.7.2
SQL authorization ID = DB2INST1
Local database alias = BKDB1
[db2i[email protected] ~]$ db2 +c "update tab1 set name='xxx' where id=1"
DB20000I The SQL command completed successfully.
未送出
2.2 APP2更新同一行資料
402号APP
[[email protected] ~]$ db2 connect to bkdb1
Database Connection Information
Database server = DB2/LINUXX8664 9.7.2
SQL authorization ID = DB2INST1
Local database alias = BKDB1
[db2i[email protected] ~]$ db2 +c "update tab1 set name='yyy' where id=1"
2.3
等待資訊[[email protected] ~]$ db2 "select count(*) from lockwaitevm"
1
-----------
4
1 record(s) selected.
使用db2pd檢視APP狀态
[[email protected] ~]$ 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
--------------------------------------------------------------------------------
[[email protected] ~]$ 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-waitApplication 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 重新整理關閉事件監控
[[email protected] ~]$ db2 flush event monitor lockwaitevm
DB20000I The SQL command completed successfully.
[[email protected] ~]$ db2 set event monitor lockwaitevm state 0
DB20000I The SQL command completed successfully.
3.2
格式化監控表[[email protected] ~]$ 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 檢視事件
[[email protected] ~]$ 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中有一條記錄，事件類型就是
LOCKWAIT3.4 檢視事件參與者
[[email protected] ~]$ 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 檢視事件活動
查詢事件參與者的活動：
[[email protected] ~]$ 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/
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)