PostgreSQL简单插入场景下SERIALIZABLE事务序列化失败问题

背景环境 #

-- psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1), server 16.1 (Debian 16.1-1.pgdg120+1))
create table kv(
  key TEXT NOT NULL PRIMARY KEY, 
  value TEXT);

事务执行与问题复现 #

终端1事务 #

begin isolation level serializable read write;
SET enable_seqscan=off;

select value from kv where key = 'a';--(0 rows)
insert into kv(key, value) values ('a', 'vala');--INSERT 0 1
commit;--COMMIT

终端2事务 #

begin isolation level serializable read write;
SET enable_seqscan=off;

select value from kv where key = 'b';--(0 rows)
insert into kv(key, value) values ('b', 'valb');--INSERT 0 1
commit;

终端2提交时触发错误：

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.

100%复现的操作顺序 #

1. 终端1开启事务
2. 终端2开启事务
3. 终端1执行SELECT
4. 终端2执行SELECT
5. 终端1执行INSERT
6. 终端2执行INSERT
7. 终端1提交
8. 终端2提交

锁信息对比 #

场景1（select-select-insert-insert顺序）提交前的锁信息 #

查询语句：

select nspname, relname, l.* 
from pg_locks l 
    join pg_class c on (relation = c.oid) 
    join pg_namespace nsp on (c.relnamespace = nsp.oid)
where pid in (select pid 
              from pg_stat_activity
              where datname = current_database() 
                and query != current_query());

查询结果：

场景2（select-insert-select-insert顺序）提交前的锁信息 #

注：此场景下两个事务均可成功提交，锁信息如下：

已调整的谓词锁参数 #

show all;

返回的关键参数：

技术疑问解答 #

1. 从逻辑上看这两个事务显然是可序列化的，是否仅因PostgreSQL无法自行验证该特性？ #

是的。PostgreSQL的SERIALIZABLE隔离级别基于**可序列化快照隔离（SSI）**实现，SSI通过检测读写依赖和潜在的序列化异常来保证事务可串行化，但它是一种保守的检测机制——为了避免漏判异常，会在无法精确判断事务是否真的存在冲突时，主动触发序列化失败，哪怕事务逻辑上是可串行化的。这个场景就属于这种"误判"，因为SSI无法精确识别两个事务操作的是完全不重叠的键，只能通过更粗粒度的锁依赖来判断。

2. 为何PostgreSQL无法识别这种可序列化性？为何会对主键索引持有两个关系锁？ #

• 无法识别的核心原因是SSI的谓词锁粒度限制：当查询返回空结果时，PostgreSQL无法在主键索引上找到具体的页或元组来加谓词锁，只能退而求其次，对整个主键索引关系加SIReadLock。这种粗粒度的锁会让SSI认为两个事务都依赖于整个索引的"无匹配行"状态，从而检测出潜在的循环依赖。
• 两个关系级SIReadLock是因为两个事务都执行了针对空结果的SELECT，各自对主键索引关系加了SIReadLock。当第一个事务插入后，第二个事务的插入会被SSI判定为破坏了第一个事务的读取依赖（SSI无法区分'a'和'b'的差异，只能看到索引被修改），从而触发冲突。

3. 为何select-select-insert-insert顺序会失败，而select-insert-select-insert顺序可成功？ #

• 在select-insert-select-insert顺序中，终端1先完成SELECT和INSERT：此时终端1的INSERT会在主键索引的具体页上添加SIReadLock（而非关系级），当终端2执行SELECT时，虽然返回空结果，但PostgreSQL可以针对索引中'b'所在的页加页级SIReadLock，而非关系级。这种更细粒度的锁让SSI能识别两个事务操作的是不同的索引区域，不会检测到冲突。
• 而select-select-insert-insert顺序中，两个事务都先执行了针对空结果的SELECT，都加了关系级SIReadLock。当第一个事务插入后，SSI会认为第二个事务的插入修改了第一个事务依赖的整个索引状态，从而判定为不可序列化。

4. 如何让这种无重叠键的简单访问模式正常工作？ #

有几种可行方案：

• 改用INSERT ... ON CONFLICT替代先SELECT后INSERT：这是最优方案，把检查和插入合并为一个原子操作，避免了单独的SELECT带来的谓词锁问题。示例：

  begin isolation level serializable read write;
  insert into kv(key, value) values ('a', 'vala') on conflict do nothing;
  commit;
  

• 降低隔离级别到REPEATABLE READ：如果业务可以接受该隔离级别（它能避免脏读、不可重复读和幻读，但不保证完全可串行化），这种无重叠键的操作不会出现问题。
• 主动重试序列化失败的事务：PostgreSQL的错误提示已经建议重试，在应用层捕获序列化失败错误后，重新执行整个事务即可，因为这种冲突是"误判"，重试后大概率成功。
• 调整查询执行计划：确保使用索引扫描（你已经设置enable_seqscan=off），空结果时如果能定位到更细的索引范围，可能会减少关系级锁的使用，但空结果时仍可能退化为关系锁，效果有限。

内容的提问来源于stack exchange，提问作者xyzzyz