pysqlite treats SAVEPOINT and RELEASE as DDL (nested transactions don't work)

[ghaering]

From skrulx on October 29, 2010 01:11:27

Nested transactions do not work with pysqlite because the SAVEPOINT and RELEASE statements are not recognized as non-DLL which cause implicit commits to be issued before execution.

Attached is a simple patch that adds "savepoint" and "release" to the list of statements that don't cause an implicit commit.

Attachment: savepoint.patch

Original issue: http://code.google.com/p/pysqlite/issues/detail?id=24

[ghaering]

From t.landsc...@gmail.com on November 02, 2010 14:21:55

I ran into this using SQLite via SQLAlchemy. It also affects DDL statements that would otherwise work fine inside a transaction.

IOW: SQLite now supports create table in a transaction, but pysqlite masks that feature.

[ghaering]

From t.landsc...@gmail.com on May 20, 2011 03:25:18

As this just hit me again, I converted the patch that I created for Python at http://bugs.python.org/issue10740 It turned out that we don't really want to rebuild python for a simple change in the sqlite bindings and SQLAlchemy automatically prefers pysqlite over sqlite3 if the former is available.

So here is a patch to allow DDL and SAVEPOINT in transactions.

Attachment: transaction_config.bundle transaction_config.diff

[nicolas-van]

I also encountered a bug concerning this problem. It makes nested transactions completely unusable in sqlalchemy. It would be nice to have this fix merged.

[ghaering]

Unfortunately, the patch got lost during the migration to Github :-(

[nicolas-van]

Ouch, but you saved it somewhere isn't it? :)

[bkircher]

Does anyone still have access to the patch by Torsten Landschoff?

This issue renders SQLAlchemy's begin_nested() useless when using Python's buildin sqlite3 or pysqlite module.

You can set
connection.isolation_level = None

or in SQLAlchemy terms
create_engine('sqlite:///...', connect_args={'isolation_level': None})

to work around this. However, as pointed out by Mike Bayer here [1] this does not work in real world w/o emitting BEGIN manually all over the place.

isolation_level affects implicit begins only, but our problem AFAIK are implicit commits as soon as anything is parsed by pysqlite that does look as DDL or is something that detect_statement_type (src/cursor.c) does not recognize, e.g. SAVEPOINT.

Anyway, why is pysqlite parsing SQL in the first place?

FYI, there is still active work on this going on over at [2].

[1] https://groups.google.com/d/msg/sqlalchemy-devel/0lanNjxSpb0/6zriniGAfu0J
[2] http://bugs.python.org/issue10740

[aaugustin]

@ghaering Should I keep working on http://bugs.python.org/issue10740 or rather here? What's the relationship between pysqlite and the stdlib's sqlite3 these days? Thank you!

[lrowe]

I had a copy of @skrul's patch in my google code clone. Applied to a github fork at: lrowe/pysqlite@f53a9f5

If you apply it please fix up the authorship information.

[bkircher]

Great! Thanks a lot.