I have a table on a PostgreSQL 9.1 server like this:
CREATE TABLE foo(id integer PRIMARY KEY);
In an interactive Python shell with psycopg2 (≥ 2.4.2), I might start a connection and cursor, and query this table:
import psycopg2
conn = psycopg2.connect('dbname=...')
curs = conn.cursor()
curs.execute('SELECT * FROM foo;')
curs.fetchall()
However, if I then try to modify the table:
ALTER TABLE foo ADD COLUMN bar integer;
this starts a virtual deadlock until I do conn.close()
from Python.
How can I start a simple connection with psycopg2 that prevents it from creating deadlocks caused by DDL changes elsewhere? The connection in Python can be read-only.
The PostgreSQL LOCK command enables manual locking. Note: There is no equivalent command for unlocking a PostgreSQL table; locks are automatically released at the end of a transaction.
There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions. PostgreSQL supports that too; see SET TRANSACTION for details.
The SELECT command acquires a lock of this mode on all tables on which one of the FOR UPDATE , FOR NO KEY UPDATE , FOR SHARE , or FOR KEY SHARE options is specified (in addition to ACCESS SHARE locks on any other tables that are referenced without any explicit FOR ... locking option).
The solution I found is to use set_session like this:
conn.set_session(readonly=True, autocommit=True)
The documentation for autocommit
warns:
By default, any query execution, including a simple SELECT will start a transaction: for 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 ensure to terminate a transaction as soon as possible or use an autocommit connection.
This sums up the experience with a simple SELECT in the question.
Just as an FYI, I ran into this very same issue doing concurrent writing with psycopg2. The documentation states:
Transactions are handled by the connection class. By default, the first time a command is sent to the database (using one of the cursors created by the connection), a new transaction is created. The following database commands will be executed in the context of the same transaction – not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection. Should any command fail, the transaction will be aborted and no further command will be executed until a call to the rollback() method.
Basically psycopg2 locks the table for all transactions using the same connection.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With