使用SQLAlchemy和SQLite实现嵌套事务

Question

我正在使用SQLAlchemy（和Elixir）编写Python应用程序，其中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)。

如何使嵌套事务工作？

- Jon

---

如果您能创建一个演示代码片段，我可以更轻松地回答这个问题。您可以使用sqlite :memory:数据库轻松地创建一个内存数据库来实现。 - Ken Kinder

相关问题：https://dev59.com/JkvSa4cB1Zd3GeqPczwH - Shane Holloway

Answer 1

我在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

在“Database Locking Behavior / Concurrency”一节中，我们提到pysqlite驱动程序的各种问题，这些问题会阻止SQLite的几个功能正常工作。 pysqlite DBAPI驱动程序有多个长期存在的错误，这些错误会影响其事务行为的正确性。在其默认操作模式下，SQLite的功能（例如SERIALIZABLE隔离级别、事务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")

添加上述监听器完全解决了我的问题！我已经发布了一个完整的工作示例作为Gist：https://gist.github.com/snorfalorpagus/c48770e7d1fcb9438830304c4cca24b9。我还发现记录SQL语句很有帮助（在上面的示例中使用）：调试(SQLAlchemy发送到数据库的显示) SQL命令。

- Snorfalorpagus

Answer 2

尽管sqlite似乎通过SAVEPOINT支持嵌套事务，但这仅适用于 版本3.6.8，发布于2009年1月12日。至少到Python v2.6为止，它使用的是早期版本：

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，最新版本似乎支持v3.6.12。但我不能确定这是否能解决你的问题，不过我认为答案解释了为什么它现在对你不起作用。

- Peter Hansen

---

似乎Python 2.7也附带了一个旧的SQLite3库。安装PySqlite解决了这个问题。 - Kiran Jonnalagadda

@KiranJonnalagadda 尽管Python 2.7可能没有最新版本，但它的版本难道不比3.6.8更高吗？在Ubuntu 12.04上使用Python 2.7.3，我得到了sqlite v3.7.9，在Windows上使用Python 2.7.5，我得到了sqlite v3.6.21。您是在尝试Python 2.7.2或更早版本吗？ - Peter Hansen

在 Mac OS X 上通过 Homebrew 新安装的 Python 2.7（不是系统安装的 Python）出现了这个问题。PySqlite 解决了它。 - Kiran Jonnalagadda

@KiranJonnalagadda 有没有可能你可以检查一下那是哪个版本的Python 2.7，如果不是第一个的话？如果在命令行上运行python，它会在提示符处显示。他们已经通过了2.7.0（这实际上是第一个2.7）到2.7.10。 - Peter Hansen

@PeterHansen 我使用的是2.7.8版本。 - Kiran Jonnalagadda

Answer 3

SQLAlchemy使用pysqlite与SQLite数据库进行交互，如果我没记错的话，pysqlite默认会将您发送的任何查询封装在事务中。

答案可能在正确设置连接时的隔离级别上。

这里有一些相关讨论

- Dirk Stoop

---

我想要嵌套事务，可以回滚外部事务中已更改的内容而不会丢失任何东西。我不确定pysqlite能否自动完成这个操作。您是在暗示使用SAVEPOINTS不可行，还是建议另一种（可能的）解决方法来解决问题？ - Jon