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

Question

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

postgresqllockingconcurrencyserialization

40

这很简单，但是我对PG（v9.0）的作用感到困惑。我们从一个简单的表开始：

CREATE TABLE test (id INT PRIMARY KEY);

和几行：

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

使用我最喜欢的JDBC查询工具（ExecuteQuery），我将两个会话窗口连接到存放这个表的数据库中。它们都是事务性的（即，auto-commit=false）。我们称它们为S1和S2。对于每个会话窗口，使用相同的代码段：

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

现在，在SQLServer中这个工作得很好。当S2执行删除操作时，它报告删除了1行。然后S2的插入操作也正常工作。我怀疑PostgreSQL是锁定了包含该行的表中的索引，而SQLServer则锁定实际的键值。我是对的吗？这个问题能解决吗？

- DaveyBob

5个回答

22

我认为这是有意设计的，根据 PostgreSQL 9.2 的读取提交隔离级别描述：更新、删除、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在搜索目标行时与 SELECT 相同：它们只会找到在命令开始时间之前提交的目标行¹。然而，在找到目标行时，该目标行可能已经被另一个并发事务更新（或删除或锁定）。在这种情况下，欲进行更新的事务将等待第一个更新事务提交或回滚（如果仍在进行中）。如果第一个更新者回滚，则其效果将被取消，第二个更新者可以继续对最初找到的行进行更新。如果第一个更新者提交，第二个更新者将忽略该行，如果第一个更新者删除了它²，否则它将尝试将其操作应用于行的更新版本。你在S1中插入的行在S2的DELETE开始时还不存在。所以根据上述（1）的情况，S2中的删除操作不会看到它。根据（2），S1删除的那个被S2的DELETE忽略了。所以在S2中，删除操作什么都没做。然而，当插入操作出现时，它确实看到了S1的插入：因为Read Committed模式在每个命令开始时都使用一个包含所有已提交事务的新快照，所以同一事务中的后续命令无论如何都会看到已提交并发事务的影响。上面的问题是关于单个命令是否能够看到数据库的绝对一致视图。所以S2尝试的插入操作由于违反约束而失败。继续阅读该文档，即使使用“可重复读”或甚至“串行化”也无法完全解决您的问题 - 第二个会话将在删除操作时出现序列化错误。这样做可以让您重新尝试事务。

- Mat

谢谢Mat。虽然看起来是这样的情况，但这个逻辑似乎存在一个缺陷。在READ_COMMITTED隔离级别下，我认为这两个语句必须在事务内成功执行： DELETE FROM test WHERE ID=1 INSERT INTO test VALUES (1)我的意思是，如果我先删除行再插入行，那么插入操作应该成功。SQLServer就能正确处理这个问题。目前，我在一个需要与两种数据库配合工作的产品中，处理这种情况非常困难。 - DaveyBob

12

我完全同意@Mat's excellent answer。我只是写了另一个答案，因为它无法适应评论。回复你的评论：S2中的DELETE已经钩住了特定的行版本。由于这个版本在此期间被S1杀死，所以S2认为自己成功了。虽然一眼看不出来，但事件序列实际上是这样的： S1 DELETE成功 S2 DELETE（通过代理-从S1删除） S1在此期间虚拟重新插入已删除的值 S2插入失败，违反唯一键约束这都是有意设计的。你真的需要使用SERIALIZABLE事务来满足你的需求，并确保在序列化失败时重试。

- Erwin Brandstetter

0

使用可延迟的主键DEFERRABLE，然后重试。

- Frank Heikens

谢谢你的建议，但是使用DEFERRABLE并没有任何区别。文档看起来应该是这样的，但实际上并不是。 - DaveyBob

-2

我们也遇到了这个问题。我们的解决方案是在delete from ... where之前添加select ... for update。隔离级别必须为读已提交。

- Mian Huang

- kgrittn · Accepted Answer

