1.创建死锁监视器
mkdir /home/db2inst1/dlocks
db2 connect to <dbname>
db2 "create event monitor dlocks for deadlocks with details write to file '/home/db2inst1/dlocks' MAXFILES 5 MAXFILESIZE 7680"2.激活死锁监视器
https://www.cndba.cn/hbhe0316/article/4776
db2 "set event monitor dlocks state 1"3.去激活默认的死锁监视器
db2 "set event monitor DB2DETAILDEADLOCK state 0"4.查看死锁监视器状态https://www.cndba.cn/hbhe0316/article/4776https://www.cndba.cn/hbhe0316/article/4776
db2 "select substr(evmonname,1,20) AS evmonname,case when event_mon_state(evmonname)=0 then 'Inactive' when event_mon_state(evmonname)=1 then 'Active' END AS STATE from syscat.eventmonitors"
或者
db2 "select varchar(evmonname,20),event_mon_state(evmonname) from syscat.eventmonitors"5.产生死锁后https://www.cndba.cn/hbhe0316/article/4776
db2 "flush event monitor dlocks"
db2 "set event monitor dlocks state 0"6.提取日志
db2evmon -path /home/db2inst1/dlocks >/home/db2inst1/dlocks/db2deadlocks.txt7.删除死锁监视器
db2 "drop event monitor dlocks"窗口1
db2 +c "update t1 set name='yo11' where id=1"窗口2：
db2 +c "update t1 set name='yo22' where id=2"窗口1：拿到id=1行锁后，申请id=2行锁
https://www.cndba.cn/hbhe0316/article/4776
https://www.cndba.cn/hbhe0316/article/4776
db2 +c "update t1 set name='yo22' where id=2"窗口2：拿到id=2行锁后，申请id=1行锁
https://www.cndba.cn/hbhe0316/article/4776
https://www.cndba.cn/hbhe0316/article/4776
db2 +c "update t1 set name='yo11' where id=1"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "2". SQLSTATE=40001[db2inst1@db01 dlocks]$ cat /tmp/b.txt
--------------------------------------------------------------------------
EVENT LOG HEADER
Event Monitor name: DLOCKS
Server Product ID: SQL11014
Version of event monitor data: 12
Byte order: LITTLE ENDIAN
Number of nodes in db2 instance: 1
Codepage of database: 1208
Territory code of database: 86
Server instance name: db2inst1
--------------------------------------------------------------------------
--------------------------------------------------------------------------
Database Name: TESTDB
Database Path: /db2data/db2inst1/NODE0000/SQL00001/MEMBER0000/
First connection timestamp: 05/11/2020 19:05:40.960511
Event Monitor Start time: 05/11/2020 20:06:22.641412
--------------------------------------------------------------------------
3) Deadlock Event ...
Deadlock ID: 3
Number of applications deadlocked: 2
Deadlock detection time: 05/11/2020 20:08:00.469483
Rolled back Appl participant no: 2
Rolled back Appl Id: *LOCAL.db2inst1.200511110631
Rolled back Appl seq number: 00007
Rolled back Appl handle: 29
4) Connection Header Event ...
Appl Handle: 29
Appl Id: *LOCAL.db2inst1.200511110631
Appl Seq number: 00007
DRDA AS Correlation Token: *LOCAL.db2inst1.200511110631
Program Name : db2bp
Authorization Id: DB2INST1
Execution Id : db2inst1
Codepage Id: 1208
Territory code: 1
Client Process Id: 22254
Client Database Alias: TESTDB
Client Product Id: SQL11014
Client Platform: Linux/X8664
Client Communication Protocol: Local
Client Network Name: db01
Connect timestamp: 05/11/2020 19:06:31.918270
5) Deadlocked Connection ...
Deadlock ID: 3
Participant no.: 2
Participant no. holding the lock: 1
Appl Id: *LOCAL.db2inst1.200511110631
Appl Seq number: 00007
Appl Id of connection holding the lock: *LOCAL.db2inst1.200511110623
Seq. no. of connection holding the lock: 00001
Lock wait start time: 05/11/2020 20:07:54.255951
Lock Name : 0x02000400040000000000000052
Lock Attributes : 0x00000000
Release Flags : 0x00000000
Lock Count : 0
Hold Count : 0
Deadlock detection time: 05/11/2020 20:08:00.469771
Table of lock waited on : T1
Schema of lock waited on : DB2INST1
Data partition id for table : 0
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 4
Application Handle: 29
Deadlocked Statement:
Type : Dynamic
Operation: Execute Immediate
Section : 203
Creator : NULLID
Package : SQLC2O29
Cursor :
Cursor was blocking: FALSE
Text : update t1 set name='yo11' where id=1
List of Locks:
Lock Name : 0x020000000100000001006038D6
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Variation
Data partition id : -1
Mode : S - Share
Lock Name : 0x02000400050000000000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 5
Object Type : Row
Tablespace Name : USERSPACE1
Table Schema : DB2INST1
Table Name : T1
Data partition id : 0
Mode : X - Exclusive
Lock Name : 0x4141414141454A696C171C12C1
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Plan
Data partition id : -1
Mode : S - Share
Lock Name : 0x02000400000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 2
Hold Count : 0
Lock Object Name : 4
Object Type : Table
Tablespace Name : USERSPACE1
Table Schema : DB2INST1
Table Name : T1
Data partition id : 0
Mode : IX - Intent Exclusive
Locks Held: 4
Locks in List: 4
Locks Displayed: 4
6) Connection Header Event ...
Appl Handle: 28
Appl Id: *LOCAL.db2inst1.200511110623
Appl Seq number: 00006
DRDA AS Correlation Token: *LOCAL.db2inst1.200511110623
Program Name : db2bp
Authorization Id: DB2INST1
Execution Id : db2inst1
Codepage Id: 1208
Territory code: 1
Client Process Id: 22237
Client Database Alias: TESTDB
Client Product Id: SQL11014
Client Platform: Linux/X8664
Client Communication Protocol: Local
Client Network Name: db01
Connect timestamp: 05/11/2020 19:06:23.079522
7) Deadlocked Connection ...
Deadlock ID: 3
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: *LOCAL.db2inst1.200511110623
Appl Seq number: 00006
Appl Id of connection holding the lock: *LOCAL.db2inst1.200511110631
Seq. no. of connection holding the lock: 00001
Lock wait start time: 05/11/2020 20:07:47.207017
Lock Name : 0x02000400050000000000000052
Lock Attributes : 0x00000000
Release Flags : 0x00000000
Lock Count : 0
Hold Count : 0
Deadlock detection time: 05/11/2020 20:08:00.470366
Table of lock waited on : T1
Schema of lock waited on : DB2INST1
Data partition id for table : 0
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 5
Application Handle: 28
Deadlocked Statement:
Type : Dynamic
Operation: Execute Immediate
Section : 203
Creator : NULLID
Package : SQLC2O29
Cursor :
Cursor was blocking: FALSE
Text : update t1 set name='yo22' where id=2
List of Locks:
Lock Name : 0x01000000020000000100C014D6
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Variation
Data partition id : -1
Mode : S - Share
Lock Name : 0x02000400040000000000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 4
Object Type : Row
Tablespace Name : USERSPACE1
Table Schema : DB2INST1
Table Name : T1
Data partition id : 0
Mode : X - Exclusive
Lock Name : 0x4141414141454A696C171C12C1
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Plan
Data partition id : -1
Mode : S - Share
Lock Name : 0x02000400000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 2
Hold Count : 0
Lock Object Name : 4
Object Type : Table
Tablespace Name : USERSPACE1
Table Schema : DB2INST1
Table Name : T1
Data partition id : 0
Mode : IX - Intent Exclusive
Locks Held: 4
Locks in List: 4
Locks Displayed: 4
5>分析dlockmon.txt
--------------------------------------------------------------------------
3) Deadlock Event ...
Deadlock ID: 1 ------>decdlock ID是1
Number of applications deadlocked: 2 ----->参与deadlock应用2个
Deadlock detection time: 01/04/2019 22:00:07.407754
Rolled back Appl participant no: 2 ------>回滚的应用参与者编号是2
Rolled back Appl Id: *LOCAL.test.190104124319 ---->回滚的应用id是*LOCAL.test.190104124319
Rolled back Appl seq number: : 0011
5) Deadlocked Connection ...
Deadlock ID: 1
Participant no.: 2
Participant no. holding the lock: 1
Appl Id: *LOCAL.test.190104124319 ---->回滚的应用app id *LOCAL.test.190104124319
Appl Seq number: 00017
Appl Id of connection holding the lock: *LOCAL.test.190104124221---->拥有锁的应用app id
Seq. no. of connection holding the lock: 00001
Lock wait start time: 01/04/2019 21:59:47.811692
Lock Name : 0x02000400050000000000000052 ------>锁的行是id=2,可用上面方式分析得出
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 01/04/2019 22:00:07.407998
Table of lock waited on : TB1
Schema of lock waited on : TEST
Data partition id for table : 0
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
Mode of lock: X - Exclusive ----->拥有锁的应用*LOCAL.test.190104124221对该行有X锁
Mode application requested on lock: X - Exclusive --->回滚的应用 *LOCAL.test.190104124319也想申请X锁
Node lock occured on: 0
Lock object name: 5
Application Handle: 926
Deadlocked Statement:
Type : Dynamic
Operation: Execute Immediate
Section : 203
Creator : NULLID
Package : SQLC2H21
Cursor :
Cursor was blocking: FALSE
Text : update tb1 set name='yo22' where id=2 ---->发生deadlock的SQL语句
List of Locks:
Lock Name : 0x02000400040000000000000052 ---->同时回滚的应用 *LOCAL.test.190104124319拥有id=1行的x锁
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 4
Object Type : Row
Tablespace Name : USERSPACE1
Table Schema : TEST
Table Name : TB1
Data partition id : 0
Mode : X - Exclusive
6) Deadlock statement history ...
Deadlock ID : 1
Participant No : 2
Stmt history ID : 2
Statement text : update tb1 set name='yo22' where id=2---->回滚应用执行的SQL语句
7) Deadlock statement history ...
Deadlock ID : 1
Participant No : 2
Statement text : update tb1 set name='yo11' where id=1 ----->回滚应用执行的SQL语句https://blog.csdn.net/aryoyo/article/details/85848707
https://www.cndba.cn/hbhe0316/article/4776
https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0602jiangt/index.htmlhttps://www.cndba.cn/hbhe0316/article/4776
版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle
扫一扫
5993
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
