How do I find and release locks in Amazon Redshift?

Short description

You might experience lock conflicts when you perform frequent data definition language (DDL) statements on user tables or data manipulation language (DML) queries.

Amazon Redshift has three lock modes:

• AccessExclusiveLock blocks all other lock attempts. A transaction obtains this lock primarily during DDL operations, such as ALTER TABLE, DROP, or TRUNCATE.
• AccessShareLock blocks only AccessExclusiveLock attempts. A transaction obtains this lock during UNLOAD, SELECT, UPDATE, or DELETE operations. AccessShareLock doesn't block other sessions that try to read or write on the table.
• ShareRowExclusiveLock blocks AccessExclusiveLock and other ShareRowExclusiveLock attempts, but doesn't block AccessShareLock attempts. A transaction obtains ShareRowExclusiveLock during COPY, INSERT, UPDATE, or DELETE operations.

Resolution

Troubleshoot a lock issue that's live

To identify processes that hold locks, run the following query:

SELECT a.txn_owner AS user_name, a.txn_db AS database_name, a.pid AS session_id, 
    a.xid AS transaction_id,a.txn_start AS txn_start_time, a.lock_mode, a.relation AS table_id,
    nvl(trim(c.relname), '') AS table_name, a.granted, b.pid AS blocking_session_id,
    datediff(s, a.txn_start, getdate())/86400 || ' days ' || 
    datediff(s, a.txn_start, getdate())%86400/3600 || ' hrs ' || 
    datediff(s, a.txn_start, getdate())%3600/60 || ' mins ' || 
    datediff(s, a.txn_start, getdate())%60 || ' secs' AS txn_duration
FROM svv_transactions a
LEFT JOIN (SELECT pid, relation, granted FROM pg_locks GROUP BY pid, relation, granted) b 
    ON a.relation = b.relation AND a.granted = 'f' AND b.granted = 't'
LEFT JOIN pg_class c ON a.relation = c.oid
WHERE a.relation IS NOT NULL
ORDER BY a.txn_start;

To detect locks on a specific table, add the following condition to the query:

AND txn_db = 'database_name'
AND table_name = 'table_name'

Note: Replace database_name and table_name with your database name and table name. A transaction that waits to acquire an AccessExclusiveLock on an Amazon Redshift table blocks all subsequent lock requests on the same table, including read-only operations. The transaction blocks requests until Amazon Redshift grants or cancels the lock.

Example output:

 user_name | database_name | session_id | transaction_id |       txn_start_time       |       lock_mode       | table_id |    table_name    | granted | blocking_session_id |        txn_duration         
-----------+---------------+------------+----------------+----------------------------+-----------------------+----------+------------------+---------+---------------------+-----------------------------
 awsuser   | dev           | 1073766894 |       30221126 | 2025-07-24 10:35:47.002935 | AccessShareLock       |  2566002 | abctbl           | t       |                     | 0 days 0 hrs 4 mins 1 secs
 awsuser   | dev           | 1073750606 |       30221311 | 2025-07-24 10:36:31.738104 | AccessExclusiveLock   |  2566002 | abctbl           | f       |          1073766894 | 0 days 0 hrs 3 mins 17 secs
 awsuser   | dev           | 1073914531 |       30221402 | 2025-07-24 10:36:59.541617 | AccessShareLock       |  2566002 | abctbl           | f       |          1073766894 | 0 days 0 hrs 2 mins 49 secs
 awsuser   | dev           | 1073930946 |       30221657 | 2025-07-24 10:38:01.775147 | ShareRowExclusiveLock |  2566002 | abctbl           | f       |          1073766894 | 0 days 0 hrs 1 mins 47 secs

If the value in the granted column is f, then the transaction waits because another transaction acquires the lock on the table. The blocking_session_id column shows the session_id of the corresponding transaction that holds the lock.

Detect the blocking transaction

You can check the blocking_session_id column to get session details for a transaction that's active and holds the lock. To view user query details, run the SYS_QUERY_HISTORY command:

