SET LOCK_TRACE Statement
The SET LOCK_TRACE statement lets you start and stop lock tracing at any time during a session. This statement has the following syntax:
set [no]lock_trace
IMPORTANT! Because the LOCK_TRACE output may change, use SET LOCK_TRACE as a debugging or tracing tool only.
To use SET LOCK_TRACE you can:
• Issue the SET LOCK_TRACE statement from a terminal monitor session.
To start tracing locks:
set lock_trace;
To stop tracing locks:
set nolock_trace;
• Include the SET LOCK_TRACE statement in an embedded language program.
• Specify SET LOCK_TRACE with an environment variable or logical.
For example, to start lock tracing with ING_SET issue the following statement at the operating system prompt:
Windows:
set ING_SET=set lock_trace
Linux:
ING_SET="set lock_trace"
export ING_SET
When you use SET LOCK_TRACE during a session, information is displayed about locks used and released in your session.
If you use an environment variable/logical to set the LOCK_TRACE flag, you receive output for utility startup queries as well as for query language statements.
LOCK_TRACE Output
An example of lock_trace output is shown here. The column headings above the example are added in this guide to describe the output.
Action Type Qual. Mode Timeout Key
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (inv,iiattribute,21)
UNLOCK: PAGE Key: (inv,iiattribute,21)
LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (inv,iiindex,11)
UNLOCK: PAGE Key: (inv,iiindex,11)
LOCK: TABLE PHYS Mode: IS Timeout: 0 Key: (inv,parts)
LOCK: PAGE Mode: S Timeout: 0 Key: (inv,parts,0)
The lock_trace output is in the following format:
action level qualifiers Mode: Timeout: Key:
where:
Action
Is the action, which can be LOCK, UNLOCK, or CONVERT. For example, a lock was used (LOCK) or released (UNLOCK).
Type
Is the type of lock resource, which can be TABLE, PAGE, ROW, or VALUE. Other strings may appear, such as SV_PAGE or BM_DATABASE, which are internal cache control locks.
Qualifiers
Specify more information about the lock. The qualifier can be:
NOWT—Do not wait if the lock is unavailable.
PHYS—Lock can be released prior to end of transaction (physical lock).
Blank—Lock is held until the transaction commits or aborts (logical lock).
Other qualifiers that may appear have internal meaning only.
Mode
Is the lock mode. Values can be:
S = shared lock
U = update lock
X = exclusive lock
IS = intent share
IX = intent exclusive
N = null lock
SIX = shared intent exclusive
Timeout
Is the default timeout or the SET LOCKMODE TIMEOUT value.
Key
Describes the resource being locked. It consists of the database name, table name, partition and page number (shown as P.p where P is the physical partition number, and p is the page number), and (for row locking) the row number.
For VALUE level locks, the Key is database name, table name, and three numbers describing the value being locked. If the table is partitioned, the table name may be shown as an internal partition name, which looks like “iiXXX ppPPP-table name” where XXX is an internally assigned number, and PPP is the physical partition number. For example:
LOCK: TABLE PHYS Mode: IX Timeout: 0 Key: (emp,ii119 pp2-range_1)
LOCK_TRACE Example
The SET LOCK_TRACE output for the following transaction is shown here.
select * from parts where color = 'red';
update parts set price = 10 where partno = 11;
commit;
For clarity, output lines in this example are numbered and explained.
Note: If you run the same query several times, less SET LOCK_TRACE output is shown due to system catalog information being cached.
select * from parts where color = 'red'
+------+-------------+------+-----------+-----+
|partno|partname |color |wt |price|
----------------------------------------------
*********************************************************************************
(1) LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (inv,iirelation,11)
(2) LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (inv,iiattribute,21)
(3) UNLOCK: PAGE Key: (inv,iiattribute,21)
(4) LOCK: PAGE PHYS Mode: S Timeout: Key: (inv,iiattribute,19)
(5) UNLOCK: PAGE Key: (inv,iiattribute,19)
(6) UNLOCK: PAGE Key: (inv,iirelation,11)
(7) LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (inv,iiindex,11)
(8) UNLOCK: PAGE Key: (inv,iiindex,11)
(9) LOCK: TABLE PHYS: Mode: IS Timeout: 0 Key: (inv,parts)
(10)LOCK: PAGE Mode: S Timeout: 0 Key: (inv,parts,0)
*********************************************************************************
|1A12 |Truck |red |290.000 | $16.00 |
|1B5 |Bean bag |red |198.000 | $18.00 |
|20G |Laser |red |165.000 | $15.80 |
+-----+-------------+--------+----------+--------+
(3 rows)
update parts set price = 10 where partno = 20G
*********************************************************************************
(11)LOCK: TABLE PHYS Mode: IX Timeout: 0 Key: (inv,parts)
(12)LOCK: PAGE Mode: U Timeout: 0 Key: (inv,parts,0)
(13)LOCK: PAGE Mode: X Timeout: 0 Key: (inv,parts,0)
*********************************************************************************
(1 row)
commit
*********************************************************************************
(14)UNLOCK: ALL Tran-id: 092903CB0A7
*********************************************************************************
End of Request
The following is an explanation of the lock_trace output:
1. A shared physical lock was taken on page 11 of the iirelation table of the inv (inventory) database.
Physical locks are internal and are released as soon as possible.
2. A shared physical lock was taken on page 21 of the iiattribute table of the inventory database.
3. The lock on page 21 of the iiattribute table was released.
4. A shared physical lock was taken on page 19 of the iiattribute table of the inventory database.
5. The lock on page 19 of the iiattribute table was released.
6. The lock on page 11 of the iirelation table was released.
7. A shared physical lock was taken on page 11 of the iiindex table of the inventory database.
8. The lock on page 11 of the iiindex table was released.
9. An intent shared lock was taken on the parts table. This is the first lock on a user table.
10. A shared lock was taken on page 0 of the parts table.
11. An intent exclusive lock was taken on the parts table.
12. An update lock was taken on page 0 of the parts table.
13. An exclusive lock was taken on page 0 of the parts table.
14. All locks used during this transaction were released.