Should 'SAVEPOINT' transaction (i.e.'begin_nested') support be considered usable with SQLite?

[Pedro Romano]

I have tried using 'SAVEPOINT' transactions (via 'session.begin_nested') with a 'SQLite' database connection, but got an exception when a 'session.add()', triggered an integrity error, nstead of the expected 'ROLLBACK TO SAVEPOINT':

The logged statements are:

BEGIN (implicit)

SAVEPOINT sa_savepoint_1

()

INSERT INTO table (col_id, col_a, col_b, col_c) VALUE (?, ?, ?, ?)

(1, None, None, None)

ROLLBACK TO SAVEPOINT sa_savepoint_1

()

ROLLBACK TO SAVEPOINT sa_savepoint_1

()

which triggers:

OperationalError: (OperationalError) no such savepoint: sa_savepoint_1 u'ROLLBACK TO SAVEPOINT sa_savepoint_1' ()

(I can attach the whole traceback if necessary).

It seems that the error is triggered because the 'ROLLBACK TO SAVEPOINT' is executed twice.

I am aware that SAVEPOINT support is relatively recent in SQLite and that transaction isolation levels in SQLite are also a rather intricate affair.

Thanks in advance for any feedback about this issue.

--Pedro.

[Michael Bayer]

the short answer is that this is a Pysqlite bug.   A short test makes this clear - and here is the bug report for them with a patch: https://code.google.com/p/pysqlite/issues/detail?id=24 .  Not at all unusually for pysqlite, the bug report has no responses from the developers for over a year, here's a related issue on the Python tracker: http://bugs.python.org/issue10740 which also has been dead for over a year.

If I set isolation_level to None and do transaction control entirely manually, then it works - because it is not intercepting SAVEPOINT instructions and blowing away the transaction.

SQLAlchemy can work around pysqlite's bugs to some degree here but it still relies upon the connection having "autobegin" behavior after any commit.   Since Pysqlite's connection actually complains if you call rollback() or commit() in isolation_level=None mode, working around this would require some ugly hacks, probably creating a wrapper for Pysqlite's connection that behaves correctly.

Pysqlite's motivation for their complex "begin" behavior is to avoid locking the database file.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/BiOjC1P1xP0J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Pedro Romano]

Thank you very much for your reply Michael. It confirms my suspicions.

--Pedro.