Mike, David,

The bug is that sqlite module issues implicit COMMIT. SQLite never needs this, many programs need to NOT have it and they can't opt out as isolation_level affects implicit begins only.

Most programs will do some changes to data after changing the schema, so they will need to commit() at the end anyway and dropping the implicit commit before DDL statements won't break them. But there may be some rare cases that do just a create or delete table/view/index here or there that could be affected, so I am not against explicit request to stop generating implicit commits. Just put a big warning in the documentation that any new code should always do this.

I don't understand why the implicit commit was initially added. It is serious problem for schema migrations and I don't see anything it would help. The only thing I can think of is to behave like MySQL which does not support DDL in transactions. But anybody who ever tried to do a non-trivial schema update in MySQL has probably cursed it to hell many times.

On the other hand there is absolutely nothing broken on the implicit BEGIN (which is required by dbapi2 specification) nor on the isolation_level property which controls it. Those shouldn't be touched; there is no reason to.

However note, that skipping the implicit BEGIN before SELECT only helps concurrency a little. If BEGIN (DEFERRED) TRANSACTION is followed by SELECT, the database is locked in shared mode and the same happens in the implicit transaction when the SELECT starts without explicit begin. Other readers are still allowed. The only difference is that with explicit BEGIN the database remains locked, while without it it is unlocked when the SELECT finishes (read to end or cursor closed).

Oh, I briefly looked at the patch and I suspect it's doing too much. It would IMHO be best to really just make the implicit COMMIT conditional on backward compatibility flag.