高并发下 PostgreSQL (串行化隔离级别)事务失败

问题场景：

项目场景：用jmeter对后端接口进行性能压力测试，样本数据100条（并发量不大），模拟用户批量提交数据（每次提交数据量一套测评试卷）。

pgsql版本：9.5.0

pgsql隔离级别：串行化

---

问题描述

在单用户场景下，可以正常提交成功，在用户并发场景下（模拟100用户提交）。压测结果一半事务提交失败，重试失败的事务会重新执行成功。

github也有网友遇到了类似的问题：

txn error · Issue #65603 · cockroachdb/cockroach · GitHub

事务失败的错误信息：

{"code":"500","message":"Could not commit JDBC transaction; 
nested exception is org.postgresql.util.PSQLException: ERROR: restart transaction: 
TransactionRetryWithProtoRefreshError: TransactionRetryError: 
retry txn (RETRY_SERIALIZABLE - failed preemptive refresh): \"sql txn\" 
meta={id=32394d8f key=/Table/3267/9/1563068205574938634/0 pri=0.00218591 
epo=0 ts=1661499475.103456864,1 min=1661499474.991734763,0 seq=211} lock=true 
stat=PENDING rts=1661499474.991734763,0 wto=false max=1661499475.491734763,0 
Stmt:<COMMIT TRANSACTION>\n  
Hint: See: CatDB Docs#retry_serializable","data":null}

---

原因分析：

高并发场景下，事务提交失败，重试后可以提交成功。可以推断不是代码逻辑的问题，问题最有可能出现在数据库执行过程中的读写冲突。

1. 数据库读写冲突,主要场景就是在串行化隔离级别下，写的过程中，对相应的数据行加了锁，导致读取一直等待。
2. 数据库读和写过程中，有没有出现全表扫描。

例如：

---

 如图：创建一张测试表 CREATE TABLE test(a int PRIMARY KEY,b int)

开启两个事务（左A，右B）：

• 事物A执行不提交，事务B（执行全表扫描 等待），必须等事务A提交
• 事务A执行不提交，加上where条件，无需全表扫描，可执行成功。

串行化隔离：

PostgreSQL: Documentation: 9.5: Transaction Isolation

在后续的pgsql文章中，会专门介绍弱隔离级别，现在针对出现的问题，我们主要来分析可串行化隔离（可串行化隔离被认为是最强的隔离级别）。

含义：可串行化保证即使事务可能会并发执行，但最终的结果与每次一个即串行执行结果相同。如果事务在单独运行时表现正确，那么在并发运行时，结果仍然正确，换句话说，数据库可以防止所有可能的竞争条件。

数据库实现串行化算法，广泛应用的是两阶段加锁（第一阶段事务执行之前需要获取锁，第二阶段事务结束时释放锁）。

数据库的每个对象都有一个读写锁来隔离读写操作。即锁可以处于共享模式或独占模式，基本用法如下：

• 如果事务要读取对象，必须以共享模式获得锁。可以有多个事务同时获得一个对象的共享锁，但是如果某个事务已经获得了对象的独占锁，则所有其他的事务必须等待。
• 如果事务要修改对象，必须以独占模式获取锁。不允许多个事务同时持有该锁（包括共享或独占模式），换言之，如果对象上已被加锁，则修改事务必须等待。
• 如果事务首先读取对象，然后尝试写入对象，则需要将共享锁升级为独占锁（等价于直接回获取锁）。
• 事务获得锁后，一直持有锁直到事务结束（包括提交或终止）。

由于使用了这么多的锁机制，所以容易出现死锁现象。数据库系统会自动检测事务之间的死锁现象，并强行终止其中的一个以打破僵局，这样另一个可以继续执行。而被终止的事务需要由应用层来重试。

解决方案：

检查索引建的是否合理：

• 打印提交场景下的sql语句
• 分析sql读写中是否有数据交集导致的冲突（出现竞争条件）
• 对于有交集的数据，建合理的索引。

上述步骤检查后，再次模拟相同场景下的压测，大部分问题会被解决。 如果问题依然存在

• 记录每张表的索引，检查是否是打印的sql的谓词
• 检查谓词锁和索引区间锁

再次压测问题完美解决，提高并发，模拟（1000）用户同时压测，均成功执行。

扩展：

谓词锁：可串行化必须防止幻读问题（即一个事务改变另一个事务的查询结果）。

• 如果事务A想要读取某些满足匹配条件的对象，它必须以共享模式获得查询条件的谓词锁。如果另一个事务B正持有任何一个匹配对象的互斥锁，那么A必须等到B释放锁之后继续执行。
• 如果事务A想要插入 更新或删除任何对象，则必须检查所有旧值和新值是否与现有的任何谓词锁匹配（即冲突）。如果事务B持有这样的谓词锁，那么B必须等待A完成后才能继续。

谓词锁可以保护数据库中那些尚不存在的但可能会被马上插入的对象（幻读）。

索引区间锁：谓词锁性能不佳，导致非常耗时。因此大多数会采用索引区间锁（nextkey locking）

即将保护的对象扩大化，这样肯定是安全的。而且锁的开销也很低。缺点是：锁定更大的范围，从而导致冲突更频繁。

本问题只有100个并发，但是只有不到一半的成功了，正式因为底层采用了索引区间锁导致锁定了更大的范围，引发频繁冲突。最终将导致区间变大的区间的时间列的索引删除，解决了问题。