PostgreSQL锁定机制中的错误或对该机制的误解 [英] Bug in PostgreSQL locking mechanism or misunderstanding of the mechanism
问题描述
我们遇到了PostgreSQL 9.0.12锁定机制的问题.
We encountered an issue with PostgreSQL 9.0.12 locking mechanism.
这是我们重现此问题的最小代码:
This is our minimal code to reproduce the issue:
场景
Transaction 1 Transaction 2
BEGIN BEGIN
...... select trees for update;
update apples;
--passes
update apples;
-- stuck!
复制代码: 如果您想在PostgreSQL中进行尝试-这是您可以复制/粘贴的代码.
reproduce code: If you want to try it in your PostgreSQL - here is a code you can copy/paste.
我有以下数据库模式:
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:
open two psql shells:
在外壳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;
苹果的第二次更新将停滞不前，外壳2的麻烦似乎正在等待外壳1的事务完成.
The second update of apples will stuck and it seems that the porcess of shell 2 is wating on the transaction of shell 1 to finish.
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中的错误?
Have we misunderstood something or it is a bug in postgres?
推荐答案
没有错误，而且我认为您没有误解任何东西.您只是错过了几个难题.
There is no bug, and I don't think you're misunderstanding anything; you're just missing a couple of pieces of the puzzle.
外键是使用行级锁定在内部实现的；从Postgres 8.1开始，直到9.2，每当您更新引用表(在这种情况下为apples)时，都会触发对引用表(trees)执行SELECT FOR SHARE的查询.这样，第一个事务中的SELECT FOR UPDATE会阻止第二个事务中的引用完整性的SELECT FOR SHARE.这就是导致第二个命令阻塞的原因.
Foreign keys are implemented internally using row-level locking; starting from Postgres 8.1 and up to 9.2, whenever you update the referencing table (apples in this case), a query is fired that does SELECT FOR SHARE on the referenced table (trees). So that SELECT FOR UPDATE in the first transaction blocks the SELECT FOR SHARE of the referential integrity for the second transaction. This is what causes the block in the second command.
现在我听到你大喊:等等！它为什么会阻塞第二个命令而不是第一个命令?实际上，这种解释很简单-这只是因为有一个简单的优化，当未修改密钥时，该优化跳过了内部SELECT FOR SHARE.但是，这很简单，因为如果第二次更新元组，则该优化将不会触发，因为更难追踪原始值.因此出现了阻塞.
Now I hear you yell, "Wait! How come it blocks on the second command and not the first? The explanation is simple, really -- that's just because there is a simple optimization that skips the internal SELECT FOR SHARE when the key is not being modified. However, this is simplistic in that if you update a tuple a second time, this optimization will not fire because it's harder to track down the original values. Hence the blockage.
您可能还想知道为什么我说这是9.2-9.3是什么?主要区别在于，在9.3中，它使用SELECT FOR KEY SHARE，这是一个新的，更轻便的锁定级别.它允许更好的并发性.如果您在9.3中尝试示例，并且还将SELECT FOR UPDATE更改为SELECT FOR NO KEY UPDATE(这是一种比SELECT FOR UPDATE更轻的模式，表示您可能要更新元组，但是您承诺不修改主键，而是保证不要删除它)，您应该会看到它没有被阻止. (此外，您可以尝试在引用的行上执行UPDATE，并且如果您不修改主键，那么它也不会阻塞.)
You might also be wondering why I said this is up to 9.2 --- what's with 9.3? The main difference there is that in 9.3 it uses SELECT FOR KEY SHARE, which is a new, lighter lock level; it allows for better concurrency. If you try your example in 9.3 and also change the SELECT FOR UPDATE to SELECT FOR NO KEY UPDATE (which is a lighter mode than SELECT FOR UPDATE that says you are maybe going to update the tuple, but you promise to not modify the primary key and promise not to delete it), you should see it doesn't block. (Also, you can try an UPDATE on the referenced row and if you don't modify the primary key, then it will also not block.)
这9.3项内容是由您的补丁真正引入的，称为 http: //git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182 ，我认为这是一个很酷的技巧(提交消息包含更多详细信息，如果您关心的话这类东西).但是请注意，请勿使用9.3.4之前的版本，因为该补丁非常复杂，以至于一些严重的bug未被注意到，我们只是在最近才修复.
This 9.3 stuff was introduced by a patch by yours truly as http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182 and I think it was a pretty cool hack (The commit message has some more details, if you care about that sort of stuff). But beware, do not use versions prior to 9.3.4 because that patch was so hugely complex that a few serious bugs went unnoticed and we only fixed recently.
这篇关于PostgreSQL锁定机制中的错误或对该机制的误解的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
