Redshift keeps lock on table after query completed

Question

Hi!  
  
I have the following situation:  
My teammate ran a query from Python, and received the data into DataFrame. So the query had been completed, but he didn't close the connection.  
  
I've tried to rename that table (using DBeaver client) and the query was running too long. When I checked the pg_locks table I found that those table was locked by my colleague.  
  
I have tested with queries from Python on my side using 2 different modules psycopg2 and pyodbc, and found that tables are locked in both cases after the query completed, until the connection is closed by me.  
  
Is it normal that Redshift keeps lock on table after the query had been completed?

Accepted Answer

Hi denisaleksandrov,  
  
I think you may find this knowledge center article helpful:  https://aws.amazon.com/premiumsupport/knowledge-center/prevent-locks-blocking-queries-redshift/  
  
I suspect there is an open transaction or perhaps an open cursor on the session doing the SELECT ... that is still holding an AccessShareLock on the table when your other session it trying to get an AccessExclusiveLock while doing the ALTER TABLE ... RENAME ...  
  
I hope this helps,  
-Kurt

Redshift keeps lock on table after query completed

Contenuto pertinente