python - Nested transactions with SQLAlchemy and sqlite

Question

I'm writing an application in Python using SQLAlchemy (and Elixir) with SQLite as the database backend. I start a new transaction using the code session.begin_transaction(), but when I call session.rollback() I get the following error:

sqlalchemy.exceptions.OperationalError: (OperationalError) no such savepoint: sa_savepoint_1 u'ROLLBACK TO SAVEPOINT sa_savepoint_1' []

I also get a similar error calling session.commit(). From what I can tell, sqlite supports SAVEPOINTS (http://www.sqlite.org/lang_savepoint.html).

How do I get nested transactions to work?

Answer 1

I've run into this issue using nested transactions, using Python 3 on Windows. I'm using SQLite version 3.8.11, so SAVEPOINT should be supported. Apparently installing pysqlite isn't an option for me as it doesn't support Python 3.

After hours of banging my head against the desk I came across this section in the documentation:

http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl

In the section Database Locking Behavior / Concurrency, we refer to the pysqlite driver’s assortment of issues that prevent several features of SQLite from working correctly. The pysqlite DBAPI driver has several long-standing bugs which impact the correctness of its transactional behavior. In its default mode of operation, SQLite features such as SERIALIZABLE isolation, transactional DDL, and SAVEPOINT support are non-functional, and in order to use these features, workarounds must be taken.

The issue is essentially that the driver attempts to second-guess the user’s intent, failing to start transactions and sometimes ending them prematurely, in an effort to minimize the SQLite databases’s file locking behavior, even though SQLite itself uses “shared” locks for read-only activities.

SQLAlchemy chooses to not alter this behavior by default, as it is the long-expected behavior of the pysqlite driver; if and when the pysqlite driver attempts to repair these issues, that will be more of a driver towards defaults for SQLAlchemy.

The good news is that with a few events, we can implement transactional support fully, by disabling pysqlite’s feature entirely and emitting BEGIN ourselves. This is achieved using two event listeners:

from sqlalchemy import create_engine, event

engine = create_engine("sqlite:///myfile.db")

@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable pysqlite's emitting of the BEGIN statement entirely.
    # also stops it from emitting COMMIT before any DDL.
    dbapi_connection.isolation_level = None

@event.listens_for(engine, "begin")
def do_begin(conn):
    # emit our own BEGIN
    conn.execute("BEGIN")

Adding the listeners above completely resolved the issue for me!

I've published a full working example as a gist:

https://gist.github.com/snorfalorpagus/c48770e7d1fcb9438830304c4cca24b9

I also found logging the SQL statements helpful (this is used in the above example):

Debugging (displaying) SQL command sent to the db by SQLAlchemy