Ruby PostgreSQL 数据库删除外键约束引发的死锁问题

martin91 · 2024年12月13日 · 最后由 martin91 回复于 2024年12月16日 · 437 次阅读
本帖已被管理员设置为精华贴

上周将一个大功能发布到了线上环境,但是在半个多小时后收到排障要求(我们使用的是 PostgreSQL 数据库),因为 SRE 发现我们的其中一个从库的 CPU 使用率接近 100% 的告警,同时 APM 监控表明部分 API 响应时间明显变长,并且相关 API 的错误追踪里会看到 Statement Timeout 错误。经过版本回滚后,数据库和服务恢复正常。后面是漫长的根因分析的过程,而最后我们确定的原因,竟是一个 Rails migration 触发(注意不是导致,因为死锁还得找到和它形成相互等待的另一方,双方同时运行才能导致死锁)的从库死锁,比较有意思,总结一下,跟各位分享。

为什么从库也有死锁?

按照过去仅有的认知,我只知道死锁在主库中更为常见,因为死锁通常发生在两个冲突的事务之间,而一般只有主库才会有大量事务同时运行,而从库由于只需要同步主库的日志进行顺序回放即可,理论上不存在并发的事务。

但是我们的死锁发生在了从库,这是一个让人感觉有点匪夷所思的问题。

根因:DDL 与高并发的 SELECT ... FROM A JOIN B ... 查询形成死锁

经过死锁日志的分析,我们发现每次死锁出现时,都有类似以下模式的联表查询语句在等待同一个线程锁定的资源:

SELECT users.id, users.name, cars.brand, ... FROM users LEFT OUTER JOIN cars ON cars.owner_id = users.id WHERE ...

(以上 SQL 中的表名和列名为示例,非真实表名列名)

完整的日志类似:

[12345]:ERROR:  deadlock detected at character 234
Process 12345 waits for AccessShareLock on relation 2 of database 789; blocked by process 911.
    Process 911 waits for AccessExclusiveLock on relation 1 of database 789; blocked by process 12345.
    Process 12345: SELECT users.id, users.name, cars.brand, ... FROM users LEFT OUTER JOIN cars ON cars.owner_id = users.id WHERE ...
    Process 911: <backend information not available>

通过查询 PostgreSQL 数据库的元数据表,还原出 relation=1 对应表名是 users,而 relation=2 对应表名是 cars。 所以:

  1. 这里的 SELECT 联表查询,正在等待 carsAccessShareLock
  2. 而线程 911 在等待 usersAccessExclusiveLock 锁。

通过 PostgreSQL 官方文档,了解到这两个锁的求锁场景以及互斥性:

  1. AccessShareLock: 仅在使用 SELECT 查询表时,对表进行加锁,并且 *只和 AccessExclusiveLock * 互斥;
  2. AccessExclusiveLock: 和其它所有模式的锁互斥,这个锁保证同一时间只有持有这个锁的事务是唯一可访问目标表的事务。DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULLREFRESH MATERIALIZED VIEW(不使用 CONCURRENTLY 前提下)以及大多数的 ALTER INDEXALTER TABLE 操作都会尝试获取这个锁。

从以上获取到的信息来看,那么死锁的形成,很有可能是有一个包含 DDL 的事务,并且整个事务里先后对 carsusers 加上了 AccessExclusiveLock 锁。

于是,我开始回顾这次版本里所有的数据库改动,也就是我们的 db/migrations/ 里的新增的文件,但是一开始,我没有发现任何问题,因为所有的 migration,看起来都是只修改了一张表。直到我冷静下来思考:如果显式的 DDL 没有相关的,但是 DDL 又确实存在,那么一定是某个我们不熟悉的潜在代码引发了问题,进一步扩大了 review 的范围,我尝试找出 migration 中同时涉及这两个表的表名或者列名的,直到发现一个可疑对象:

# db/migrate/20241212010203_add_repaired_at_to_and_remove_fk_constraint_from_cars.rb
class AddReparedAtToAndRemoveFkConstraintFromCars < ActiveRecord::Migration[8.0]
  def change
    add_column :cars, :repaired_at, :datetime
    remove_foreign_key :remove_foreign_key, :cars, :owner_id
  end
end

