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