PostgreSQL中xmin大于xmax的场景及事务挂起问题咨询

问题背景 #

一条尝试删除部分行的事务一直处于挂起状态，事务ID为1838047967。检查受影响行的系统字段发现：

• 部分行的xmax被设置为该事务ID
• 其中少量行存在xmin > xmax的情况，且这些行的xmin大于该事务对应的backend_xmin值

数据摘录 #

tableoid   cmax xmax       cmin xmin        ctid        id
15979773    1   1838047967  1   1838054693  (10508,58)  391992038
15979773    1   1838047967  1   1838071633  (10525,48)  391833021
15979773    1   1838047967  1   1838075176  (10548,9)   391294625
15979773    1   1838047967  1   1838075891  (10543,28)  391590003
15979773    1   1838047967  1   1838076423  (10539,37)  391871511
15979773    1   1838047967  1   1838076905  (10539,39)  392007013
15979773    1   1838047967  1   1838078116  (10543,47)  391833029
15979773    1   1838047967  1   1838078121  (10540,56)  391855133
15979773    1   1838047967  1   1838078123  (10541,54)  391867004
15979773    1   1838047967  1   1838078125  (10550,27)  391503024

事务状态验证 #

SELECT pg_xact_status (xid8 '1838047967');
-- 输出：in progress

XID回卷排除 #

通过启动新事务验证XID未回卷：

begin;
    SELECT * FROM txid_current();
end;
commit;

-- 输出：1848210508

环境限制 #

当前使用PostgreSQL版本为14.3，无法使用pageinspect工具。

现象解释 #

1. xmin > xmax的本质 #

PostgreSQL中，xmin是创建/更新该行的事务ID，xmax是删除/锁定该行的事务ID。正常情况下xmin早于xmax，但出现xmin > xmax的核心原因是：删除事务启动后，这些行被后续的新事务创建或更新了。

2. 具体场景还原 #

• 事务1838047967启动，进入删除流程，此时它的backend_xmin被确定（代表当前数据库中活跃事务的最小ID，是可见性判断的基准）。
• 在该删除事务尚未完成的期间，其他新事务（ID从1838054693到1838078125）创建或更新了这些行。
• 当删除事务尝试删除这些行时，会将自己的ID写入这些行的xmax字段，但这些行的xmin（创建/更新它们的事务ID）本身就大于删除事务的ID，同时也大于删除事务的backend_xmin——因为这些新事务是在删除事务启动后才开始的，不在删除事务启动时的活跃事务集合里。

3. 事务挂起的关联影响 #

这些行的存在不会直接导致删除事务挂起，但删除事务持续挂起会让这些行的xmax一直处于“未提交删除”状态：

• 对于其他事务来说，这些行是否可见取决于各自的backend_xmin：如果其他事务的backend_xmin小于这些行的xmin，则能看到这些行；如果大于，则需要等待删除事务结束才能判断可见性。
• 要解决挂起问题，需要先定位删除事务挂起的原因（比如持有锁等待、长时间未提交、网络中断导致的僵尸事务等）。

4. 后续处理建议 #

• 先查询事务的详细状态：

  SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE xid = 1838047967;
  

• 如果事务确实是僵尸状态（比如客户端已断开但未清理），可以使用pg_terminate_backend(pid)终止该事务，之后这些行的xmax标记会被清理，恢复正常的可见性逻辑。

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