Postgres pg_try_advisory_lock阻止所有记录 [英] Postgres pg_try_advisory_lock blocks all records

问题描述

我在Postgres中使用pg_try_advisory_lock().

I'm using pg_try_advisory_lock() in Postgres.

接下来的两个查询锁定table1中的多个记录:

Next two queries lock more than one records in 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()来仅锁定一条记录.

But I need pg_try_advisory_lock() to lock only one record.

怎么了?

UPD

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

But the strange thing is that when I run the following query

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扫描第一行然后停止?我不明白:它应该扫描所有行，然后将结果限制为一行?

Postgres locks only one row. So, Postgres scans the very first row then stops? I don't get it: it should scan all rows then limit the results to one row, or not?

推荐答案

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

You're calling pg_try_advisory_lock() once per row in the entire set that gets scanned (as part of the filtering that occurs in the where clause), whereas you only want it called once per row in table1 returned by the query.

您可以尝试使用子查询或CTE:

You could try using a subquery or a CTE instead:

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应该试图以您的初始查询的方式重写它.

But don't rely on that to necessarily work as expected either: Postgres should be tempted to rewrite it the way your initial query was.

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

Another possibility is this, since the select part of a statement is evaluated very late in the query:

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吗?

The real issue in practice is that pg_try_advisory_lock() is something you'd normally find in app land or in a function, rather than in a query like you're doing. Speaking of which, depending on what you're doing, are you sure you shouldn't be using select … for update?

关于您的更新:

postgres扫描第一行然后停止?

postgres scans the very first row then stops?

是的.由于limit 1，它将找到一个匹配项并立即停止.但是，可能发生的情况是，它不会以相同的顺序来评估where子句，具体取决于您的查询. SQL无法保证首先评估a <> 0 and b / a > c中的a <> 0部分.应用于您的案例，它不能保证在 之后将a中的行与b连接起来获得咨询锁.

Yes. Due to the limit 1, it's going to find a match and immediately stop. What is probably happening, though, is that it's not evaluating the where clause in the same order depending on your queries. SQL offers no guarantee that the a <> 0 part in a <> 0 and b / a > c gets evaluated first. Applied to your case, it offers no guarantee that the advisory lock is obtained after the row from a is joined with b.

这篇关于Postgres pg_try_advisory_lock阻止所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！