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.