一个PG主键重复的案例

0    389    2

👉 本文共约2432个字,系统预计阅读时间或需10分钟。

前言

昨天早上同事转了一封邮件过来,发现某张表的主键重复了!😵 印象中这是第二次遇到了,让我们一起分析下这个罕见的案例。根因很重要呀!耐心看完

分析

出问题的表结构不复杂,主要字段如下(部分信息做了脱敏)

昨天开发反馈更新的时候提示主键重复,于是上去看了一下

果然主键重复了,DBA 表示惊呆了!主键的功能就是非空 + 唯一,那是哪里出了问题呢?

先看下这两条数据的事务状态,是否是可见性导致的

第一条数据 xmax 字段有值,说明这条数据要么被更新过,要么获取过行锁,这点很好验证

再看看 ctid

根据过往的一些经验,初步怀疑可能是索引出了问题,比如 index only scan 仅在索引中取数据,索引和存储上的数据不一致,这个是有具体的案例的,可以翻下之前的文章。看下执行计划:

顺序扫描和索引扫描返回的数据一致,那不是索引的问题。不过为了确保索引是正确的状态(属实踩雷踩多了),还是使用插件 amcheck 检查了一下。

em,索引也是正确的状态,说明有别于以往的案例。继续分析一下

CTID 表示这两行数据分别位于 9789 和 9790 这两个数据块,使用 pageinspect 窥探一下底层文件

这里可以看到一些差别,第一行数据的 ctid 指向了 '(106234,2)',同时表示该行已经冻结过了 XMIN_INVALID|XMIN_COMMITTED

通过 pageinspect 查看,发现有这行数据,但是通过指定查询是没有数据的

假如查询的时候第一条数据通过 ctid '(9789,8)' 到 ctid '(106234,2)' 这个 ctid 链进行查找的话,数据的确无法可见,也就不会有第一条数据了,主键也就不会重复了。难道是数据不一致了?ctid 链出问题了?内存中的数据和底层的数据不一致?

后面仔细一想,发现是我想多了,这个其实是正常的机制,也是我最开始被绕进去的地方 👇🏻 做个小实验:

相信各位也看明白了,第一行数据依旧在哪里,但是通过 pageinspect 看的话,ctid 均是指向的第二行数据,即使第二行数据已经回滚了,通过 MVCC 和行上的 infomask(XMIN_INVALID) 来确保第二条数据不可见。所以,这个是正常的现象。这个不起眼的地方属实把我绕进去了,各种计算偏移量去看底层文件。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

既然如此,说明是 1847923208 这个事务做了更新,然后更新失败了。既然如此,根据此事务 ID 去检索一下日志,日志中是包含具体的事务 ID 的

果然,昨天业务 18:21 通过 DBeaver 进行了更新,提示了唯一约束冲突,事务回滚。但是可惜的是没有在日志中检索到两条数据插入的日志

那难道是索引的状态不对,没有起到 unique 的功能?不过可能性微乎其微,举个栗子

查询了一下系统表,这个索引的状态也是正确的。至此,貌似进入了死胡同不知道该从哪方面去分析。

但是可喜的是,发现这个表上有两个字段 date_created 和 date_updated ,分别是创建时间和更新时间,这就很方便了。

通过查看,发现这两条数据插入的时间是在 10-09 09:37 左右,检索一下该段时间的日志

原来这个时间点磁盘写爆了...数据库宕机了,发送了 signal 6!然后整个实例都重启了

后面经过了解,原来那个时间点有个失效的复制槽,WAL把磁盘打爆了。负责的 DBA 在 10:30 左右使用了 pg_resetxlog -x 1837553131 -o xxx -f $PGDATA,但是当恢复完成之后,日志一直在报奇奇怪怪的错误:could not access status of transaction 1837555746

另外在生产中我们打开了 track_commit_timestamp 参数,因此可以查看事务提交的时间

由于版本是 9.6.6 的,在 10 以后才提供 txid_status() 查看事务的状态,本来还想通过计算偏移量的方式来去查看 CLOG 事务的状态,可惜元组由于已经被冻结过(前面的 infomask 已经证实),CLOG 也被会被清理。

可以看到,数据是在 09:37 分插入的,但是事务提交是 10:44 的,中间差了整整一个小时,日志中大量 CLOG 相关的错误,完全无法知晓事务的状态

因此此时数据库的状态已经不知道变成一个什么样的状态了,根据前面的分析,应该是第二条数据本不该可见的,但是通过 pg_resetxlog 重置了之后,导致第二条数据由于 MVCC 的判断机制,属于未来删除的事务了,复现一下

可以看到我将第二行数据改为1,由于违反主键约束,事务报错回滚了。于是我使用pg_resetwal重置一下xid

可以看到,主键重复了。这也是这次案例的根因。怎么样,是不是发现了新大陆?pg_resetwal 慎用,除非你知道自己在做什么。

小结

从根因分析的话,依旧是那句话,在 PostgreSQL 中,磁盘写满然后数据库宕机之后,你能做的十分有限,无非删除一些日志文件或一些其他无关的文件,运气好可能你删除了一些大的不影响可用性的表/索引等。但是大多数时候都没啥用,并且删除对象十分危险,因此一个可行的方式是提前创建一个占位文件,比如 dd/fallocate 等,关键时刻删除 Maybe 可以救命。另外,pg_resetxlog 要慎用,比如我的复现过程,主键重复都能出现,我个人也是能不用就不用,很容易将数据库搞到一个不知所以然的状态。

参考

https://mp.weixin.qq.com/s/kLoqJIsozTp-Qz5H6bcxXw

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

5 × 4 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部