专栏首页艾小仙阿里二面:怎么解决MySQL死锁问题的?

阿里二面:怎么解决MySQL死锁问题的?

咱们使用 MySQL 大概率上都会遇到死锁问题,这实在是个令人非常头痛的问题。本文将会对死锁进行相应介绍,对常见的死锁案例进行相关分析与探讨,以及如何去尽可能避免死锁给出一些建议。

话不多说,开整!

什么是死锁

死锁是并发系统中常见的问题,同样也会出现在数据库MySQL的并发读写请求场景中。当两个及以上的事务,双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源,就会出现“死锁”。常见的报错信息为 Deadlock found when trying to get lock...

举例来说 A 事务持有 X1 锁 ,申请 X2 锁,B事务持有 X2 锁,申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待,就造成了死锁。

如上图,是右侧的四辆汽车资源请求产生了回路现象,即死循环,导致了死锁。

从死锁的定义来看,MySQL 出现死锁的几个要素为:

  1. 两个或者两个以上事务
  2. 每个事务都已经持有锁并且申请新的锁
  3. 锁资源同时只能被同一个事务持有或者不兼容
  4. 事务之间因为持有锁和申请锁导致彼此循环等待

InnoDB 锁类型

为了分析死锁,我们有必要对 InnoDB 的锁类型有一个了解。

MySQL InnoDB 引擎实现了标准的行级别锁:共享锁( S lock ) 和排他锁 ( X lock )

  1. 不同事务可以同时对同一行记录加 S 锁。
  2. 如果一个事务对某一行记录加 X 锁,其他事务就不能加 S 锁或者 X 锁,从而导致锁等待。

如果事务 T1 持有行 r 的 S 锁,那么另一个事务 T2 请求 r 的锁时,会做如下处理:

  1. T2 请求 S 锁立即被允许,结果 T1 T2 都持有 r 行的 S 锁
  2. T2 请求 X 锁不能被立即允许

如果 T1 持有 r 的 X 锁,那么 T2 请求 r 的 X、S 锁都不能被立即允许,T2 必须等待 T1 释放 X 锁才可以,因为 X 锁与任何的锁都不兼容。共享锁和排他锁的兼容性如下所示:

间隙锁( gap lock )

间隙锁锁住一个间隙以防止插入。假设索引列有2, 4, 8 三个值,如果对 4 加锁,那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是,间隙锁有个例外:

  1. 如果索引列是唯一索引,那么只会锁住这条记录(只加行锁),而不会锁住间隙。
  2. 对于联合索引且是唯一索引,如果 where 条件只包括联合索引的一部分,那么依然会加间隙锁。

next-key lock

next-key lock 实际上就是 行锁+这条记录前面的 gap lock 的组合。假设有索引值10,11,13和 20,那么可能的 next-key lock 包括:

(负无穷,10],(10,11],(11,13],(13,20],(20,正无穷)

在 RR 隔离级别下,InnoDB 使用 next-key lock 主要是防止幻读问题产生。

意向锁( Intention lock )

InnoDB 为了支持多粒度的加锁,允许行锁和表锁同时存在。为了支持在不同粒度上的加锁操作,InnoDB 支持了额外的一种锁方式,称之为意向锁( Intention Lock )。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度上进行加锁。意向锁分为两种:

  1. 意向共享锁( IS ):事务有意向对表中的某些行加共享锁
  2. 意向排他锁( IX ):事务有意向对表中的某些行加排他锁

由于 InnoDB 存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫描以外的任何请求。表级意向锁与行级锁的兼容性如下所示:

插入意向锁( Insert Intention lock )

插入意向锁是在插入一行记录操作之前设置的一种间隙锁,这个锁释放了一种插入方式的信号,即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。假设某列有索引值2,6,只要两个事务插入位置不同(如事务 A 插入3,事务 B 插入4),那么就可以同时插入。

锁模式兼容矩阵

横向是已持有锁,纵向是正在请求的锁:

阅读死锁日志

在进行具体案例分析之前,咱们先了解下如何去读懂死锁日志,尽可能地使用死锁日志里面的信息来帮助我们来解决死锁问题。

后面测试用例的数据库场景如下:MySQL 5.7 事务隔离级别为 RR

表结构和数据如下:

测试用例如下:

通过执行show engine innodb status 可以查看到最近一次死锁的日志。

日志分析如下:

  1. ***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

事务号为2322,活跃 6秒,starting index read 表示事务状态为根据索引读取数据。常见的其他状态有:

mysql tables in use 1 说明当前的事务使用一个表。

locked 1 表示表上有一个表锁,对于 DML 语句为 LOCK_IX

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

LOCK WAIT 表示正在等待锁,2 lock struct(s) 表示 trx->trx_locks 锁链表的长度为2,每个链表节点代表该事务持有的一个锁结构,包括表锁,记录锁以及自增锁等。本用例中 2locks 表示 IX 锁和lock_mode X (Next-key lock)

1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

MySQL thread id 37 表示执行该事务的线程 ID 为 37 (即 show processlist; 展示的 ID )

delete from student where stuno=5 表示事务1正在执行的 sql,比较难受的事情是 show engine innodb status 是查看不到完整的 sql 的,通常显示当前正在等待锁的 sql。

***** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

RECORD LOCKS 表示记录锁, 此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁,本案例中其实是 Next-Key Lock 。

事务2的 log 和上面分析类似:

  1. ***** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

显示事务 2 的 insert into student(stuno,score) values(2,10) 持有了 a=5 的 Lock mode X

| LOCK_gap,不过我们从日志里面看不到事务2执行的 delete from student where stuno=5;

这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。

  1. ***** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting

表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap )

经典案例分析