select user_id, query_id, transaction_id, session_id, database_name,query_type,status,start_time,end_time, error_message, query_text, trim(username) username from sys_query_history where session_id = 1073766894 order by start_time desc; 

Example output:

 user_id | query_id | transaction_id | session_id | database_name | query_type |   status   |         start_time         |          end_time          | error_message |             query_text             | username 
---------+----------+----------------+------------+---------------+------------+------------+----------------------------+----------------------------+---------------+------------------------------------+----------
     100 | 27394364 |       30221126 | 1073766894 | dev           | SELECT     | success    | 2025-07-24 10:35:46.991647 | 2025-07-24 10:35:51.69748  |               | SELECT * FROM abctbl WHERE id = 1; | awsuser
     100 | 27394361 |       30221126 | 1073766894 | dev           | UTILITY    | success    | 2025-07-24 10:35:41.285316 | 2025-07-24 10:35:41.286035 |               | BEGIN;                             | awsuser

Note: In the preceding example, the query ran in a transaction block that started with the BEGIN command. To end the transaction, run the COMMIT command.

Check the last query timestamp and status. If no queries are running but the transaction remains active with locks, then the output might show an idle transaction that didn't roll back.

Resolve lock conflicts

If the blocking transaction is active and in the running status, then wait for the transaction to complete. When the transaction completes, locks release automatically.

To end the blocking session and release the lock, use the PG_TERMINATE_BACKEND command. If the session doesn't end, then contact AWS Support.

To verify that the session has ended, run the SYS_SESSION_HISTORY command. To monitor the transaction status, run the SYS_TRANSACTION_HISTORY command.

To resolve an idle session timeout, run the ALTER USER command. Modify the session_timeout parameter.

Review previous lock issues

To identify when the lock wait time is high, use Query performance summary. Run the following query to identify queries that waited to acquire locks on particular tables during that timeframe:

SELECT 
query_id,
    user_id,
    trim(username) username,
    session_id,
    transaction_id,
    start_time,
    end_time,
    database_name,
    query_type,
    status,
    elapsed_time/1000000 AS elapsed_time_seconds,
    lock_wait_time/1000000 AS lock_wait_time_seconds
FROM SYS_QUERY_HISTORY where start_time between '2025-07-24 10:20:00' AND '2025-07-24 11:10:00'   — REPLACE WITH YOUR TIMESTAMP RANGE
-- WHERE QUERY_TEXT ILIKE '%TABLE_NAME%'
order by lock_wait_time desc limit 30;

Example output:

query_id | user_id | username | session_id | transaction_id |         start_time         |          end_time          | database_name | query_type |   status   | elapsed_time_seconds | lock_wait_time_seconds 
----------+---------+----------+------------+----------------+----------------------------+----------------------------+---------------+------------+------------+----------------------+------------------------
 27394382 |     100 | awsuser  | 1073750606 |       30221311 | 2025-07-24 10:36:31.737601 | 2025-07-24 11:05:12.77167  | dev           | DDL        | success    |                 1721 |                   1720
 27394389 |     100 | awsuser  | 1073914531 |       30221402 | 2025-07-24 10:36:59.541097 | 2025-07-24 11:05:18.53392  | dev           | SELECT     | success    |                 1698 |                   1693
 27394415 |     100 |          | 1073930946 |       30221657 | 2025-07-24 10:38:01.775054 | 2025-07-24 11:05:21.347414 | dev           | UPDATE     | success    |                 1639 |                   1631
 27394714 |     100 | awsuser  | 1073856846 |       30224605 | 2025-07-24 10:50:51.054534 | 2025-07-24 10:50:53.442305 | dev           | SELECT     | success    |                    2 |                      0
 27395099 |     100 | awsuser  | 1073856846 |       30228082 | 2025-07-24 11:05:18.67457  | 2025-07-24 11:05:19.347451 | dev           | SELECT     | success    |                    0 |                      0