How to detect locks on Redshift

When a query or transaction acquires a lock on a table, the lock remains for the duration of the query or transaction. Other queries or transactions that are waiting to acquire the same lock are blocked.

When you take a look to Redshift documentation they recommend you using STV_LOCKS, which results on:

It seems really useful until you have a real database lock. Last month I was trying to solve a lock that was blocking lots of processes. Finally, I found a better way to locate the queries that are causing locks:

Here you have the query itself:

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();

source

You can also find me on Twitter if you’d like to read similar technical tricks!