难道是这个 remove_foreign_key 会导致在 users 表上加锁?尝试 Google 搜索关键词“Pg remove foreign key constraint lock on table”,找到一篇讨论,标题是“Thread: Full table lock dropping a foreign key”,里边提到帖子作者通过 pg_locks 观察到当 pg 在执行外键约束删除时,会对被引用的表加上 AccessExclusiveLock 锁,底层原因是 PostgreSQL 数据库内部会在此过程中移除响应的 trigger,但是由于对数据库底层我不精通,到此打住不深究,但是已经足以我找出此次死锁的根因了。

所以,从始至终,包括我自己在内的开发人员,都没有意识到原来删除外键约束,会对外键引用的表进行加锁操作,这是我们写出这个风险代码,并且没有在死锁出现后第一时间怀疑到这里的原因,甚至我们一大帮人在分析根因的时候,虽然共享屏幕上正对着这行代码,但是我们都以为这行代码对于死锁原因分析无关紧要。

根因定位过程的一些有趣思考

区分症结和现象

我们一开始引入 DBA 同事参与排查,DBA 同事将原因归为高并发,原因是当时并没有立马意识到死锁的存在以及监控显示数据库连接数在问题期间翻了一番。事实上,如我们一位 SRE 同事所怀疑的一致,连接数高不是因,而是果,是因为查询请求时延过高,导致连接池连接复用率下降,业务必须建立更多的数据库连接来完成数据查询。

事后冷静思考,我才想起去看一下当时其他的指标,如果是业务量临时变大导致,那请求 QPS 以及数据库 IO 应该明显变高,但是事实上是当时的 API QPS 和数据库 IO 并没有明显变化。如果当时把这些观察纳入推理,那我们会更快排除这个错误的原因。

所以,分析问题,得先确定问题是什么,避免把问题导致的连锁反应当成问题本身。

为什么主库没有出现死锁?

因为我们之前做了主从读写分离,主库的查询请求非常少,从概率的角度来看,这降低了这次死锁在主库出现的概率,但也不是说主库一定高枕无忧。

为什么另一个从库没有问题?

我们有 2 个从库实例,事实上另一个从库也不是独善其身,事后分析日志,仍然发现另一个从库上出现了 8 次死锁日志,说明这个从库在重放的过程中,还是遇到了 8 次死锁,只不过第 9 次重试时,它很幸运,抢先一步完成所有 DDL 并提交了事务,之后正常提供查询服务。

而那个有问题的从库,则是很不幸地一直抢不到所有的锁,数据库一直重试,一直死锁,这点从事后回顾从库的复制的 lag 监控也能看出来,问题期间,数据库从库复制的 lag 持续拉大,表明数据库当时卡在了事务重放了,但是我们当时没有很快去排查这个监控,也是一个教训。

为什么请求不是全部失败?

PostgreSQL 数据库默认的死锁检测等待时间是 1s,之后启动死锁检测并且回滚导致死锁的事务。所以大量请求更多的影响是时延变高(等待 1 秒后拿到了共享锁,完成查询),并不会失败。但是也会有一些线程的查询很不幸,虽然 DDL 事务已经回滚,但是回滚同时这个线程可能在休眠中,等到线程调度进来的时候,新的 DDL 事务又已经开启并且进入等待了,所以这些不幸的线程再次等待。从事后监控看,问题期间,整体的 API 时延升高,P99 到达惊人的 1 分钟,说明极少数请求很不幸地一直在等待锁,直到 STATEMENT 超时(默认 60 秒)。

最佳实践建议

  1. 慎重使用数据库层面的 constraint 恰当使用数据库 constraint 可以简化应用层逻辑,保证数据完整性,但是对其不熟悉的前提下使用,容易踩坑。理想状况下,数据库只做数据存取这一件事,其他的事情交给应用层。
  2. 避免多个表在同一个 DDL 事务里 需要特别警醒的是,Rails migration 会将每个 migration 包装成一个事务,为了防止死锁,一个事务,也就是一个 migration,只能操作一个表,如果你不确定,可以将操作分散到多个 migration 里,或者干脆关闭这个 migration 的事务,即调用 disable_ddl_transaction!

本文首发于个人博客:https://blog.hackerpie.com/posts/postgresql/deadlock-due-to-remove-constrait/

