Db transactions and locking

[Frank Millman]

Hi all

I just learned something about database transactions, locking, and DB-API 
2.0.

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?

Frank Millman