對於資料庫鎖的調查，一般從以下四個個方面
誰持有的鎖
誰等待鎖
持有鎖的應用執行了什麼SQL
等待鎖的應用執行了什麼SQL
DB2對於鎖的分析從很長時間以來被不少人抱怨太麻煩。
如果你只是透過db2pd來調查分析鎖，很遺憾的告訴你，不能完全找到鎖產生的原因，因為db2pd裡面沒有把一個app handle和它曾經執行過的SQL關聯起來
從DB2 9.1開始，DB2引入了 db2pd 結合db2cos的方式，當發生鎖超時的時候，DB2會自動呼叫回撥指令碼，這種方式也不好，因為需要分析db2pd的輸出，在一個繁忙的資料庫中，看db2pd的輸出，並匹配一些值，是一個不小的體力活
從DB2 9.5開始，DB2引入了 DB2_CAPTURE_LOCKTIMEOUT註冊變數，透過它和dead lock event monitor一起可以讓鎖的分析稍微簡單一點
從DB2 9.7開始，DB2引入了 event monitor for locking，你可以透過SQL查詢來獲取鎖相關的資訊。

對於DB2_CAPTURE_LOCKTIMEOUT註冊變數的使用，涉及到一個deadlock event monitor的問題，這是這篇技術blog的關注點

#### 建立測試資料庫
db2sampl

#### 檢視新的資料庫的 event monitor

#### 檢視DB2DETAILDEADLOCK的屬性

#### 檢視DB2DETAILDEADLOCK的檔案儲存路徑 (optional)

#### 建立測試表並insert資料

#### Session 1，模擬產生鎖
[db2inst1@osboxes1 ~]$ db2 connect to sample
[db2inst1@osboxes1 ~]$ db2 +c "update dba.test set name='KaKa' where id=2"

#### Session 2，模擬鎖超時
[db2inst1@osboxes1 ~]$ db2 connect to sample
[db2inst1@osboxes1 ~]$ db2 "update dba.test set name='Haha' where id=2"

#### 檢視db2locktimeout.*檔案

[db2inst1@osboxes1 ~]$ db2pd -diag

Database Member 0 -- Active -- Up 0 days 03:01:56 -- Date 2022-01-01-23.12.34.745297

/home/db2inst1/sqllib/db2dump/DIAG0000/
[db2inst1@osboxes1 ~]$ cd /home/db2inst1/sqllib/db2dump/DIAG0000/
[db2inst1@osboxes1 DIAG0000]$ ls -l
total 10268
drwxr-xr-x 2 db2inst1 db2iadm1       6 Nov 11 12:49 clientrecords
-rw-rw-rw- 1 db2inst1 db2iadm1 4007946 Jan 1 22:59 db2diag.log
-rw-r----- 1 db2inst1 db2iadm1 6291284 Jan 1 20:10 db2eventlog.000
-rw-rw-rw- 1 db2inst1 db2iadm1 204294 Jan 1 22:59 db2inst1.nfy
-rw-r----- 1 db2inst1 db2iadm1    1783 Jan 1 22:58 db2locktimeout.0.113.2022-01-01-22-58-55
-rw-r--r-- 1 db2fenc1 db2fadm1    1707 Jan 1 22:49 db2sampl_Import.msg
drwxrwxrwx 2 db2inst1 db2iadm1      62 Nov 12 04:51 events
drwxrwxr-t 2 db2inst1 db2iadm1      42 Dec 28 01:57 stmmlog

[db2inst1@osboxes1 DIAG0000]$ cat db2locktimeout.0.113.2022-01-01-22-58-55

LOCK TIMEOUT REPORT

Date:               01/01/2022
Time:               22:58:55
Instance:           db2inst1
Database:           SAMPLE
Member ID:          0

Lock Information:

   Lock Name:      03000500050000000000000052
   Lock Type:      Basic RECORD lock(DMS/IXM)
   Lock Specifics: (obj={3;5}, rid=d(0;0;5), x0500000000000000)

Lock Requestor:
   System Auth ID:          DB2INST1
   Application Handle:      [0-270]
   Application ID:          *LOCAL.db2inst1.220102035823
   Application Name:        db2bp
   Requesting Agent ID:     113
   Coordinator Agent ID:    113
   Coordinator Partition:   0
   Lock timeout Value:      30000 milliseconds
   Lock mode requested:     ..U
   Application Status:      (SQLM_LOCKWAIT)
   Current Operation:       (SQLM_EXECUTE_IMMEDIATE)
   Lock Escalation:         No

   Context of Lock Request:
   Identification:            UOW ID (1); Activity ID (1)
   Activity Information:
      Package Schema:         (NULLID )
      Package Name:           (SQLC2P31NULLID )
      Package Version:        ()
      Section Entry Number:   203
      SQL Type:               Dynamic
      Statement Type:         DML, Insert/Update/Delete
      Effective Isolation:    Cursor Stability
      Statement Unicode Flag: No
      Statement:              update dba.test set name='Haha' where id=2

