Postgres 是否自动锁定查询中的所有行，甚至通过 JOIN 跨不同表锁定？

发布于 2025-01-11 06:42:39 字数 734 浏览 0 评论 0原文

我的代码出现死锁错误。问题是这个死锁错误发生在事务的第一个查询上。此查询连接两个表：TableA 和 TableB，并且应使用 id==table_a_id 锁定 TableA 中的一行，以及 TableB 上具有 table_a_id 外键的所有行。

查询如下所示（我正在使用 SQLAlchemy，此输出来自打印等效查询，并且下面也会有其代码）：

SELECT TableB.id AS TableB_id
FROM TableA JOIN TableB ON TableA.id = TableB.table_a_id 
WHERE TableB.id = %(id_1)s FOR UPDATE

查询在 SQLAlchemy 语法中如下所示：

query = (
    database.query(TableB.id)
    .select_from(TableA)
    .filter_by(id=table_a_id)
    .join((TableB, TableA.id == TableB.table_a_id))
    .with_for_update()
)

return query.all()

我的问题是，此查询会原子锁定所有两个表中的那些行？如果是这样，为什么我会在这个查询上遇到死锁，因为它是事务的第一个查询？

原文

I am getting a deadlock error on my code. The issue is that this deadlock error is happening on the very first query of the transaction. This query joins two tables, TableA and TableB and should lock a single row in TableA with id==table_a_id, and all the rows on TableB that have a foreign key for table_a_id.

The query looks as follows (I am using SQLAlchemy, this output is from printing the equivalent query from it and will have its code below as well):

SELECT TableB.id AS TableB_id
FROM TableA JOIN TableB ON TableA.id = TableB.table_a_id 
WHERE TableB.id = %(id_1)s FOR UPDATE

The query looks as follows in SQLAlchemy syntax:

query = (
    database.query(TableB.id)
    .select_from(TableA)
    .filter_by(id=table_a_id)
    .join((TableB, TableA.id == TableB.table_a_id))
    .with_for_update()
)

return query.all()

My question is, will this query atomically lock all those rows from both tables? If so, why would I get a deadlock already exactly on this query, given it's the first query of the transaction?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

舟遥客 2025-01-18 06:42:39

查询将在选择行时逐一锁定行。确切的顺序将取决于执行计划。也许您可以添加 FOR UPDATE OF table_name 来仅锁定需要锁定的表中的行。

我还有两个想法：

重写查询，以便它按特定顺序锁定行：

将 b 作为具体化 (
   选择 id，表_a_id
   来自表b
   其中 ID = 42
   无密钥更新
）
选择 b.id
来自表a
哪里存在（从 b 中选择 1
              WHERE tablea.id = b.table_a_id)
ORDER BY tablea.id
无关键更新；

性能可能不会那么好，但如果每个人都这样选择，就不会陷入僵局。

锁定表：
```
以独占模式锁定表 tablea、tableb；
```
该锁将防止并发行锁和数据修改，因此您将避免死锁。
仅将其作为最后的努力，并且不要经常这样做。如果您经常采用这样的高表锁，则会阻止 autovacuum 运行并危及数据库的健康。

The query will lock the rows one after the other as they are selected. The exact order will depend on the execution plan. Perhaps you can add FOR UPDATE OF table_name to lock rows only in the table where you need them locked.

I have two more ideas:

rewrite the query so that it locks the rows in a certain order:

WITH b AS MATERIALIZED (
   SELECT id, table_a_id
   FROM tableb
   WHERE id = 42
   FOR NO KEY UPDATE
)
SELECT b.id
FROM tablea
WHERE EXISTS (SELECT 1 FROM b
              WHERE tablea.id = b.table_a_id)
ORDER BY tablea.id
FOR NO KEY UPDATE;

Performance may not be as good, but if everybody selects like that, you won't get a deadlock.

lock the tables:
```
LOCK TABLE tablea, tableb IN EXCLUSIVE MODE;
```
That lock will prevent concurrent row locks and data modifications, so you will be safe from a deadlock.
Only do that as a last-ditch effort, and don't do it too often. If you frequently take high table locks like that, you keep autovacuum from running and endanger the health of your database.

回复收藏 0 原文

~没有更多了~