Redshift LOCK

Overview

There are three LOCK mode:

AccessExclusiveLock: Acquired primarily during DDL operations, such as ALTER TABLE, DROP, or TRUNCATE. AccessExclusiveLock blocks all other locking attempts.

AccessShareLock: Acquired during UNLOAD, SELECT, UPDATE, or DELETE operations. AccessShareLock blocks only AccessExclusiveLock attempts. AccessShareLock doesn’t block other sessions that are trying to read or write on the table.

ShareRowExclusiveLock: Acquired during COPY, INSERT, UPDATE, or DELETE operations. ShareRowExclusiveLock blocks AccessExclusiveLock and other ShareRowExclusiveLock attempts but doesn’t block AccessShareLock attempts.

Troubleshooting

In case the table is under an AccessShareLock caused by unexpected queries, here is how to solve the problem: :

1. Run a query to identify sessions that are holding locks.
2. Use pg_terminate_backend to terminate the session that is holding the lock.