为什么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://dev59.com/wVgQ5IYBdhLWcg3wvGe4#42303225的建议，我明确禁用了顺序扫描以强制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://dev59.com/oqzka4cB1Zd3GeqP3BZ0#50809788？

我正在使用PostgreSQL 11.5 on x86_64-apple-darwin18.6.0, compiled by Apple LLVM version 10.0.1 (clang-1001.0.46.4), 64-bit。

- Denis Stafichuk

---

快速猜测：整个表的独占锁 - https://wiki.postgresql.org/wiki/Lock_Monitoring 和 https://www.postgresql.org/docs/12/explicit-locking.html#LOCKING-DEADLOCKS - Lukasz Szozda

你使用的是哪个版本？你能准确地展示一下如何交错执行这些命令吗？如果我先运行A的前三行，然后运行B的前三行，接着提交A，再提交B，我就不会遇到问题。 - jjanes

@jjanes，我已经编辑了问题来回答你的问题。 - Denis Stafichuk

@LukaszSzozda，这不是整个表的独占锁，但是您的评论帮助我大大找出了问题所在。我已经在下面发布了我的答案。 - Denis Stafichuk

@DenisStafichuk 很高兴能够帮助。干得好 :) - Lukasz Szozda

Answer 1

这里的问题在于谓词锁（SIReadLock），PostgreSQL使用它来确定并发事务之间是否存在冲突。如果您在事务执行期间运行下面的查询，您将会看到这些锁：

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

这种情况是因为在 mytab_class_idx 索引上存在页面锁的问题。如果并发事务恰好获取了 mytab_class_idx 关系的同一页的锁定，则会发生串行化冲突。如果它们获取不同页面的锁定，则它们都可以成功提交。

如果像上面的问题中那样没有足够的数据，所有行的索引条目将落在同一页上，这时不可避免地会发生串行化冲突。对于足够大的表，虽然不如预期的罕见，但串行化冲突仍会时有发生。

- Denis Stafichuk