Serialization 失败
错误信息
能不 serialize access due 到 concurrent 更新
快速摘要
Under SERIALIZABLE isolation, 数据库 detected conflict 之间 concurrent 事务 and aborted one 到 maintain correctness.
为什么发生
Two concurrent 事务修改 overlapping 数据 under SERIALIZABLE isolation
高 contention on 相同行
application 做不 retry 事务 on serialization failures
最小示例
-- Two concurrent transactions under SERIALIZABLE
-- both try to update the same row
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT; -- may fail with serialization failure-- Retry on serialization failure
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;
-- If it fails: wait briefly and retry the whole transactionSerialization failures 是 expected under SERIALIZABLE isolation. application 应该 retry 事务.
如何诊断
检查 isolation level 的事务
Identify conflicting concurrent 事务
检查如果 application handles serialization failures 使用 retries
如何修复
Retry 事务 — serialization failures 是 expected and retriable
Use lower isolation level (READ COMMITTED) 如果 serializable 是不 必需
Reduce contention 通过 redesigning 事务到 touch fewer 行
如何预防
Implement retry logic for 事务 under SERIALIZABLE isolation
Use lowest isolation level 那个 meets 你的 correctness 需求
Keep 事务短 到 reduce chance 的 conflicts