SQLAlchemy 和 sqlite 的嵌套事务

我正在使用 SQLAlchemy(和 Elixir)和 SQLite 作为数据库后端在 Python 中编写应用程序.我使用代码 session.begin_transaction() 开始一个新事务，但是当我调用 session.rollback() 时，我收到以下错误:

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

我在调用 session.commit() 时也遇到类似的错误.据我所知，sqlite 支持 SAVEPOINTS (http://www.sqlite.org/lang_savepoint.html).

如何让嵌套事务工作?

解决方案

我在 Windows 上使用 Python 3 使用嵌套事务时遇到了这个问题.我使用的是 SQLite 3.8.11 版，因此应该支持 SAVEPOINT.显然安装 pysqlite 不适合我，因为它不支持 Python 3.

在我的头撞在桌子上几个小时后，我在文档中看到了这一部分:

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

<块引用>

在数据库锁定行为/并发部分中，我们指的是pysqlite 驱动程序的各种问题阻止了几个SQLite 的功能无法正常工作.pysqlite DBAPI 驱动程序有几个长期存在的错误会影响其正确性交易行为.在默认操作模式下，SQLiteSERIALIZABLE 隔离、事务性 DDL 和SAVEPOINT 支持是非功能性的，为了使用这些功能，必须采取变通办法.

问题本质上是司机试图重新猜测用户的意图，未能开始交易，有时会结束交易过早地，为了尽量减少 SQLite 数据库的文件锁定行为，即使 SQLite 本身使用共享"锁只读活动.

SQLAlchemy 默认选择不改变这个行为，因为它是pysqlite 驱动程序的长期预期行为；如果当pysqlite 驱动程序尝试修复这些问题，这将更多SQLAlchemy 的默认驱动程序.

好消息是，通过一些事件，我们可以实现通过完全禁用 pysqlite 的功能来完全支持事务并发出 BEGIN 我们自己.这是使用两个事件实现的听众:

from sqlalchemy import create_engine, eventengine = create_engine("sqlite:///myfile.db")@event.listens_for(引擎，连接")def do_connect(dbapi_connection, connection_record):# 完全禁用 pysqlite 发出 BEGIN 语句.# 还会阻止它在任何 DDL 之前发出 COMMIT.dbapi_connection.isolation_level = 无@event.listens_for(引擎，开始")def do_begin(conn):# 发出我们自己的 BEGINconn.execute("BEGIN")

添加上面的监听器完全解决了我的问题！

我已经发布了一个完整的工作示例作为要点:

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

我还发现记录 SQL 语句很有帮助(在上面的示例中使用):

调试(显示)SQL 命令发送到数据库由 SQLAlchemy

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?

解决方案

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