[DB-SIG] autocommit support in pep-249

[Michael Bayer]

On Sep 13, 2011, at 6:01 PM, Daniele Varrazzo wrote:

> 
> I think with Postgres the situation is slightly easier, as there is no
> "non transaction" statement: any statement including DML is
> transactional except a couple (such as CREATE DATABASE) that would
> raise an error if run inside a transaction. Because the situation is
> this simple, psycopg can just run a BEGIN before the first statement
> executed, whatever it is, and there is no need to parse it; it then
> knows the state of the connection and an error such as switching to
> autocommit while in transaction is indeed handled by the adapter
> without calling the backend.
> 
> What does the Oracle adapter currently do? From my interpretation of
> the dbapi there is no explicit conn.begin(), a transaction is
> implicitly started at the first statement. How does the driver
> implement this behaviour? Is it something it can delegate to the
> backend?
> 
> Ditto for ODBC, where parsing the statements would be not impratical
> but just impossible: how does the adapter currently implement the "not
> autocommit" default dbapi behaviour? Is the begin handled by the
> adapter or by the backend?

The pysqlite adapter does actually parse incoming statements, looking for DML in an attempt to delay the BEGIN until necessary, as a means of avoiding aggressive file-locking between otherwise read-only connections to a SQLite database.   But I agree in general most DBAPIs don't do anything like this.

As for Oracle, like the DBAPI itself it has no "BEGIN" statement for transactions, and doesn't natively support "autocommit" in any way - there's always a transaction in progress that starts implicitly after a previous COMMIT or ROLLBACK.  Therefore it works very nicely with DBAPI's scheme and I doubt cx_oracle needs to do anything here.

This is one reason why i really don't want DBAPI to have a begin() - not all backends have such a concept.