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