CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftdeadlock
Harry Su
Harry Su

Reputation: 169

Possible deadlock in redshift

I have two redshift queries which seems to block each other, So I am suspecting there's potential deadlock

query1 which is a query in a ETL pipeline

DROP TABLE IF EXISTS temp_table;
CREATE TABLE temp_table AS SELECT * FROM sometable;
BEGIN;
ALTER TABLE table_a RENAME TO temp_old_table;
ALTER TABLE temp_table RENAME TO table_a;
END;
DROP TABLE IF EXISTS temp_old_table;

query2 is adhoc query;

select * from table_a;

query1 and query2 run at the same time. Not sure which query run first. But for whatever reason, both query get stuck. here're the lock situation in pg_locks: query2 has AccessShareLock on table_a, granted true query1 is waiting for AccessExclusiveLock on table_a, granted false

Since query2 already has AccessShareLock, it should be able to move forward and query1 should finish too.

The place looks suspicious is that query1 is not a single transaction. It might try to acquire lock couple of times and query2 may acquire the lock in between. Is there any possible scenarios that deadlock can happen between these two query?

Upvotes: 1

Views: 5171

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269410

From How to Prevent Locks from Blocking Queries in Amazon Redshift:

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.

When a table acquires a lock, the lock remains until you finish the transaction with COMMIT or ROLLBACK. Transactions that are waiting for locks can block subsequent transactions that are also waiting to acquire the same locks. This can lead to lock enqueuing on the cluster.

It is strange that a SELECT would cause blocking.

If you are using an SQL client, turn on auto-commit.

Upvotes: 3

Related Questions