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 -- https://mail.python.org/mailman/listinfo/python-list
