我们遇到了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外壳：
在外壳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 8.1到9.2开始，每当更新引用表（在本例中为apples）时，都会触发一个对引用表（SELECT FOR SHARE）执行trees的查询。因此，第一个事务中的SELECT FOR UPDATE将阻塞第二个事务的引用完整性的SELECT FOR SHARE。这就是导致第二个命令中的块的原因。
现在我听到你喊，“等等！为什么它挡在第二个命令上而不是第一个？这个解释很简单，真的——这只是因为有一个简单的优化，在没有修改键的时候跳过了内部的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更轻量级的模式，该模式表示您可能要更新元组，但您承诺不修改主键并保证不删除它），则应该看到它不会阻塞。（此外，您可以尝试对引用行进行更新，如果不修改主键，则它也不会被阻止。）
这个9.3的东西是由你的一个补丁真正引入的http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182，我认为这是一个非常酷的黑客（commit消息有更多的细节，如果你关心这类东西的话）。但是请注意，不要使用9.3.4之前的版本，因为这个补丁非常复杂，以至于一些严重的错误没有被注意到，我们只是最近才修复。