Recently I was using the psycopg2 library in a periodic task that I was using to alert myself when a website went down. There was a single query that selected a few rows from a table and that’s it. I’ll show some code that looks similar to what I was doing below.
Anyways, I realized that an extra column was necessary, but when I tried
ALTER query, it would hang indefinitely manually killing it. After
some searching around the Internet, I found the following query which
illuminated the unintentional lock on my table.
db=> select * from pg_locks where relation=(select oid from pg_class where
When run against my Postgres instance, saw something like the following showed
up. While I’m not 100% sure about all the details here (
example), it was enough to make me start looking into my code to see what was
holding the table in contention.
The code that was accessing the query looked something along the lines of this:
cursor.execute("SELECT name, last_received FROM heartbeater WHERE
hb_list = cursor.fetchall():
for heartbeat in hb_list:
# do some logic here
get_heartbeats was part of a celery task that would
run every minute or so and supplied the DB connection and cursor. As you can
see, I never called
connection.commit() after executing and fetching the data
from my query. Since this was a long running process, the session remained idle
and open, and ultimately locked the table from outside queries and manipulations.
Once I did some digging in the psycopg2 documentation, I found that my usage was wrong. Quoted from the documentation below:
By default even a simple SELECT will start a transaction: in long-running programs, if no further action is taken, the session will remain “idle in transaction”, an undesirable condition for several reasons (locks are held by the session, tables bloat…). For long lived scripts, either make sure to terminate a transaction as soon as possible or use an autocommit connection.
To remedy the mistake, I simply called
connection.commit() at the end of each
function call. After redeploying, the long-term locks were removed and there
were no further issues. If you have any long-running processes making queries
psycopg2, double-check to make sure that you are closing transactions
promptly either explicitly or by using the autocommit property.