对于数据库锁的调查，一般从以下四个个方面
  谁持有的锁
  谁等待锁
  持有锁的应用执行了什么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