Postgres pg_try_advisory_lock阻止所有记录

小编典典

Postgres pg_try_advisory_lock阻止所有记录

sql

我pg_try_advisory_lock()在Postgres中使用。

接下来的两个查询将多个记录锁定在中table1：

1）

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE
    table2.id = 1
    AND
    pg_try_advisory_lock('table1'::regclass::integer, a.id)
LIMIT 1;

但

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1

返回一条记录。

2）

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
JOIN table3 c ON b.table2_id = c.id
WHERE
    table3.id = 1
    AND
    pg_try_advisory_lock('table1'::regclass::integer, a.id)
LIMIT 1;

但是我pg_try_advisory_lock()只需要锁定一条记录。

怎么了？

UPD

但是奇怪的是，当我运行以下查询时

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE
    pg_try_advisory_lock('table1'::regclass::integer, a.id)
LIMIT 1;

Postgres仅锁定一行。那么，Postgres扫描第一行然后停止？我不明白：它应该扫描所有行，然后将结果限制为一行，否则不行吗？

阅读 234

2021-05-23

共1个答案

小编典典

您正在对要扫描的整个集合中的每一行调用pg_try_advisory_lock（）（作为在where子句中进行的过滤的一部分），而您只希望查询返回的table1中的每一行调用一次。

您可以尝试使用子查询或CTE代替：

with rows as (
SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1
)
select rows.*
from rows
where pg_try_advisory_lock('table1'::regclass::integer, rows.id);

但是也不要依赖于它一定能按预期工作：Postgres应该试图以您的初始查询的方式重写它。

这是另一种可能性，因为select语句的一部分在查询中很晚才被求值：

with rows as (
SELECT a.id,
       pg_try_advisory_lock('table1'::regclass::integer, a.id) as locked
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1
)
select rows.id
from rows
where rows.locked;

实际上，真正的问题pg_try_advisory_lock()是您通常会在应用程序领域或函数中找到这些内容，而不是像您正在做的那样在查询中找到这些内容。说到哪个，取决于您在做什么，您确定不应该使用select… for update吗？

关于您的更新：

postgres扫描第一行然后停止？

是的。由于存在limit1，它将找到一个匹配项并立即停止。但是，可能发生的情况是，where根据查询的不同，它没有以相同的顺序评估该子句。SQL不保证首先评估其中的a <>0部分a <> 0 and b / a > c。应用于您的案例，它不能保证在a 的行与b联接之后获得咨询锁。

2021-05-23