Redshift locked tables

Redshift: Detect and Cancel Long running queries Per User Connection

Troubleshooting in Redshift

Show locked tables, highlight the query and run it, copy the pid

Show Locked Tables

/* Show locked Tables */
SELECT 
  current_time, 
  c.relname, 
  l.database, 
  l.transaction, 
  l.pid, 
  a.usename, 
  l.mode, 
  l.granted
FROM pg_locks l 
JOIN pg_catalog.pg_class c ON c.oid = l.relation
JOIN pg_catalog.pg_stat_activity a ON a.procpid = l.pid
WHERE l.pid <> pg_backend_pid();Show locks, oldest first

Kill Session (note change "pid" to a process with a lock from above)

/* kill session, Redshift ends session and rolls back all transactions that are associated with it. */
select pg_terminate_backend( pid )

Show locks, oldest first

/* show locks oldest first */
select 
  table_id, 
  last_update, 
  last_commit, 
  lock_owner_pid, 
  lock_status 
from stv_locks 
order by last_update asc