Identifying Lock Conflicts
Posted: Mon May 07, 2012 2:48 pm
You can view any currently held locks by querying the LOCKS system table:
Example:
The type of lock held is listed in the LOCK_MODE column of the LOCKS table.
Example:
Code: Select all
dbadmin=> \x
Expanded display is on.
dbadmin=> select * from locks;
-[ RECORD 1 ]-----------+---------------------------------------------------------------
node_names | v_intersect_node0001,v_intersect_node0002,v_intersect_node0003
object_name | Table:test_lock
object_id | 45035996275781480
transaction_description | Txn: a0000000111e06 'insert into test_lock values (1);'
lock_mode | I
lock_scope | TRANSACTION
request_timestamp | 2012-05-07 09:35:50.639641-04
grant_timestamp | 2012-05-07 09:35:50.639645-04
- S --> Share lock needed for select operations
I --> Insert lock needed for insert operations
SI --> Share+Insert lock needed for operations that read and query the table. Distinguished from X because SI mode disallows delete/update operations. SI is also the result of lock promotion (see Table 2).
X --> Exclusive lock is always needed for delete operations. X lock is also the result of lock promotion (see Table 2)
T --> Tuple Mover lock used by the Tuple Mover and also used for COPY into pre-join projections
U --> Usage lock needed for moveout and mergeout operations in the first phase; they then upgrade their U lock to a T lock for the second phase. U locks conflicts with no other locks but O.
O --> Owner lock needed for DROP_PARTITION, TRUNCATE TABLE, and ADD COLUMN. O locks conflict with all locks. O locks never promote.