SQLAlchemy 长时间运行的脚本:用户持有关系锁的时间太长 [英] SQLAlchemy long running script: User was holding a relation lock for too long

问题描述

我在脚本中有一个 SQLAlchemy 会话.脚本运行时间长，只从数据库中取数据，不更新也不插入.

I have an SQLAlchemy session in a script. The script is running for a long time, and it only fetches data from database, never updates or inserts.

我收到了很多类似的错误

I get quite a lot of errors like

sqlalchemy.exc.DBAPIError: (TransactionRollbackError) terminating connection due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.

按照我的理解，SQLAlchemy 创建了一个事务，发出第一个选择，然后重用它.由于我的脚本可能会运行大约一个小时，因此很可能在该事务的生命周期内发生冲突.

The way I understand it, SQLAlchemy creates a transaction with the first select issued, and then reuses it. As my script may run for about an hour, it is very likely that a conflict comes up during the lifetime of that transaction.

为了消除错误，我可以在不推荐使用的模式下使用自动提交(不做任何其他操作)，但文档明确不鼓励这样做.

To get rid of the error, I could use autocommit in te deprecated mode (without doing anything more), but this is explicitly discouraged by the documentation.

处理错误的正确方法是什么?我可以在没有事务的情况下使用 ORM 查询吗?

What is the right way to deal with the error? Can I use ORM queries without transactions at all?

推荐答案

我在(几乎)每次选择后都关闭了会话，就像

I ended up closing the session after (almost) every select, like

session.query(Foo).all()
session.close()

由于我不使用自动提交，所以会自动打开一个新事务.

since I do not use autocommit, a new transaction is automatically opened.

这篇关于SQLAlchemy 长时间运行的脚本:用户持有关系锁的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！