Lock Owner (Representative):
   System Auth ID:          DB2INST1
   Application Handle:      [0-245]
   Application ID:          *LOCAL.db2inst1.220102035133
   Application Name:        db2bp
   Requesting Agent ID:     52
   Coordinator Agent ID:    52
   Coordinator Partition:   0
   Lock mode held:          ..X

   List of Active SQL Statements: Not available

   List of Inactive SQL Statements from current UOW: Not available
[db2inst1@osboxes1 DIAG0000]$

從這個輸出可以看出
App handle 270 想獲取鎖，但是沒有得到，它執行的SQL是 update dba.test set name='Haha' where id=2
App handle 245 持有鎖，遺憾的是，沒有給出SQL資訊

那怎麼能獲取完整的SQL資訊？

#### 為了得到完整SQL資訊，刪掉預設的 db2detaildeadlock
[db2inst1@osboxes1 ~]$ db2 "set event monitor db2detaildeadlock state=0"
DB20000I The SQL command completed successfully.
[db2inst1@osboxes1 ~]$ db2 "drop event monitor db2detaildeadlock"
DB20000I The SQL command completed successfully.

#### 建立一個新的 deadlock event monitor,注意這裡用了 with details history
[db2inst1@osboxes1 ~]$ mkdir /home/db2inst1/evmondeadlock
[db2inst1@osboxes1 ~]$ db2 "create event monitor evmondeadlock for deadlocks with details history
> write to file '/home/db2inst1/evmondeadlock'
> manualstart
> on dbpartitionnum 0
> maxfiles 20
> maxfilesize 512
> buffersize 17
> blocked append"
DB20000I The SQL command completed successfully.
[db2inst1@osboxes1 ~]$

#### Session 1，模擬產生鎖
[db2inst1@osboxes1 ~]$ db2 connect to sample
[db2inst1@osboxes1 ~]$ db2 +c "update dba.test set name='KaKa' where id=2"

#### Session 2，模擬鎖超時
[db2inst1@osboxes1 ~]$ db2 connect to sample
[db2inst1@osboxes1 ~]$ db2 "update dba.test set name='Haha' where id=2"

#### 檢視db2locktimeout.*檔案
[db2inst1@osboxes1 DIAG0000]$ more db2locktimeout.0.113.2022-01-01-23-25-45

LOCK TIMEOUT REPORT

Date:               01/01/2022
Time:               23:25:45
Instance:           db2inst1
Database:           SAMPLE
Member ID:          0

Lock Information:

   Lock Name:      03000500050000000000000052
   Lock Type:      Basic RECORD lock(DMS/IXM)
   Lock Specifics: (obj={3;5}, rid=d(0;0;5), x0500000000000000)

Lock Requestor:
   System Auth ID:          DB2INST1
   Application Handle:      [0-270]
   Application ID:          *LOCAL.db2inst1.220102035823
   Application Name:        db2bp
   Requesting Agent ID:     113
   Coordinator Agent ID:    113
   Coordinator Partition:   0
   Lock timeout Value:      30000 milliseconds
   Lock mode requested:     ..U
   Application Status:      (SQLM_LOCKWAIT)
   Current Operation:       (SQLM_EXECUTE_IMMEDIATE)
   Lock Escalation:         No

   Context of Lock Request:
   Identification:            UOW ID (3); Activity ID (1)
   Activity Information:
      Package Schema:         (NULLID )
      Package Name:           (SQLC2P31NULLID )
      Package Version:        ()
      Section Entry Number:   203
      SQL Type:               Dynamic
      Statement Type:         DML, Insert/Update/Delete
      Effective Isolation:    Cursor Stability
      Statement Unicode Flag: No
      Statement:              update dba.test set name='Haha' where id=2

Lock Owner (Representative):
   System Auth ID:          DB2INST1
   Application Handle:      [0-245]
   Application ID:          *LOCAL.db2inst1.220102035133
   Application Name:        db2bp
   Requesting Agent ID:     52
   Coordinator Agent ID:    52
   Coordinator Partition:   0
   Lock mode held:          ..X

   List of Active SQL Statements: Not available

   List of Inactive SQL Statements from current UOW:

      Entry:                  #1
      Identification:         UOW ID (18); Activity ID (1)
      Package Schema:         (NULLID )
      Package Name:           (SQLC2P31)
      Package Version:        ()
      Section Entry Number:   203
      SQL Type:               Dynamic
      Statement Type:         DML, Insert/Update/Delete
      Effective Isolation:    Cursor Stability
      Statement Unicode Flag: No
      Statement:              update dba.test set name='KaKa' where id=2
[db2inst1@osboxes1 DIAG0000]$
可以看到持有鎖的 App handle 245，它的 Inactive SQL Statements 部分是執行過的SQL

db2 鎖分析 DB2_CAPTURE_LOCKTIMEOUT使用

相關文章