PostgreSQL中SELECT FOR UPDATE的锁定顺序

Question

假设表a和b都只有一行数据，在查询时：

 SELECT * FROM a, b FOR UPDATE

应该获得两个行级锁（一个在a上，一个在b上）。这些锁的获取是否有定义的顺序？是否有任何方法要求从表b获取的锁在从a获取的锁之前获取（以避免与其他事务发生死锁）？

- Heptic

---

死锁通常应该导致一个事务被任意地中止。通常情况下，您不需要担心这个问题。 - Kevin

我有其他正在运行的事务按照定义的顺序获取锁，因此我需要确保它们被获取的顺序。 - Heptic

“我需要确保它们被获取的顺序” - 为什么？数据库将为您解决死锁问题。 “我有其他正在运行的事务按照定义的顺序获取锁” - 这与任何事情有关吗？ - Kevin

数据库在等待超时后会打破死锁，这会降低吞吐量（死锁的唯一原因是同时进行大量更新）。而定义顺序与所有事情都有关系，它是标准的死锁预防措施。 - Heptic

您确定需要锁定连接的两侧吗？如果不需要，只需使用"FOR UPDATE OF a"来锁定表a中的行。这并不能解答您的问题，但通常情况下，FOR UPDATE会锁定不必要的内容。 - Markus Winand

是的，在这种情况下，我需要锁定连接的两侧。（之后我会修改它们两个） - Heptic

Answer 1

在获取锁的过程中，有没有定义好的顺序？

就我所知，在使用 SELECT * 时并没有定义好的顺序。由于此情况下没有文档记录锁的顺序，即使在实际应用中存在该顺序，也不能依赖它。它可能会在未来版本中发生改变。

是否有办法要求获取表 b 的锁在获取表 a 的锁之前（以避免与其他事务产生死锁）？

如果必须使用 SELECT * ，则无法做到。但如果可以控制 SELECT 列表，则可以做到。看起来行锁是按照相关元组字段在 SELECT 列表中出现的顺序进行获取的，因此：

SELECT a.x, b.x FROM b, a FOR UPDATE;

将锁定从 a 行开始，然后再锁定从 b 行开始。目前为止，我不认为标准规定了这一点，并且在文档中也没有任何提及，因此 以后可能会发生改变。

就我个人而言，我会使用 DO 块或单独的查询。可能可以使用一些子查询或 CTE 来完成这项工作，但您必须创建某种人工依赖关系来确保排序。这很脆弱且不值得。

---

让我们看看实际发生了什么:

regress=> EXPLAIN (VERBOSE) SELECT * FROM a, b FOR UPDATE;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 LockRows  (cost=0.00..129674.00 rows=5760000 width=20)
   Output: a.x, b.x, a.ctid, b.ctid
   ->  Nested Loop  (cost=0.00..72074.00 rows=5760000 width=20)
         Output: a.x, b.x, a.ctid, b.ctid
         ->  Seq Scan on public.a  (cost=0.00..34.00 rows=2400 width=10)
               Output: a.x, a.ctid
         ->  Materialize  (cost=0.00..46.00 rows=2400 width=10)
               Output: b.x, b.ctid
               ->  Seq Scan on public.b  (cost=0.00..34.00 rows=2400 width=10)
                     Output: b.x, b.ctid
(10 rows)

查询被执行后，结果会被输入到一个LockRows节点中。那么LockRows是做什么的呢？这时候就需要去看源代码了。src/backend/executor/nodeLockRows.c中的ExecLockRows是相关的代码。其中有很多内容，但其要点是按顺序遍历RowMark列表，并依次获取每个锁。该列表由ExecInitLockRows设置，它复制并过滤了在计划期间准备好并存储在LockRows节点中的列表。

我没有时间追溯计划器以找到LockRows创建的顺序，但如果我没记错的话，基本上只是解析顺序（对于SELECT *）或字段出现在SELECT列表中的顺序（如果你没有使用*）。我建议不要依赖它。

- Craig Ringer