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
