windows:
F:/>cd F:/DB2_WZGL/NODE0000/SQL00001/DB2EVENT/db2detaildeadlockF:/>db2 set event monitor db2detaildeadlock state=0
DB20000I SQL 命令成功完成。F:/>db2evmon -path F:/DB2_WZGL/NODE0000/SQL00001/DB2EVENT/db2detaildeadlock>deadlock.txt
Reading F:/DB2_WZGL/NODE0000/SQL00001/DB2EVENT/db2detaildeadlock/00000000.EVT ...
F:/>db2 set event monitor db2detaildeadlock state=1
DB20000I SQL 命令成功完成。
unix:
$ find . -name db2detaildeadlock
./inst16/NODE0000/SQL00001/db2event/db2detaildeadlock
$ db2evmon -path ./inst16/NODE0000/SQL00001/db2event/db2detaildeadlock>lock16.txtReading ./inst16/NODE0000/SQL00001/db2event/db2detaildeadlock/00000000.evt ...
Reading ./inst16/NODE0000/SQL00001/db2event/db2detaildeadlock/00000001.evt ...
$
$ grep "Deadlock detection time:" lock16.txt
Deadlock detection time: 06/10/2008 11:42:04.580880
Deadlock detection time: 06/10/2008 11:42:04.594876
Deadlock detection time: 06/10/2008 11:42:04.608274
Deadlock detection time: 06/10/2008 11:42:05.594945
Deadlock detection time: 06/10/2008 11:42:05.595020
Deadlock detection time: 06/10/2008 11:42:05.610197
.....
2、分析死锁监视器
--------------------------------------------------------------------------
Database Name: WZGL
Database Path: F:/DB2_WZGL/NODE0000/SQL00001/
First connection timestamp: 2008-06-22 10:59:53.632033
Event Monitor Start time: 2008-06-22 10:59:53.755018
--------------------------------------------------------------------------9793) Deadlock Event ...
Deadlock ID: 1
Number of applications deadlocked: 2
Deadlock detection time: 2008-06-23 10:45:36.282882
Rolled back Appl participant no: 2 ＃ 已做回滚处理的事务编号
Rolled back Appl Id: C0A80010.C106.080623024535
Rolled back Appl seq number: : 00019794) Connection Header Event ...
Appl Handle: 249
Appl Id: C0A80010.C106.080623024535
Appl Seq number: 0001
DRDA AS Correlation Token: C0A80010.C106.080623024535
Program Name : db2jcc_application
Authorization Id: WZGLADM
Execution Id : WZGLADM
Codepage Id: 1208
Territory code: 0
Client Process Id: 1019224320
Client Database Alias:
Client Product Id: JCC02100
Client Platform: Unknown via DRDA
Client Communication Protocol: TCPIP
Client Network Name:
Connect timestamp: 2008-06-23 10:45:30.3224189795) Deadlocked Connection ...
Deadlock ID: 1
Participant no.: 2 ＃ 事务编号 ，该事务在这次死锁处理中被回滚。
Participant no. holding the lock: 1
Appl Id: C0A80010.C106.080623024535
Appl Seq number: 0001
Appl Id of connection holding the lock: C0A80010.C006.080623024534
Seq. no. of connection holding the lock: 0001
Lock wait start time: 2008-06-23 10:45:30.359150
Lock Name : 0x04002D001C0800000000000052
Lock Attributes : 0x00000000
Release Flags : 0x00000001
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 2008-06-23 10:45:36.291269
Table of lock waited on : MAT_REQPLAN
Schema of lock waited on : WZGLADM
Tablespace of lock waited on : WZGL_SR_4KB
Type of lock: Row
Mode of lock: X - Exclusive
Mode application requested on lock: NS - Share (and Next Key Share)
Node lock occured on: 0
Lock object name: 2076 # 等待的锁定 此数字可以在下面其他事务保持的锁定里找到。
Application Handle: 249
Deadlocked Statement:
Type : Dynamic
Operation: Fetch
Section : 1
Creator : NULLID
Package : SYSSH200 # 经确认 该包的隔离级别为CS
Cursor : SQL_CURSH200C1
Cursor was blocking: FALSE
Text : select distinct mg.GATHERPLAN_ID # 引发死锁的sql
from MAT_GATHERPLAN as mg
left join MAT_MATERIAL as a
on mg.GATHERPLAN_ID=a.GATHERPLAN_ID
inner join MAT_REQPLAN b
on a.REQPLAN_ID=b.REQPLAN_ID
inner join MAT_PROJECT c
on b.PROJECT_ID=c.project_id
inner join MAT_THESAURUS d
on a.THESAURUS_ID=d.THESAURUS_ID
inner join MAT_MATTYPE m
on d.MATTYPE_ID=m.MATTYPE_ID
where 1=1
and b.CROP_CODE like '10%'
and ( mg.AUTHOR_ID=19688
and mg.WORKFLOW_STATE=1 )
and mg.URGENT_FLAG=0
List of Locks:...... [ 已略去不相干的锁列表 ]
Lock Name : 0x040024000C1600000000000052
Lock Attributes : 0x00000000
Release Flags : 0x00000001
Lock Count : 1
Hold Count : 0
Lock Object Name : 5644 # 此锁定正是 编号为1的事务所请求的
Object Type : Row
Tablespace Name : WZGL_SR_4KB
Table Schema : WZGLADM
Table Name : MAT_MATERIAL
Mode : NS - Share (and Next Key Share)9796) Connection Header Event ...
Appl Handle: 248
Appl Id: C0A80010.C006.080623024534
Appl Seq number: 0001
DRDA AS Correlation Token: C0A80010.C006.080623024534
Program Name : db2jcc_application
Authorization Id: WZGLADM
Execution Id : WZGLADM
Codepage Id: 1208
Territory code: 0
Client Process Id: 1019224320
Client Database Alias:
Client Product Id: JCC02100
Client Platform: Unknown via DRDA
Client Communication Protocol: TCPIP
Client Network Name:
Connect timestamp: 2008-06-23 10:45:30.1974449797) Deadlocked Connection ...
Deadlock ID: 1
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: C0A80010.C006.080623024534
Appl Seq number: 0001
Appl Id of connection holding the lock: C0A80010.C106.080623024535
Seq. no. of connection holding the lock: 0001
Lock wait start time: 2008-06-23 10:45:30.362735
Lock Name : 0x040024000C1600000000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 2008-06-23 10:45:36.299542
Table of lock waited on : MAT_MATERIAL
Schema of lock waited on : WZGLADM
Tablespace of lock waited on : WZGL_SR_4KB
Type of lock: Row
Mode of lock: NS - Share (and Next Key Share)
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 5644 ＃ 正等待 编号2的锁
Application Handle: 248
Deadlocked Statement:
Type : Dynamic
Operation: Execute Immediate
Section : 65
Creator : NULLID
Package : SYSSH100
Cursor :
Cursor was blocking: FALSE
Text : update MAT_MATERIAL set GATHERPLAN_ID=null # 引发死锁的sql
where 1=1
and GATHERPLAN_ID in ( 2005178,0 )
List of Locks:...... [ 已略去不相干的锁列表 ]
Lock Name : 0x04002D001C0800000000000052
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 2076 # 此锁定正是 编号为2的事务所请求的
Object Type : Row
Tablespace Name : WZGL_SR_4KB
Table Schema : WZGLADM
Table Name : MAT_REQPLAN
Mode : X - Exclusive