Here is an issue we see frequently:
User A connects to Redshift with AutoCommit OFF. User A selects from table T. User B try’s to drop table T and even though User B owns table T, the drop is blocked by the User A – as User A did not connect with AutoCommit On/True and the AccessShareLock obtained by User A, on table T is held until User A’s session is terminated.
This situation can be avoided (or at least mitigated) by User A connecting with AutoCommit On/True. Then and only then is the AccessShareLock more temporary – as it is only held while the select is executing and released on completion of the select statement execution.
https://aws.amazon.com/premiumsupport/knowledge-center/prevent-locks-blocking-queries-redshift/
Per AWS:
Amazon Redshift has three lock modes:
- 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.
Here is a script that will show locks held and process ids etc.
-- You can a view from this… select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id, nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid, 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 1,2,3) b on a.relation=b.relation and a.granted='f' and b.granted='t' left join (select * from stv_tbl_perm where slice=0) c on a.relation=c.id left join pg_class d on a.relation=d.oid where a.relation is not null and a.lock_mode is not null;
Here is the SuperUser syntax to kill the session holding the lock.
select pg_terminate_backend( pid );
Additionally, a only a Redshift SuperUser can terminate any process holding a on a table. We have created “Definers Rights” stored procedures to allow non-SuperUsers to terminate sessions holding locks on table/objects the invoker owns.