案例一:事务并发 insert 唯一键冲突

表结构和数据如下所示:

测试用例如下:

日志分析如下:

  1. 事务 T2 insert into t7(id,a) values (26,10) 语句 insert 成功,持有 a=10 的 排他行锁( Xlocks rec but no gap )
  2. 事务 T1 insert into t7(id,a) values (30,10), 因为T2的第一条 insert 已经插入 a=10 的记录,事务 T1 insert a=10 则发生唯一键冲突,需要申请对冲突的唯一索引加上S Next-key Lock( 即 lock mode S waiting ) 这是一个间隙锁会申请锁住(,10],(10,20]之间的 gap 区域。
  3. 事务 T2 insert into t7(id,a) values (40,9)该语句插入的 a=9 的值在事务 T1 申请的 gap 锁4-10之间, 故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting 。

案例一:先 update 再 insert 的并发死锁问题

表结构如下,无数据:

测试用例如下:

死锁分析: 可以看到两个事务 update 不存在的记录,先后获得间隙锁( gap 锁),gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁,然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候,当前会话申请不了插入意向锁,导致死锁。

如何尽可能避免死锁

  1. 合理的设计索引,区分度高的列放到组合索引前面,使业务 SQL 尽可能通过索引定位更少的行,减少锁竞争
  2. 调整业务逻辑 SQL 执行顺序, 避免 update/delete 长时间持有锁的 SQL 在事务前面。
  3. 避免大事务,尽量将大事务拆成多个小事务来处理,小事务发生锁冲突的几率也更小。
  4. 固定的顺序访问表和行。比如两个更新数据的事务,事务 A 更新数据的顺序为 1,2;事务 B 更新数据的顺序为 2,1。这样更可能会造成死锁。
  5. 在并发比较高的系统中,不要显式加锁,特别是是在事务里显式加锁。如 select … for update 语句,如果是在事务里(运行了 start transaction 或设置了autocommit 等于0),那么就会锁定所查找到的记录。
  6. 尽量按主键/索引去查找记录,范围查找增加了锁冲突的可能性,也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句,由于类似这样的语句用不到索引,因此将导致整个表的数据都被锁住。
  7. 优化 SQL 和表设计,减少同时占用太多资源的情况。比如说,减少连接的表,将复杂 SQL 分解为多个简单的 SQL。

好了。今天就说到这了,我还会不断分享自己的所学所想,希望我们一起走在成功的道路上!

本文分享自微信公众号 - 艾小仙(aixiaoxianren)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-06-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

推荐阅读

  • 忽视的12分钟,破防!

    现象:某大客户集群部分服务器出站流量飙高,集群cpu整体水平在持续上涨,客户业务出现崩溃。

    万海旭
    云镜(主机安全)主机安全(云镜)欺骗防御与威胁感知系统官方文档云+社区
  • TKE集群pod内无法访问集群外cvm自建容器

    使用tke产品的客户,经常会遇到GlobalRouter网络模式下tke集群内的pod无法访问集群外cvm自建docker容器服务,cvm节点也无法集群内pod服务,vpc-cni网络模式下集群是没有这个问题的。

    聂伟星
  • 【中秋迎新,好礼不停】腾云先锋反馈交流群纳新送福利啦!

    腾云先锋团队
  • 使用acme.sh申请Let's Encrypt免费的SSL证书

    acme.sh 实现了 acme 协议,可以从letsencrypt生成免费的证书。接下来将为大家介绍怎样申请Let's Encrypt通配符证书。

    青阳
    SSL 证书
  • 基于GPT2制作一个chatbot

    想必大家都有经历,处于多种原因有个很好的朋友不再跟你聊天了,那么可不可以用他的微信聊天记录来大致还原一下这个人的聊天习惯语气甚至是喜欢发的表情包等等呢?

    大鹅
    机器学习智能钛机器学习自然语言处理自然语言处理NLP自然语言
  • Webify实战:快速、低成本部署个人Hexo静态博客

    Hexo是一款简洁且高效的博客框架,我一般称呼为Hexo博客引擎。使用Hexo,可以快速根据自己本地的博客配置和文章内容自动创建为纯静态的博客网站。对比Wordpress这样的动态博客框架,有以下特点:

    Mintimate
    云开发CloudBase云开发静态网站托管HexoCODING DevOps网站建设
  • 使用Coding Devops+自动构建以及部署博客

    本文只是我使用Coding Devops+部署博客以及自动Push到GitHub的过程记录,我只用到了repo和ci功能,不需要一模一样,hexo等可以效仿,其他仅作参考,有问题评论留言即可。(看懂本文需要会一点Git,Linux服务器相关内容)

    jcjyxjs
    CODING DevOpsDevOps
  • GPT/GPT2/DialoGPT 详解对比与应用-文本生成与对话

    GPT 的全名:Generative Pre-Training,其论文标题是 Improving Language Understanding by Generative Pre-Training。

    大鹅
    自然语言处理NLP机器学习深度学习AI 人工智能TensorFlow
  • GME重磅上线未成年人声音识别功能

    作为游戏行业从业者,应严格落实未成年人沉迷网络游戏有关措施,形成落实防沉迷要求的社会合力,共同为未成年人健康成长营造良好环境。

    游戏多媒体引擎GME
    游戏多媒体引擎 GME游戏解决方案
  • 浅谈 K8s Informer

    进入 K8s 的世界,会发现有很多的 Controller,它们都是为了完成某类资源(如 pod 是通过 DeploymentController, ReplicaSetController 进行管理)的调谐,目标是保持用户期望的状态。

    astraw99
    Kubernetes云计算云+社区

扫码关注云+社区

领取腾讯云代金券