使用SQLAlchemy和sqlite的嵌套事务

Question

我正在用Python写一个应用程序，使用SQLAlchemy（和Elixir），数据库用的是SQLite。我用代码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）。

我该如何让嵌套事务正常工作呢？

Answer 1

SQLAlchemy使用pysqlite来和SQLite数据库进行交互。如果我没记错的话，pysqlite默认会把你发送的任何查询都放在一个事务里。

解决这个问题的关键可能在于你连接时正确设置隔离级别。

这里有一些相关的讨论

Answer 2

虽然sqlite确实支持通过SAVEPOINT来实现嵌套事务，但这项功能是在2009年1月12日发布的3.6.8版本中才开始支持的。而Python在2.6版本之前使用的是更早的sqlite版本：

c:\svn\core\apps\general>python
Python 2.6.2 (r262:71605, Apr 14 2009, 22:40:02) [MSC v.1500 32 bit (Intel)] on win32
>>> import sqlite3 as s
>>> s.sqlite_version
'3.5.9'

我认为你可以自己安装PySqlite，而且最新的版本似乎支持3.6.12。不过我不能确定这能否解决你的问题，但我相信这个回答解释了为什么你现在遇到的问题。

Answer 3

我在使用嵌套事务时遇到了问题，使用的是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驱动程序有几个长期存在的错误，这些错误影响了它的事务行为的正确性。在默认的操作模式下，SQLite的一些功能，比如可序列化隔离、事务DDL和SAVEPOINT支持，都无法使用，因此需要采取一些变通办法。

问题的关键在于，驱动程序试图猜测用户的意图，结果导致事务没有正确开始，有时还会提前结束事务，目的是为了减少SQLite数据库的文件锁定行为，尽管SQLite本身在只读操作时使用的是“共享”锁。

SQLAlchemy选择默认不改变这种行为，因为这是pysqlite驱动程序的预期行为；如果pysqlite驱动程序在未来修复了这些问题，那将会影响SQLAlchemy的默认设置。

好消息是，通过一些事件，我们可以完全实现事务支持，方法是完全禁用pysqlite的功能，并自己发出BEGIN命令。这可以通过两个事件监听器来实现：

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")

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

我还发布了一个完整的工作示例，可以在这里查看：

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

我还发现记录SQL语句很有帮助（在上面的示例中使用了这个）：

调试（显示）SQL命令发送到数据库的SQLAlchemy