sys.lock

This system view shows the current locks on all tables in the database. You can use this view to find lock conflicts on tables.

Column Name Data Type Description
table_id bigint Unique ID of the table.
database_id bigint Unique ID of the database.
object_type text Always TABLE.
session_id integer Unique database session ID.
is_granted boolean Whether the requested lock was granted.
lock_type text Type of lock requested.
blocked_by_session_id integer Session ID that is blocking the requested lock (NULL if not blocked).

Examples

yellowbrick_test=# select * from sys.lock 
order by table_id, database_id;
 table_id  | database_id | object_type | session_id | is_granted |        lock_type         | blocked_by_session_id 
-----------+-------------+-------------+------------+------------+--------------------------+-----------------------
      1247 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      1259 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      1260 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      1262 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      2615 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      2662 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      2663 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      2671 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      2672 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      2676 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      2677 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      2684 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      2685 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      2703 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      2704 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      2840 |       17106 | TABLE       |       4867 | t          | ShareUpdateExclusiveLock |                      
      2841 |       17106 | TABLE       |       4867 | t          | RowExclusiveLock         |                      
      2964 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      2965 |           0 | TABLE       |      16175 | t          | AccessShareLock          |                      
      3455 |       17106 | TABLE       |      39525 | t          | AccessShareLock          |                      
      5003 |           0 | TABLE       |      35946 | t          | RowExclusiveLock         |                      
      5003 |           0 | TABLE       |      35946 | t          | AccessShareLock          |                      
...
yellowbrick_test=# select * from sys.lock 
where blocked_by_session_id is not null;
 table_id | database_id | object_type | session_id | is_granted |    lock_type    | blocked_by_session_id 
----------+-------------+-------------+------------+------------+-----------------+-----------------------
  1812902 |       17106 | TABLE       |      28200 | f          | AccessShareLock |                 28264
    17137 |       17106 | TABLE       |      28770 | f          | ExclusiveLock   |                 35873
    17137 |       17106 | TABLE       |      28770 | f          | ExclusiveLock   |                 35873
(3 rows)
yellowbrick_test=# select sct.table_id, sct.name, ssl.lock_type 
from sys.table sct join sys.lock ssl on sct.table_id=ssl.table_id and sct.database_id=ssl.database_id;
 table_id |                  name                  |    lock_type     
----------+----------------------------------------+------------------
    56889 | ThirdPartyDataGeneralPopulationOverlap | AccessShareLock
    17395 | TargetingDataUniqueThirdPartyUsers     | AccessShareLock
    17395 | TargetingDataUniqueThirdPartyUsers     | ExclusiveLock
    17386 | TargetingData                          | AccessShareLock
    56865 | LookAlikeModelResult                   | AccessShareLock
    56865 | LookAlikeModelResult                   | ExclusiveLock
...