如何防止psycopg2锁定表格

Question

我在一个 PostgreSQL 9.1 服务器上有一张这样的表：

CREATE TABLE foo(id integer PRIMARY KEY);

在使用 psycopg2 (≥ 2.4.2) 的交互式 Python shell 中，我可以启动一个连接和光标，并查询这个表：

import psycopg2
conn = psycopg2.connect('dbname=...')
curs = conn.cursor()
curs.execute('SELECT * FROM foo;')
curs.fetchall()

然而，如果我尝试修改该表：

ALTER TABLE foo ADD COLUMN bar integer;

这会导致虚拟死锁，直到我在 Python 中执行 conn.close()。

我该如何使用 psycopg2 建立一个简单的连接，以防止由其他地方的 DDL 更改引起的死锁？Python 中的连接可以是只读的。

- Mike T

Answer 1

我找到的解决方案是使用set_session，像这样：

conn.set_session(readonly=True, autocommit=True)

autocommit 的文档警告:

默认情况下，任何查询（包括简单的 SELECT）都会启动一个事务：对于长时间运行的程序，如果没有采取进一步的操作，会话将保持“空闲事务”状态，这是不良的状态，有几个原因（锁由会话持有，表膨胀等）。对于长期运行的脚本，要么尽快终止事务，要么使用自动提交连接。

这概括了在问题中使用简单 SELECT 的经验。

- Mike T

---

你也可以直接执行 conn.commit()，而不是将 autocommit 设置为 True。 - Clodoaldo Neto

Answer 2

作为FYI，我在使用psycopg2进行并发写入时遇到了同样的问题。文档说明如下：

事务由连接类处理。默认情况下，第一次使用连接创建的游标之一将向数据库发送命令时，将创建新的事务。以下数据库命令将在相同事务的上下文中执行 - 不仅是由第一个游标发出的命令，而且是由同一连接创建的所有游标发出的命令。如果任何命令失败，事务将被中止，并且直到调用rollback（）方法后不会执行任何其他命令。

基本上，psycopg2为使用相同连接的所有事务锁定表格。

- Cory Brickner

---

除了上面的答案之外，您还可以通过在SQL语句前加上“BEGIN;”并以“END;”结尾来防止表锁。这实际上取决于您尝试完成的范围。 - Cory Brickner