Db transactions and locking
frank at chagford.com
Thu Nov 27 11:24:39 CET 2014
I just learned something about database transactions, locking, and DB-API
I wondered why a PostgreSQL statement was hanging. On investigation, it was
waiting to acquire a lock. On further investigation, the lock was held by a
simple SELECT statement. This surprised me.
I got a clue from a message on the PostgreSQL mailing list -
"PostgreSQL by default commits between each statement unless you explicitly
start a transaction."
All Python database adaptors that I have used start a transaction when you
open a cursor. I have just re-read DB-API 2.0, and I cannot see anything
that specifies this behaviour, but AFAICT this is what happens.
I started to monitor the locks that PostgreSQL holds while I was running my
application, and sure enough, the lock table grew and grew, even though I
was only issuing SELECTs. I use a connection pool, so I never actually close
the connections. If I did I am fairly sure the locks would be released.
I changed my application to call conn.commit() every time I return a
connection back to the pool, and PostgreSQL shows all the locks being
released straight away, so I think this has solved the problem.
I don't know if Sql Server and sqlite3 behave the same, but I don't think it
can do any harm, so I let it apply across the board.
Can anyone see any problem with this?
More information about the Python-list