突然想到,我们找解决方案一般都用错误日志/症状搜索。这次的错误日志 (lock error) 和症状(100%cpu、high timeout:spinlock ) 挺独特的,从这个角度写文章,或许也可以帮到网友 😄

btw 我们用回滚来恢复服务的方式也很有意思,虽然 db 在回滚代码后就恢复了,但是真正起作用的不是代码回滚了,而是滚动部署时,旧 db connection 被断开了,释放了锁。

阴差阳错,其实重启也能起到断开 db conn 的作用。重启和回滚,两大恢复服务的利器,哈哈😄

学习了,感谢大佬分享!

线上问题排查不容易!

仔细想了一下,确实除了自己的项目,在工作中从来没用过外键约束,数据库层面最多用一个 uniq 索引,甚至 null false 都很少用,很久之前还觉得这样不优雅,现在看确实能避免很多不好发现的问题😂 当然另外一个原因是微服务都拆成零碎了,业务最核心的四五张表分别在三个部门的三个数据库里,根本没办法 join

willx 回复

我之前所在的公司,倒是都有明确禁止使用数据库层面的约束,除了 uniq 和 null。然后也是禁止使用 join 查询。

Rei 将本帖设为了精华贴。 12月15日 15:34

学习了 写的挺好

martin91 回复

我不是很能理解,禁止使用 join, 需要用关联查询的时候怎么做呢,通过冗余字段,或者多次查询来解决吗

lyb124553153 回复

如果你的业务数据量非常大的时候 要谨慎使用 join 你可以认为这是一个地雷,不是不能用 要慎用 a.joins(:b).where(c: ['1', '2']) 所有的数据都会做关联 这时候可以改为: ids = a.where(c: ['1', '2']) b.where(a_id: ids )

总结::

  1. 死锁的背景:作者在一个 Rails 项目中部署了新功能后,发现数据库的一个从库 CPU 使用率接近 100%,API 响应时间变长,并出现了 Statement Timeout 错误。版本回滚后,问题得到解决。

  2. 死锁的原因:经过分析,发现死锁是由于一个 Rails migration 触发的,这个 migration 包含了对cars表添加repaired_at列和删除owner_id外键约束的操作。这个操作在从库上导致了死锁。

  3. 死锁的模式:死锁发生时,有联表查询语句在等待cars表的AccessShareLock,而另一个进程在等待users表的AccessExclusiveLock。这两个锁是互斥的,导致了死锁。

  4. 锁的类型

    • AccessShareLock:在 SELECT 查询表时使用,仅与AccessExclusiveLock互斥。
    • AccessExclusiveLock:与其他所有锁模式互斥,用于 DDL 操作,如DROP TABLETRUNCATE等。
  5. DDL 操作的影响:在执行删除外键约束的 DDL 操作时,PostgreSQL 会对被引用的表(在这个案例中是users表)加上AccessExclusiveLock锁,这可能导致与其他查询操作的锁冲突。

  6. 问题的具体代码:问题是由以下 Rails migration 代码引起的:

    class AddReparedAtToAndRemoveFkConstraintFromCars < ActiveRecord::Migration[8.0]
     def change
       add_column :cars, :repaired_at, :datetime
       remove_foreign_key :cars, :owner_id
     end
    end
    

    这段代码中的remove_foreign_key操作导致了对users表的锁,从而引发了死锁。

  7. 其他思考

    • 区分症结和现象:确认问题本身是什么,避免将问题的结果误认为是原因。
    • 主库为何没有死锁:主库查询请求少,降低了死锁概率。
    • 另一个从库为何没有问题:另一个从库虽然也遇到了死锁,但最终成功提交了事务。
    • 请求为何不是全部失败:PostgreSQL 的死锁检测机制会在 1 秒后回滚导致死锁的事务,所以大多数请求只是延迟,而不是失败。

好文,是个很新奇的角度的问题。值得深入思考。

@528070506 @lyb124553153 是的,就是相当于在应用逻辑层手动做这种多次查询。 我理解有的公司直接一刀切不让用的出发点是,人去做判断会有主观性,而联表查询的性能开销也不一定仅限于表数据规模,所以用简单粗暴的规定,避免出现崩溃性的 bug。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号