postgresql - 为什么 PostgreSQL 认为两个可序列化事务之间存在冲突？

Question

我正在尝试弄清楚 PostgreSQL 中的可序列化隔离级别是如何工作的。从理论上讲，根据 PostgreSQL 自己的文档，PostgreSQL 应该足够聪明，能够以某种方式检测序列化冲突并自动回滚有问题的事务。然而，当我自己尝试使用可序列化隔离级别时，我偶然发现了很多误报，并开始怀疑我自己对可序列化概念或 PostgreSQL 对其实现的理解。您可以在下面找到此类误报的最简单示例之一:

create table mytab(
    class integer,
    value integer not null
);

create index mytab_class_idx on mytab (class);

insert into mytab (class, value) values (1, 10);
insert into mytab (class, value) values (1, 20);
insert into mytab (class, value) values (2, 100);
insert into mytab (class, value) values (2, 200);

表格数据如下:

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

然后我运行两个并发事务。代码中的 Step n 注释显示了我执行语句的顺序。遵循 https://stackoverflow.com/a/42303225/3249257 的建议我明确禁用顺序扫描以强制 PostgreSQL 使用索引:

SET enable_seqscan=off;

事务 A:

begin; -- step 1
select sum(value) from mytab where class = 1; -- step 2
insert into mytab(class, value) values (3, 30); -- step 5
commit; -- step 7

事务 B:

begin; -- step 3
select sum(value) from mytab where class = 2; -- step 4
insert into mytab(class, value) values (4, 300); -- step 6
commit; -- step 8

据我了解，这两项交易之间应该没有任何冲突。他们不接触同一行。但是，当我提交第二个事务时，它失败并出现此错误:

[40001] ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
Hint: The transaction might succeed if retried.

这是怎么回事？我对可序列化隔离级别的理解有缺陷吗？这是这个答案中提到的 PostgreSQL 启发式方法的失败吗 https://stackoverflow.com/a/50809788/3249257 ？

我在 x86_64-apple-darwin18.6.0 上使用 PostgreSQL 11.5，由 Apple LLVM 版本 10.0.1 (clang-1001.0.46.4) 编译，64 位。

Answer 1

这里的问题是 PostgreSQL 用来确定并发事务之间是否存在冲突的谓词锁 (SIReadLock)。如果您在事务执行过程中运行以下查询，您将看到这些锁:

select relation::regclass, locktype, page, tuple, pid from pg_locks
where mode = 'SIReadLock';

在这种情况下，问题出在 mytab_class_idx 索引上的页面锁定。如果并发事务碰巧为mytab_class_idx关系的同一页获取了锁，就会发生序列化冲突。如果他们为不同的页面获取锁，则他们都成功提交。

如果像上面的问题一样没有足够的数据，所有行的索引条目将落在同一页上，那么不可避免地会发生序列化冲突。对于足够大的表，序列化冲突很少会发生，尽管不是尽可能少。