Why don't DDL statements open a transaction?

[aaugustin]

796b3af "introduces" a special case for DDL statements: unlike other statements, they won't open a transaction implicitly when autocommit is off.

("introduces" because it actually adjusts changes from the previous commit...)

The most convenient way to start a database transaction with Python DB-API modules is to turn autocommit off. This commit makes it necessary to special case SQLite by using a BEGIN statement instead of turning autocommit off.

Django currently does this for other reasons (to work around #24, which has been fixed and should make it into Python 3.6). See https://github.com/django/django/blob/271581df606b307d89c141e8b1a50ace763bea81/django/db/backends/base/base.py#L375-L404

I'd love to remove this hack in the long term, but if pysqlite / sqlite3 don't open a transaction consistently when autocommit is off, we'll have to keep it.

In my opinion the least surprising behavior is:

• when autocommit is off (default required by the DB-API) : begin a transaction implicitly if no transaction is in progress, never commit implicitly
• when autocommit is on (default in Django) : never begin a transaction implicitly, never commit implicitly (the latter was pysqlite treats SAVEPOINT and RELEASE as DDL (nested transactions don't work) #24)

I understand that any documented behavior can be worked around, but it would be so nice if we could keep things simple...

[ghost]

There is a bug about 796b3af, please see http://bugs.python.org/issue28518
Although it's sqlite3 module, pysqlite should has the same problem.

[ghaering]

It's hard to change anything at this point because of backwards compatibility reasons.

[aaugustin]

That was the answer I expected.