Use the STV_LOCKS table to view any current updates on tables in the database.
Amazon Redshift locks tables to prevent two users from updating the same table at the same time. While the STV_LOCKS table shows all current table updates, query the STL_TR_CONFLICT table to see a log of lock conflicts. Use the SVV_TRANSACTIONS view to identify open transactions and lock contention issues.
STV_LOCKS is visible only to superusers. For more information, see Visibility of data in system tables and views.
Table columns
| Column name | Data type | Description |
|---|---|---|
| table_id | bigint | Table ID for the table acquiring the lock. |
| last_commit | timestamp | Timestamp for the last commit in the table. |
| last_update | timestamp | Timestamp for the last update for the table. |
| lock_owner | bigint | Transaction ID associated with the lock. |
| lock_owner_pid | bigint | Process ID associated with the lock. |
| lock_owner_start_ts | timestamp | Timestamp for the transaction start time. |
| lock_owner_end_ts | timestamp | Timestamp for the transaction end time. |
| lock_status | character (22) | Status of the process either waiting for or holding a lock. |
Sample query
To view all locks taking place in current transactions, type the following command:
select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;This query returns the following sample output, which displays three locks currently in effect:
table_id | last_update | lock_owner | lock_owner_pid
----------+----------------------------+------------+----------------
100004 | 2008-12-23 10:08:48.882319 | 1043 | 5656
100003 | 2008-12-23 10:08:48.779543 | 1043 | 5656
100140 | 2008-12-23 10:08:48.021576 | 1043 | 5656
(3 rows)