Disable autocommit for new connections

[Drew Smathers]

I could very well be wrong on this, but from my understanding, autocommit should be disabled for databases such as MySQL that have this feature to be compliant with python's DB-API. I had an agonizing time discovering this when trying to get test cases to rollback when run on oursql. Perhaps oursql, similar to MySQLdb should set autocommit off for new cursors.

[Chris Petersen (geek+launchpad)]

SET autocommit='OFF' is a connection setting, not a cursor setting. Wouldn't it be a little more efficient to run at connection time rather than every time a cursor is created?

[Chris Petersen (geek+launchpad)]

I've been running like this for a few days and it seems that I was hasty in making the recommendation. Disabling autocommit automatically makes mysql start a transaction (meaning that calling BEGIN is now optional), which can make for some very long transactions if you have a persistent connection that only issues select queries. Additionally, those transactions will be stuck in an isolation level that prevents them from seeing any changes to the database that were issued after the transaction was created.

You can of course set the isolation level to "read committed", but that behavior may surprise people who are used to the default isolation level. More info is here if you want some docs on isolation levels:

http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html