DEADLOCK Tracing

Use the DEADLOCK component to trace the occurrences of deadlocks for all applications.

Table 2-3 describes the DEADLOCK tracing levels. Each level with a '+' sign includes the trace information described for that level, plus all levels preceding it.

Table 2-3 DEADLOCK tracing levels

Level	Output
1	Deadlock cycles, logged as they are discovered
4, 6	+ Detailed information about how the deadlock is detected

In this example, execute ttTraceMon to do a DEADLOCK trace at level 1, which is the default, on myDSN database.

Make two connections to myDSN. For the first connection, autocommit is on. Create table test and insert two rows. Then, set autocommit off and update the x1=1 row of table test. Because autocommit is off, the row is not inserted into the table until the commit. A lock is held until the transaction is committed or rolled back.

Command> create table test (x1 int unique, y1 int);
Command> insert into test values (1,1);
1 row inserted.
Command> insert into test values (2,2);
1 row inserted.
Command> autocommit 0;
Command> update test set y1=y1 where x1=1;
1 row updated.

For the second connection to myDSN, autocommit is set to off. Update the x1=2 row of table test.

Command> autocommit 0;
Command> update test set y1=y1 where x1=2;1 row updated.

Now, create a deadlock situation by executing update statements in both connections for rows that are locked by each other. The first connection executes an update against the row where x1=2.

Command> update test set y1=y1 where x1=2;
 6003: Lock request denied because of time-out
Details: Tran 2.1 (pid 32750) wants Un lock on rowid BMUFVUAAAAaAAAAETk, 
table ME.TEST. But tran 3.2 (pid 32731) has it in Xn (request was Xn).
Holder SQL (update t1 set y1=y1 where x1=2)
The command failed.

The second connection executes an update against the row where x1=1.

Command> update test set y1=y1 where x1=1;
 6002: Lock request denied because of deadlock
Details: Tran 3.2 (pid 32731) wants Un lock on rowid BMUFVUAAAAaAAAADzk, 
table ME.TEST. But tran 2.1 (pid 32750) has it in Xn (request was Xn). 
Holder SQL (update t1 set y1=y1 where x1=1)
The command failed.

Use the flush command to empty the buffer.

% ttTraceMon myDSN
Trace monitor; empty line to exit
Trace> flush

The trace buffer contains the following information showing all level 1 deadlock traces, as evidenced by '1L'.

Trace> dump
09:50:26.444      13 DEADLOCK 1L 2036C   3484P edge 1: xid 3.2, cid 3, 
<Row BMUFVUAAAAaAAAADzk,0x8c5
74(574836)> 0 cnt=1 , Tbl 'T1', SQL='update t1 set y1=y1 where x1=1'
09:50:26.455      14 DEADLOCK 1L 2036C   3484P edge 0: xid 2.1, cid 2, 
<Row BMUFVUAAAAaAAAAETk,0x8c5
74(574836)> 0 cnt=1 , Tbl 'T1', SQL='update t1 set y1=y1 where x1=2'
09:50:26.455      15 DEADLOCK 1L 2036C   3484P Victim: xcb:3.2, 
SQL='update t1 set y1=y1 where x1=1'

If you want more information, set DEADLOCK tracing to a higher value. For example, the following sets DEADLOCK tracing to level 4 in ttTraceMon:

Trace > level deadlock 4