在PostgreSQL中并发DELETE / INSERT锁定问题

CREATE TABLE test (id INT PRIMARY KEY);

INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);

1:DELETE FROM test WHERE id=1;
2:INSERT INTO test VALUES (1);
3:COMMIT;

S1-1 runs (1 row deleted)
S2-1 runs (but is blocked since S1 has a write lock)
S1-2 runs (1 row inserted)
S1-3 runs, releasing the write lock
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???
S2-2 runs, reports a unique key constraint violation

#1 楼

Mat和Erwin都是对的，我只是添加另一个答案，以用不适合发表评论的方式进一步扩展他们所说的内容。由于他们的回答似乎并不能使所有人满意，因此有人建议应咨询PostgreSQL开发人员，我是其中的一个，我将详细说明。在以事务隔离级别运行的事务中，限制是未提交事务的工作必须不可见。当已提交事务的工作可见时，将取决于实现。您所指出的是两种产品选择实施该方法的差异。两种实现都没有违反该标准的要求。

这是PostgreSQL内部的详细信息：位置，因为S1可能仍会回滚，但是S1现在在该行上拥有一个锁，以便任何其他尝试修改该行的会话都将等待以查看S1是提交还是回滚。除非尝试使用READ COMMITTED或SELECT FOR UPDATE锁定表，否则对表的任何读取仍然可以看到旧行。

S1-1 runs (1 row deleted)

S2现在必须等待以查看结果S1。如果S1将回滚而不是提交，则S2将删除该行。请注意，如果S1在回滚之前插入了新版本，那么从任何其他事务的角度来看，新版本都不会存在，从任何其他事务的角度来看，旧版本也不会被删除。

S2-1 runs (but is blocked since S1 has a write lock)

该行独立于旧行。如果存在id = 1的行的更新，则旧版本和新版本将相关，并且S2可以在该行的未阻止状态删除该行的更新版本。新行恰好具有与过去存在的某行相同的值，这使其与该行的更新版本不同。

S1-2 runs (1 row inserted)

因此S1的更改得以保留。一行不见了。已添加一行。

S1-3 runs, releasing the write lock

内部发生的事情是，存在从一个行的一个版本指向同一行的下一个版本的指针。更新。如果该行被删除，则没有下一个版本。当SELECT FOR SHARE事务在发生写冲突时从一个块中唤醒时，它将跟随该更新链到达末尾。如果尚未删除该行，并且该行仍符合查询的选择条件，则将对其进行处理。该行已被删除，因此S2的查询继续进行。

S2在扫描表期间可能会或可能不会到达新行。如果是这样，它将看到新行是在S2的READ COMMITTED语句启动之后创建的，因此它也不是它可见的行集合的一部分。

如果PostgreSQL重新启动S2的整个DELETE语句从新快照开始，它的行为与SQL Server相同。由于性能原因，PostgreSQL社区未选择这样做。在这种简单的情况下，您永远不会注意到性能的差异，但是如果在阻塞时将一千万行插入到DELETE中，您肯定会注意到。 PostgreSQL选择性能时需要权衡，因为更快的版本仍然符合标准的要求。

S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???

当然，该行已经存在。这是图片中最令人惊讶的部分。

虽然这里有一些令人惊讶的行为，但是所有内容都符合SQL标准，并且在该标准的“特定于实现”的范围内。如果您假设所有实现中都会存在某些其他实现的行为，那肯定会令人惊讶，但是PostgreSQL尽力避免在DELETE隔离级别上发生序列化失败，并允许某些行为不同于其他产品以实现该目的。 。

现在，就我个人而言，我不喜欢任何产品实现中的READ COMMITTED事务隔离级别。从交易的角度来看，它们都允许种族条件产生令人惊讶的行为。一旦某人习惯了一种产品所允许的怪异行为，他们就会倾向于认为“正常”，而另一种产品所选择的取舍则很奇怪。但是，每种产品都必须对实际上未实现为READ COMMITTED的任何模式进行某种折衷。 PostgreSQL开发人员选择在SERIALIZABLE中划清界限的地方是最大程度地减少阻塞（读取不阻塞写入，写入不阻塞读取）并最小化序列化失败的机会。

标准要求READ COMMITTED事务是默认事务，但是大多数产品不这样做，因为它会导致事务隔离级别更高的性能下降。选择SERIALIZABLE时，某些产品甚至不提供真正可序列化的事务-最著名的是Oracle和9.1之前的PostgreSQL版本。但是，使用真正的SERIALIZABLE事务是避免竞争条件带来的意外影响的唯一方法，并且SERIALIZABLE事务始终必须要么阻塞以避免竞争条件，要么回滚某些事务以避免发展的竞争条件。 SERIALIZABLE事务的最常见实现是严格的两阶段锁定（S2PL），它同时具有阻塞和序列化失败（以死锁的形式）。

全面披露：我与MIT的Dan Ports合作使用一种称为“可序列化快照隔离”的新技术，将真正可序列化的事务添加到PostgreSQL 9.1版。

#2 楼

我相信这是设计使然，根据对PostgreSQL 9.2的已提交读隔离级别的描述：


UPDATE，DELETE，SELECT FOR UPDATE和SELECT FOR SHARE命令的行为相同在搜索目标行方面与SELECT相同：它们只会找到从命令开始时间1开始提交的目标行。但是，这样的目标行在被发现时可能已经被另一个并发事务更新（或删除或锁定）。在这种情况下，可能的更新程序将等待第一个更新事务提交或回滚（如果仍在进行中）。如果第一个更新程序回滚，则其效果将被否定，第二个更新程序可以继续更新最初找到的行。如果第一个更新程序提交，则第二个更新程序将忽略该行（如果第一个更新程序删除了该行），否则它将尝试将其操作应用于该行的更新版本。


S1的S2启动时，您在DELETE中插入的行尚不存在。因此，按上述（1）进行删除在S2中将看不到它。 S1的S2根据（2）会忽略DELETE删除的那个。

因此在S2中，删除不执行任何操作。但是，当插入操作出现时，确实会看到S1的插入操作：


因为“读取已提交”模式以一个新快照开始了每个命令，该快照包含了该时刻之前提交的所有事务，因此无论如何，同一事务中的命令将看到已提交的并发事务的效果。上面的问题是单个命令是否看到数据库的绝对一致视图。


因此S2尝试插入会失败，并违反约束。

继续读取文档，使用可重复读取甚至可序列化都无法完全解决您的问题-第二个会话将失败，并在delete上出现序列化错误。

这将使您可以重试事务。

#3 楼

我完全同意@Mat的出色回答。我只写另一个答案，因为它不能放在注释中。

回复您的评论：S2中的DELETE已经挂接到特定的行版本上。由于这同时被S1杀死，因此S2认为自己成功。尽管乍看之下并不明显，但一系列事件实际上是这样的：

   S1 DELETE successful  
S2 DELETE (successful by proxy - DELETE from S1)  
   S1 re-INSERTs deleted value virtually in the meantime  
S2 INSERT fails with unique key constraint violation

这都是设计使然。您确实需要根据需求使用SERIALIZABLE事务，并确保重试序列化失败。

#4 楼

使用DEFERRABLE主键，然后重试。

#5 楼

我们也面临这个问题。我们的解决方案是在select ... for update之前添加delete from ... where。隔离级别必须为“已读”。