Identifying Lock Conflicts

[JimKnicely]

You can view any currently held locks by querying the LOCKS system 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

The type of lock held is listed in the LOCK_MODE column of the LOCKS table.

• 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.