postgresql - PostgreSQL 锁定机制中的错误或对该机制的误解

Question

我们遇到了 PostgreSQL 9.0.12 锁定机制的问题。

这是我们重现问题的最少代码:

场景

Transaction 1      Transaction 2
BEGIN              BEGIN
......             select trees for update;                
update apples;      
--passes
update apples;    
-- stuck!      

重现代码:如果你想在你的 PostgreSQL 中尝试它 - 这是你可以复制/粘贴的代码。

我有以下数据库架构:

CREATE TABLE trees (
    id       integer primary key
);

create table apples (
    id       integer primary key,
    tree_id  integer references trees(id)
);

insert into trees values(1);
insert into apples values(1,1);

打开两个 psql shell:

在外壳 1 上:

BEGIN;
    SELECT id FROM trees WHERE id = 1 FOR UPDATE;    

在外壳 2 上:

BEGIN;
UPDATE apples SET id = id WHERE id = 1;
UPDATE apples SET id = id WHERE id = 1;

apples 的第二次更新会卡住，似乎 shell 2 的进程正在等待 shell 1 的事务完成。

relname  |transactionid|procpid|mode              |substr                                    |       age      |procpid
-----------+-------------+-------+------------------+------------------------------------------+----------------+-------
           |             | 4911  | ExclusiveLock    | <IDLE> in transaction                    | 00:05:42.718051|4911
           |   190839904 | 4911  | ExclusiveLock    | <IDLE> in transaction                    | 00:05:42.718051|4911
trees      |             | 4911  | RowShareLock     | <IDLE> in transaction                    | 00:05:42.718051|4911
           |             | 5111  | ExclusiveLock    | UPDATE apples SET id = id WHERE id = 1;  | 00:05:21.67203 |5111
           |   190839905 | 5111  | ExclusiveLock    | UPDATE apples SET id = id WHERE id = 1;  | 00:05:21.67203 |5111
apples_pkey|             | 5111  | RowExclusiveLock | UPDATE apples SET id = id WHERE id = 1;  | 00:05:21.67203 |5111
apples     |             | 5111  | RowExclusiveLock | UPDATE apples SET id = id WHERE id = 1;  | 00:05:21.67203 |5111
trees      |             | 5111  | RowShareLock     | UPDATE apples SET id = id WHERE id = 1;  | 00:05:21.67203 |5111
trees      |             | 5111  | ShareLock        | UPDATE apples SET id = id WHERE id = 1;  | 00:05:21.67203 |5111
           |             | 2369  | ExclusiveLock    | <IDLE> in transaction                    | 00:00:00.199268|2369
           |             | 2369  | ExclusiveLock    | <IDLE> in transaction                    | 00:00:00.199268|2369
           |             | 5226  | ExclusiveLock    | select pg_class.relname,pg_locks.transac | 00:00:00       |5226

我们是不是误解了什么或者是 postgres 中的错误？

Answer 1

没有错误，我认为您没有误解任何内容；你只是错过了几 block 拼图。

外键在内部使用行级锁定实现；从 Postgres 8.1 到 9.2，每当您更新引用表(在本例中为 apples)时，都会触发一个查询，在引用表上执行 SELECT FOR SHARE( 树)。因此，第一个事务中的 SELECT FOR UPDATE 会阻止第二个事务的引用完整性的 SELECT FOR SHARE。这就是导致第二个命令出现阻塞的原因。

现在我听到你大喊:“等等!为什么它会阻塞第二个命令而不是第一个？解释很简单，真的——那只是因为有一个简单的优化，在 key 未被修改时跳过内部 SELECT FOR SHARE。然而，这很简单，因为如果您第二次更新元组，则不会触发此优化，因为很难追踪到原始值。因此堵塞。

您可能还想知道为什么我说这是最高 9.2 --- 9.3 是什么？主要区别是在 9.3 中它使用 SELECT FOR KEY SHARE，这是一个新的、更轻的锁级别；它允许更好的并发性。如果您在 9.3 中尝试您的示例并将 SELECT FOR UPDATE 更改为 SELECT FOR NO KEY UPDATE(这是比 SELECT FOR UPDATE 更轻的模式> 这表示您可能要更新元组，但您 promise 不修改主键并 promise 不删除它)，您应该会看到它不会阻塞。 (此外，您可以在引用的行上尝试更新，如果您不修改主键，那么它也不会阻塞。)

这个 9.3 的东西是由你真正的补丁引入的 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182我认为这是一个非常酷的 hack(提交消息有更多细节，如果你关心这类东西的话)。但请注意，不要使用 9.3.4 之前的版本，因为该补丁非常复杂，以至于我们没有注意到一些严重的错误，我们最近才修复。