[issue10740] sqlite3 module breaks transactions and potentially corrupts data

[aaugustin]

aaugustin added the comment:

Hey -- maintainer of Django's transaction support here.

This ticket was brought to my attention again today. As I know a few things about this issue and I see Python core devs asking for input, I'll give my $0.02.

The core of this issue is that, **by default**, the sqlite3 module performs a simplistic parsing of SQL queries and decides to commit before anything it doesn't understand, for example "SAVEPOINT foo".

I don't think it's a good idea to change sqlite's default operation mode to autocommit. PEP 249's prescription for transaction management aren't practical (and Django enforces the opposite behavior by default) but that's beyond the point. It's way too much backwards incompatible.

However, I don't think it's a good idea either to automatically send a COMMIT when I want to make a SAVEPOINT. In fact, if you want to use transactions with sqlite, connection.isolation_level = None is almost warranted -- and then you do everything manually.

For a slightly longer explanation, see https://www.youtube.com/watch?v=09tM18_st4I#t=1751

I've struggled mightily with all this when rewriting Django's transaction management. See:

- https://github.com/django/django/blob/3becac84/django/db/backends/sqlite3/base.py#L107
https://github.com/django/django/blob/3becac84/django/db/backends/sqlite3/base.py#L398-L403
- https://github.com/django/django/blob/3becac84/django/db/transaction.py#L185-L195

I have implemented the workarounds I need and I we won't be able to remove them from Django anytime soon. As a consequence, I have little interest in getting this fixed.

Still, it would be sane to stop committing before savepoints by default -- that kinda ruins the concept of savepoints :-) It would be even saner to stop parsing SQL. Does http://hg.python.org/cpython/file/85fd955c6fc8/Modules/_sqlite/cursor.c#l32 look like an adequate parser for http://sqlite.org/lang.html?

Unfortunately, I don't have backwards-compatible proposal to fix this. Trying to account for a bit more syntax will help in the short term but not fix the underlying issue.

PS: I find it unfair to brand SQLite as a neutered database engine. Its transaction model is vastly superior to, say, MySQL. Too bad sqlite3 ruins it.

----------
nosy: +aaugustin

_______________________________________
Python tracker <report at bugs.python.org>
<http://bugs.python.org/issue10740>
_______________________________________