How to detect locks on Redshift

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 localize 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

If you liked this post, you can follow me there as I continue to document my journey.

Originally published at notadatascientist.com on February 11, 2016