LOCK Tracing

Use the LOCK component to trace the locking behavior of your application to detect trouble with deadlocks or lock waits. LOCK tracing generates a great deal of volume, so it is important to choose the appropriate level of tracing. Level 3, for example, begins to generate a large number of traces, as traces are written for fairly common events. Also, the traces themselves may be hard to read in places. If you cannot discern enough information, contact TimesTen Customer Support for more information.

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

Table 2-4 LOCK tracing levels

Level	Output
1	Deadlock cycles as they are discovered
2	+ Failures to grant locks for any reason
3	+ Lock waits (which may or may not be granted)
4	+ All lock grants/releases, some routine calls, and the mechanism of the deadlock detector
6	+ Each step in cycle traversal

In this example, execute ttTraceMon to do a LOCK trace at level 4 on myDSN database.

Make two connections to myDSN. For the first connection, set autocommit on. Create table test and insert three rows. Create a materialized view using table test.

Turn on tracing at level 4 and use the flush command to empty the buffer.

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

For the second connection to myDSN, set autocommit off. Insert a row into 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.

If the dump command is used to display the contents of the trace buffer, there are no records in the trace buffer:

Trace> dump
0 records dumped

From the first connection, try to drop the materialized view. The view cannot be dropped because there is a transaction that has not been committed or rolled back:

Command> drop materialized view v;
 6003: Lock request denied because of time-out
Details: Tran 3.71 (pid 24524) wants Sn lock on table TTUSER.TEST. But tran 1.42 
(pid 24263) has it in IXn (request was IXn). Holder SQL (insert into test 
values (100);)
The command failed.

The trace buffer contains the following information:

Trace> dump
20:09:04.789  174759 LOCK     3L    3C  24524P ENQ: xcb:00003 <Tbl 0x9b894,0x0>
0+Sn=>SL activity 0 Sn cnt=0; Holder xcb:00001 IXn
20:09:04.789  174760 LOCK     3L    3C  24524P Connection 3 scheduled for sleep
20:09:04.789  174761 LOCK     3L    3C  24524P Connection 3 sleeping
20:09:14.871  174762 LOCK     3L 2047C  24237P Connection 3 timed out
20:09:14.871  174763 LOCK     3L 2047C  24237P Connection 3 woken up
20:09:14.871  174764 LOCK     3L    3C  24524P Connection 3 awake
20:09:14.871  174765 LOCK     2L    3C  24524P ENQ: xcb:00003 <Tbl 0x9b894,0x0>
0+Sn=>TM activity 0 Sn cnt=1; Holder xcb:00001 IXn
7 records dumped

When finished with the trace, set LOCK tracing back to its default setting (0) and exit ttTraceMon:

Trace > level lock 0
Trace > {press ENTER – blank line}