[DB-SIG] autocommit support in pep-249

[Michael Bayer]

On Sep 13, 2011, at 2:39 PM, Joshua D. Drake wrote:

> 
> Oh now that is interesting. Hmmm... perhaps conn.autocommit() is actually a synonym for conn.begin() except that it sets the autocommit property? So we would have two semantics:
> 
> conn.begin() -- Starts a transaction, will not commit unless explicit conn.commit() is called.
> 
> conn.autocommit() -- Works in autocommit mode, calling conn.commit() is redundant, each statement is executed as it is delivered to the database.

I'm -1 on conn.begin().   I've spent years explaining to users the beauty of the DBAPI in that it has no "begin" method.   Providing for two different modes of doing the same thing, i.e. working with a standard DBAPI connection in a transaction, or forcing autocommit to True then using begin(), creates for a more confusing and controversial interface.   

Right now, pysqlite users are shielded from SQLite's aggressive file locking by the fact that pysqlite doesn't actually emit BEGIN until DML is encountered.   There is an enhancement request on pysqlite's bugtrcker to allow this to be more configurable.   In the meantime, I sometimes get email requests stating that SQLAlchemy should be emitting "BEGIN" itself to work around this issue.    While I give these users a workaround for what's essentially a missing feature in pysqlite, SQLA never emits the BEGIN.   It makes life much easier for SQLAlchemy that DBAPIs are responsible for handling transactional markers like this and I'd hate to see the interface diluted - there is sometimes functionality within "implicit begin" that may not be easy to replicate with an "explicit begin", and having both would make life more complicated for DBAPI authors as well as users.