postgresql - 使用 CTE 和 FOR UPDATE 锁定多行

Question

在一个 session 中，我试图锁定“用户”表中的多行，并获取用户 WHERE id = 2 的“状态”列。

do $$
declare user_status int;
begin
    WITH t(id, status) AS(
        SELECT id, status FROM users WHERE id in( 2,4,7,6) order by id FOR UPDATE
    )
    SELECT status FROM t WHERE id = 2 INTO user_status;

    -- just run transaction for a while
    FOR i in 1..2000000000 loop
    end loop;

end;
$$ language plpgsql

当这个事务运行时，我在另一个 session 中运行这个查询:

UPDATE users SET some_col = some_col WHERE id = 6;

我预计 WHERE id = 6 行应该被第一个事务锁定，但这不是真的，因为第二个 session 中的 UPDATE 立即运行(不等待结束事务在第一次 session 中)。

我被误解了什么？

附言

如果在第一笔交易中，而不是 CTE 使用:

perform id, status FROM users WHERE id in( 2,4,7,6) order by id FOR UPDATE;

然后锁按预期工作。

Answer 1

我认为这是由于 PL/pgSQL 的 SELECT INTO 在后台工作的方式。

docs 中提到了明显的根本原因:

if a locking clause is used in a cursor's query, only rows actually fetched or stepped past by the cursor will be locked.

...这种效果会传递到查询中的任何 CTE，只有在外部查询需要时才会获取行(并因此锁定行)。

这很容易证明。首先，一些设置:

CREATE TABLE t (x INT);
INSERT INTO t VALUES (1),(2),(3);

如预期的那样，此查询将锁定所有行:

WITH cte AS (SELECT x FROM t WHERE x IN (1,2,3) FOR UPDATE)
SELECT x FROM cte WHERE x = 1

但是，这将产生您观察到的行为，仅锁定第一条记录:

DECLARE c CURSOR FOR
  WITH cte AS (SELECT x FROM t WHERE x IN (1,2,3) FOR UPDATE)
  SELECT x FROM cte WHERE x = 1;
FETCH NEXT FROM c;

我的猜测是 PL/pgSQL 中的 SELECT INTO 以几乎相同的方式运行；如 docs 中所述，如果查询返回多行，则第一行分配给目标变量，其余行将被忽略(因此永远不需要获取多条记录)。

但是，此语句将锁定t 中的所有行:

DO $$
DECLARE i INT;
BEGIN
  WITH cte AS (SELECT x FROM t WHERE x IN (1,2,3) FOR UPDATE)
  SELECT x FROM cte WHERE x = 1 INTO STRICT i;
END
$$ LANGUAGE plpgsql;

注意 SELECT INTO STRICT 的使用.如果查询返回多行，STRICT 关键字会引发错误。但是为了发现返回了第二行，PL/pgSQL 必须尝试获取它，这会导致 CTE 查询获取剩余的记录(将它们锁定在进程中)。

所有这些在 PL/pgSQL 函数中大部分时间都不是问题，因为您通常会从 FOR UPDATE 查询中获取 所有 记录，并将它们传递给实际更新它们的人。另一方面，如果您想要查询的副作用但对其输出不感兴趣，那么(正如您观察到的那样)您可以使用 PERFORM，它将运行查询直到完成，但丢弃结果。

在极少数情况下，您需要(即锁定)所有行，同时只返回其中的一个子集，您可能需要编写自己的 loop .