How do I detect and release locks in Amazon Redshift?

Short description

You might experience locking conflicts when you perform frequent Data Definition Language (DDL) statements on user tables or Data Manipulation Language (DML) queries in Amazon Redshift. This guide explains how to identify lock conflicts, detect blocking transactions, and resolve lock-related issues using various methods on your Redshift cluster and Serverless.

Amazon Redshift has the following three lock modes:

• AccessExclusiveLock blocks all other locking attempts and is obtained primarily during DDL operations, such as ALTER TABLE, DROP, or TRUNCATE.
• AccessShareLock blocks only AccessExclusiveLock attempts and is obtained 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. ShareRowExclusiveLock is obtained during COPY, INSERT, UPDATE, or DELETE operations.

Resolution

To resolve this issue, identify the issue table locks, identify the issue query (if necessary), and then release the issue table locks.

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI . Also, make sure that you're using the most recent AWS CLI version.

1. WHEN THE LOCKING ISSUE IS 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 the locks on a specific table, please add below condition to the above query before ORDER BY by replacing the database_name and table_name of the concerned table.

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

Note: A queued AccessExclusiveLock in Redshift blocks all subsequent lock requests (including read-only operations) until it's either granted or canceled. The locking order is based on the transaction start time.

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 result in the granted column is f (false), then the transaction is waiting for locks because the lock is held by another transaction. The blocking_session_id column shows the session_id (PID) of the session that holds the lock.

Detect the blocking transaction:

Please note that the relation locks are hold at the transaction level and not at session or query level. Locks are released when transaction ends (rollback or committed).

• Using the blocking session_id, we can get underlying transaction details which is active and currently holding the lock using SYS_QUERY_HISTORY:

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; 

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

• Check the last query timestamp and status. If no queries are running but the transaction remains active with locks, it likely indicates an idle transaction that hasn't been committed or rolled back.
• In the above scenario, query has been run in transaction mode with BEGIN and it require COMMIT to END the transaction properly.

You can use below query to get the open query transactions for 5+ mins(only for Provisioned clusters):

SELECT distinct t.xid transaction_id, 
t.pid session_id, 
i.query query_id,
t.txn_start txn_start_time, 
DATEDIFF(second, txn_start, GETDATE()) as txn_duration_in_secs
FROM svv_transactions t
LEFT JOIN stv_inflight i ON t.pid = i.pid
WHERE i.query IS NULL -- No active query for this transaction
AND DATEDIFF(second, txn_start, GETDATE()) > 300 -- running for more than 5 minutes
ORDER BY running_time_seconds DESC;

output:

transaction_id | session_id | query_id | txn_start_time | txn_duration_in_secs 
----------------+------------+----------+----------------+----------------------
 30221126 | 1073766894 |       | 2025-07-24 10:35:47.002935 |                  846
 30221311 | 1073750606 |       | 2025-07-24 10:36:31.738104 |                  802
 30221402 | 1073914531 |       | 2025-07-24 10:36:59.541617 |                  774
 30221657 | 1073930946 |       | 2025-07-24 10:38:01.775147 |                  712

Resolving Lock Conflicts

Based on the criticality of the blocking transaction (identified by blocking_session_id), you can choose from the following resolution methods (in order of increasing impact):

1. Wait for Automatic Release

   • If the blocking transaction is active and running as per status in SYS_QUERY_HISTORY, wait for its natural completion
   • Locks will be released automatically upon transaction completion.

2. Terminate the Session

   • Use PG_TERMINATE_BACKEND to abort the blocking session to clear blockage

   SELECT PG_TERMINATE_BACKEND(blocking_session_id);
   -- Example: SELECT PG_TERMINATE_BACKEND(1073766894);
   

   • Verify termination in SYS_SESSION_HISTORY
   • Monitor transaction status in SYS_TRANSACTION_HISTORY as transaction rollback take sometime to release the lock.

Note:

• If PG_TERMINATE_BACKEND fails to abort the session, rebooting the cluster will clear all sessions and resolve the blocking issue
• However, it's recommended to contact AWS Support before taking this action of rebooting cluster. This allows AWS Support to gather critical diagnostic information and investigate the root cause of the locking issue, helping prevent similar issues in the future.
• Only proceed with cluster reboot if immediate resolution is required for business operations.

2. WHEN THE LOCKING ISSUE IS NOT LIVE:

You can use Enhanced Query Monitoring to identify the time period when lock wait time was high and then run below query to identify the queries/transactions that were waiting to acquire locks on particular tables in the issue timeframe.

• Detect the transactions that were running and holding the locks on the relation(table) during the concerned period with highest lock wait time:

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 DATE RANGE
-- WHERE QUERY_TEXT ILIKE '%TABLE_NAME%'
order by lock_wait_time desc limit 30;

Note: If concern is regarding a particular table, you can include the where condition and replace table name in the above query.

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