Mat和Erwin都是正确的，我只是在进一步扩展他们所说的内容，以一种不适合评论的方式添加另一个答案。由于他们的答案似乎不能满足所有人，并且有建议应咨询PostgreSQL开发人员，而我就是其中之一，因此我将详细说明。重要的是，在SQL标准下，在运行在“READ COMMITTED”事务隔离级别下的事务中，限制是未提交事务的工作不能被看到。已提交事务的工作何时变得可见是实现相关的。您指出的是两个产品选择实现方式的差异。任何一种实现都没有违反标准的要求。以下是PostgreSQL中的详细情况：

S1-1运行（删除1行）

旧行仍然保留在原地，因为S1可能仍然回滚，但是S1现在持有该行的锁定，因此任何其他尝试修改该行的会话都将等待看到S1是否提交或回滚。表的任何读取仍然可以看到旧行，除非它们尝试使用SELECT FOR UPDATE或SELECT FOR SHARE锁定它。 S2-1运行了（但由于S1有一个写锁而被阻止）。

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

S1-2运行了（插入了1行）。

这一行与旧行无关。如果对id = 1的行进行了更新，旧版本和新版本将有关联，当S2解除阻塞时，它可以删除已更新的行的版本。一个新行恰好具有与过去存在的某行相同的值，并不能使其成为该行的更新版本。

S1-3运行，释放写锁。

所以S1的更改已持久化。一行消失了。添加了一行。

S2-1运行，现在可以获得锁。但报告说已删除0行。什么？？？

发生的内部情况是，如果一行的版本被更新了，那么就会有一个指针从该行的一个版本指向下一个版本。如果该行被删除了，就没有下一个版本了。当一个“读提交”事务在写冲突上被唤醒时，它会沿着更新链一直到最后；如果该行没有被删除，并且仍然符合查询的选择条件，它将被处理。这一行已经被删除了，所以S2的查询继续进行。 S2在扫描表时可能会或可能不会遇到新行。如果遇到了，它会看到新行是在S2的“DELETE”语句开始之后创建的，因此不属于它可见的行集合。如果PostgreSQL重新从头开始使用新的快照来重新启动S2的整个DELETE语句，它的行为将与SQL Server相同。出于性能原因，PostgreSQL社区选择不这样做。在这种简单的情况下，您可能永远不会注意到性能上的差异，但如果您在执行DELETE时被阻塞了一千万行，那肯定会感觉到差别。在这里，PostgreSQL在性能和符合标准要求之间做了权衡。 S2-2运行时报告了唯一键约束冲突。当然，行已经存在。这是图片中最不令人惊讶的部分。虽然这里有一些令人惊讶的行为，但一切都符合 SQL 标准，并在标准规定的“实现特定”范围内。如果您认为所有实现都会具有其他实现的行为，那么当然会感到意外，但 PostgreSQL 非常努力地避免在“读提交”隔离级别中出现序列化故障，并允许一些与其他产品不同的行为以实现这一点。现在，从个人角度来看，我并不是任何产品实现中 READ COMMITTED 事务隔离级别的拥趸。它们都允许竞争条件创建从事务角度来看令人惊讶的行为。一旦有人习惯于某个产品允许的奇怪行为，他们倾向于认为那是“正常的”，而另一个产品选择的权衡则很奇怪。但是，对于任何未实际实现为 SERIALIZABLE 的模式，每个产品都必须做出某种权衡。PostgreSQL 开发人员在 READ COMMITTED 中选择的界限是最小化阻塞（读取不会阻塞写入，写入不会阻塞读取）并最小化序列化故障的机会。标准要求SERIALIZABLE事务成为默认选项，但大多数产品并不这样做，因为它会导致性能下降，相对较宽松的事务隔离级别更受欢迎。有些产品甚至在选择SERIALIZABLE时并没有提供真正的可串行化事务，其中最著名的是Oracle和9.1版本之前的PostgreSQL。然而，使用真正的SERIALIZABLE事务是避免竞态条件带来意外效果的唯一方法，SERIALIZABLE事务必须要么阻塞以避免竞态条件，要么回滚某些事务以避免竞态条件的发生。最常见的SERIALIZABLE事务实现是严格两阶段锁定（S2PL），它既存在阻塞问题，也存在序列化失败（死锁）。

完全透明披露：我与MIT的Dan Ports合作，在PostgreSQL 9.1版本中使用一种名为Serializable Snapshot Isolation的新技术添加了真正的可串行化事务。