测试环境：
db2 V9.5
参考书籍《db2数据库性能调整和优化》牛新庄著 255页
为了加强模拟效果，设置DLCHKTIME为300000 （ms），locktimeout 为-1 （无限等待）
打开三个clp窗口：
第一个窗口：
$db2 "create table deadtable( c1 integer)"
$db2 +c "insert into deadtable values(1)"
然后在第二个窗口：
$db2 +c "insert into deadtable values(2)"
$db2 +c "select * from deadtable"
出现等待
然后在第一个窗口，执行
$db2 +c "select * from deadtable"
出现等待
在第三个窗口中，打开snapshot监控，重置计数器，设置死锁事件监控器dlock
$db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on
$db2 reset monitor all
$db2 "create event monitor dlock for deadlocks with details write to file '/test'"
确认：
$ db2 get monitor switches
Monitor Recording Switches
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 06/28/2009 10:34:30.471334
Lock Information (LOCK) = ON 06/28/2009 21:58:36.539542
Sorting Information (SORT) = ON 06/28/2009 10:34:30.471334
SQL Statement Information (STATEMENT) = ON 06/28/2009 21:58:36.539542
Table Activity Information (TABLE) = ON 06/28/2009 10:34:30.471333
Take Timestamp Information (TIMESTAMP) = ON 06/20/2009 23:07:43.583010
Unit of Work Information (UOW) = ON 06/28/2009 10:34:30.471332
$ db2 "select evmonname, event_mon_state(evmonname) from syscat.eventmonitors"
EVMONNAME 2
-------------------------------------------------------------------------------------------------------------------------------- -----------
DLOCK 0
1 record(s) selected.
然后：
$db2 set event monitor dlock state 1
$db2 get snapshot for database on sample|grep -i deadlock
Deadlocks detected = 1
Internal rollbacks due to deadlock = 1
检测到了1个死锁。
$db2 set event monitor dlock state 0
$db2evmon -db sample -evm dlock
--------------------------------------------------------------------------
Database Name: SAMPLE
Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00002/
First connection timestamp: 06/28/2009 10:32:14.462494
Event Monitor Start time: 07/08/2009 00:46:28.225131
--------------------------------------------------------------------------
33) Deadlock Event ...
Deadlock ID: 11
Number of applications deadlocked: 2
Deadlock detection time: 07/08/2009 00:50:44.660011
Rolled back Appl participant no: 2
Rolled back Appl Id: *LOCAL.db2inst1.090628013221 --被回滚的应用
Rolled back Appl seq number: : 000f
34) Connection Header Event ...
Appl Handle: 3704
Appl Id: *LOCAL.db2inst1.090628013221
Appl Seq number: 00015
DRDA AS Correlation Token: *LOCAL.db2inst1.090628013221
Program Name : db2bp
Authorization Id: DB2INST1
Execution Id : db2inst1
Codepage Id: 819
Territory code: 1
Client Process Id: 868652
Client Database Alias: SAMPLE
Client Product Id: SQL09050
Client Platform: Unknown
Client Communication Protocol: Local
Client Network Name: testaix
Connect timestamp: 06/28/2009 10:32:21.711291
35) Deadlocked Connection ...
Deadlock ID: 11
Participant no.: 2
Participant no. holding the lock: 1
Appl Id: *LOCAL.db2inst1.090628013221
Appl Seq number: 00015
Appl Id of connection holding the lock: *LOCAL.db2inst1.090628013214 -持有锁的应用
Seq. no. of connection holding the lock: 00001
Lock wait start time: 07/08/2009 00:47:08.671752
Lock Name : 0x00030005000000000280000452
Lock Attributes : 0x00000000
Release Flags : 0x00000001
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 07/08/2009 00:50:44.660083
Table of lock waited on : DEADTABLE --引起死锁的表
Schema of lock waited on : DB2INST1
Data partition id for table : 0
Tablespace of lock waited on : IBMDB2SAMPLEREL
Type of lock: Row -引起死锁的是行锁，update语句已经拥有了排他锁，select语句想申请NS锁
Mode of lock: X - Exclusive
Mode application requested on lock: NS - Share (and Next Key Share)
Node lock occured on: 0
Lock object name: 41943044
Application Handle: 3704
Deadlocked Statement:
Type : Dynamic
Operation: Fetch
Section : 201
Creator : NULLID
Package : SQLC2G13
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : select * from deadtable --引起死锁的语句，也就是申请NS锁的语句
List of Locks:
Lock Name : 0x00000001000000010001A10056
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 : 0x00030005000000000280000552
Lock Attributes : 0x00000008
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 41943045
Object Type : Row
Tablespace Name : IBMDB2SAMPLEREL
Table Schema : DB2INST1
Table Name : DEADTABLE
Data partition id : 0
Mode : X - Exclusive
Lock Name : 0x53514C433247313333CEF3B641
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 : 0x00030005000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000001
Lock Count : 2
Hold Count : 0
Lock Object Name : 5
Object Type : Table
Tablespace Name : IBMDB2SAMPLEREL
Table Schema : DB2INST1
Table Name : DEADTABLE
Data partition id : 0
Mode : IX - Intent Exclusive
Locks Held: 4
Locks in List: 4
Locks Displayed: 4
36) Connection Header Event ...
Appl Handle: 3699
Appl Id: *LOCAL.db2inst1.090628013214
Appl Seq number: 00013
DRDA AS Correlation Token: *LOCAL.db2inst1.090628013214
Program Name : db2bp
Authorization Id: DB2INST1
Execution Id : db2inst1
Codepage Id: 819
Territory code: 1
Client Process Id: 811278
Client Database Alias: SAMPLE
Client Product Id: SQL09050
Client Platform: Unknown
Client Communication Protocol: Local
Client Network Name: testaix
Connect timestamp: 06/28/2009 10:32:14.462494
37) Deadlocked Connection ...
Deadlock ID: 11
Participant no.: 1
Participant no. holding the lock: 2
Appl Id: *LOCAL.db2inst1.090628013214
Appl Seq number: 00013
Appl Id of connection holding the lock: *LOCAL.db2inst1.090628013221
Seq. no. of connection holding the lock: 00001
Lock wait start time: 07/08/2009 00:47:17.547684
Lock Name : 0x00030005000000000280000552
Lock Attributes : 0x00000000
Release Flags : 0x00000001
Lock Count : 1
Hold Count : 0
Current Mode : none
Deadlock detection time: 07/08/2009 00:50:44.660288
Table of lock waited on : DEADTABLE
Schema of lock waited on : DB2INST1
Data partition id for table : 0
Tablespace of lock waited on : IBMDB2SAMPLEREL
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: 41943045
Application Handle: 3699
Deadlocked Statement:
Type : Dynamic
Operation: Fetch
Section : 201
Creator : NULLID
Package : SQLC2G13
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : select * from deadtable
List of Locks:
Lock Name : 0x00000001000000010001A10056
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 : 0x00030005000000000280000452
Lock Attributes : 0x00000008
Release Flags : 0x40000001
Lock Count : 1
Hold Count : 0
Lock Object Name : 41943044
Object Type : Row
Tablespace Name : IBMDB2SAMPLEREL
Table Schema : DB2INST1
Table Name : DEADTABLE
Data partition id : 0
Mode : X - Exclusive
Lock Name : 0x53514C433247313333CEF3B641
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 : 0x00030005000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000001
Lock Count : 2
Hold Count : 0
Lock Object Name : 5
Object Type : Table
Tablespace Name : IBMDB2SAMPLEREL
Table Schema : DB2INST1
Table Name : DEADTABLE
Data partition id : 0
Mode : IX - Intent Exclusive
Locks Held: 4
Locks in List: 4
Locks Displayed: 4
再来观察窗口2：
$ db2 +c "select * from deadtable"
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "2". SQLSTATE=40001
由于死锁引起事务回滚，代码就是sql0911 “2”
$ db2 ? sql0911
SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "".
Explanation:
The current unit of work was involved in an unresolved contention for
use of an object and had to be rolled back.
The reason codes are as follows:
2 transaction rolled back due to deadlock.
68 transaction rolled back due to lock timeout.
72 transaction rolled back due to an error concerning a DB2 Data
Links Manager involved in the transaction.
窗口1：
$ db2 +c "select * from deadtable"
C1
-----------
1
1 record(s) selected.
