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.

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.

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

Resolution

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.

Detect the locks

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

SELECT
  a.txn_start,
  _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,
  a.txn_owner,
  a.txn_db,
  a.pid,
  a.xid,
  a.lock_mode,
  a.relation AS table_id,
  nvl(_trim_(c."table"),d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid
FROM SVV_TRANSACTIONS a
LEFT JOIN (SELECT pid, relation, granted FROM PG_LOCKS GROUP BY 1,2,3) b
  ON a.relation = b.relation AND a.granted='f' AND b.granted='t'
LEFT JOIN (SELECT * FROM SVV_TABLE_INFO) c
  ON a.relation = c.table_id
LEFT JOIN PG_CLASS d
  ON a.relation = d.oid
WHERE
  a.relation IS NOT NULL
  AND txn_db = '<database name>'
  AND tablename = '<table name>';

Note: You can run the preceding query on both an Amazon Redshift provisioned cluster and Amazon Redshift Serverless.

Example output:

         txn_start         |        txn_duration         | txn_owner | txn_db |    pid     |   xid   |      lock_mode      | table_id | tablename | granted | blocking_pid 
---------------------------+-----------------------------+-----------+--------+------------+---------+---------------------+----------+-----------+---------+--------------
 2025-02-07 15:22:54.62833 | 0 days 0 hrs 3 mins 46 secs | admin     | dev    | 1073905801 | 3950326 | AccessExclusiveLock |  1410058 | abctbl    | t       |             
 2025-02-07 15:22:57.67816 | 0 days 0 hrs 3 mins 43 secs | admin     | dev    | 1073963119 | 3950380 | AccessShareLock     |  1410058 | abctbl    | f       |   1073905801

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_pid column shows the process ID (PID) of the session that holds the lock.

Detect the issue query

If the lock issue for the specific table occurs consistently, then use SYS_QUERY_HISTORY to see which query causes the issue.

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

Release locks

To release the locks, complete the following steps:

1. Wait until the transaction that holds the lock is released.
2. Run the PG_TERMINATE_BACKEND function to release the lock manually.
   Note: The PG_TERMINATE_BACKEND(PID) query returns the value 1 when the command successfully requests a process stop. It's a best practice to check SYS_SESSION_HISTORY to confirm that the process is stopped.
3. For a Redshift provisioned cluster, if PG_TERMINATE_BACKEND doesn't successfully stop the process, then run the REBOOT_CLUSTER function.
   Note: REBOOT_CLUSTER reboots the cluster without closing the connections.
4. For a Redshift provisioned cluster, if REBOOT_CLUSTER doesn't successfully stop the process, then reboot the cluster from the Amazon Redshift console or run the reboot-cluster AWS CLI command.
   Note: reboot-cluster closes all the current connections.