【MySQL】如何阅读死锁日志-阿里云开发者社区

开发者社区> yangyi_dba> 正文

【MySQL】如何阅读死锁日志

简介: 一 前言    工欲善其事必先利其器,前面分析了很多死锁案例,并没有详细的介绍如何通过死锁日志来诊断死锁的成因。本文将介绍如何读懂死锁日志,尽可能的获取信息来辅助我们解决死锁问题。
+关注继续查看
一 前言
   
工欲善其事必先利其器,前面分析了很多死锁案例,并没有详细的介绍如何通过死锁日志来诊断死锁的成因。本文将介绍如何读懂死锁日志,尽可能的获取信息来辅助我们解决死锁问题。
二 日志分析
2.1 场景 
为了更好的学习死锁日志,我们需要提前了解死锁场景
MySQL 5.6 事务隔离级别为RR
  1. CREATE TABLE `ty` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `a` int(11) DEFAULT NULL,
  4.   `b` int(11) DEFAULT NULL,
  5.   PRIMARY KEY (`id`),
  6.   KEY `idxa` (`a`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
  8. insert into ty(a,b) values(2,3),(5,4),(6,7);
2.2 测试用例

T2

T1

begin;


delete from  ty where  a=5;

begin;


delete from  ty where  a=5;

insert into ty(a,b) values(2,10);



delete from  ty where  a=5;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

                                                            
2.3 我们通过show engine innodb status 查看的日志是最新一次记录死锁的日志。
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-09 22:34:13 7f78eab82700
  5. *** (1) TRANSACTION: #事务1
  6. TRANSACTION 462308399, ACTIVE 33 sec starting index read
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
  9. MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
  10. delete from ty where a=5
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
  15. mysql tables in use 1, locked 1
  16. 5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
  17. MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
  18. insert into ty(a,b) values(2,10)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (1)
2.4 日志分析
*** (1) TRANSACTION: #事务1
TRANSACTION 462308399, ACTIVE 33 sec starting index read 
事务编号为 462308399 ,活跃33秒,starting index read 表示事务状态为根据索引读取数据。常见的其他状态:
  1. fetching rows 表示事务状态在row_search_for_mysql中被设置,表示正在查找记录。
  2. updating or deleting 表示事务已经真正进入了Update/delete的函数逻辑(row_update_for_mysql)
  3. thread declared inside InnoDB 说明事务已经进入innodb层。通常而言 不在innodb层的事务大部分是会被回滚的。
mysql tables in use 1, 说明当前的事务使用一个表。locked 1 表示表上有一个表锁,对于DML语句为LOCK_IX
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK WAIT表示正在等待锁, 2 lock struct(s) 表示trx->trx_locks锁链表的长度为2,每个链表节点代表该事务持有的一个锁结构,包括表锁,记录锁以及auto_inc锁等。本案例中2locks 表示IX锁和lock_mode X(Next-key lock)
heap size 360 表示事务分配的锁堆内存大小,一般没有什么具体的用处。
1 row lock(s)表示当前事务持有的行记录锁/gap 锁的个数。
delete from ty where a=5 表示事务1在执行的sql ,不过比较悲伤的事情是show engine innodb status 是查看不到完整的事务的sql 的,通常显示当前正在等待锁的sql。
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
RECORD LOCKS 表示记录锁,space id为219,page号4 ,n bits 72表示这个聚集索引记录锁结构上留有72个Bit位
表示事务1 正在等待表 ty 上的 idxa 的 X 锁本案例中其实是Next-Key lock
事务2的log 和上面分析类似,
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
显示了事务2 insert into ty(a,b) values(2,10)持有了a=5 的Lock mode X |LOCK_GAP ,不过我们从日志里面看不到 事务2 执行的 delete from  ty where  a=5;这点也是造成DBA 仅仅根据日志难以分析死锁的问题的根本原因。
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 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 )
这里需要各位注意的是锁组合,类似lock_mode X waiting ,lock_mode X,lock_mode X locks gap before rec insert intention waiting 是我们分析死锁的核心重点。如何理解锁组合呢?
首先我们要知道对于MySQL有两种常规锁模式
LOCK_S(读锁,共享锁)
LOCK_X(写锁,排它锁)
最容易理解的锁模式,读加共享锁,写加排它锁.
有如下几种锁的属性
LOCK_REC_NOT_GAP        (锁记录)
LOCK_GAP                       (锁记录前的GAP)
LOCK_ORDINARY              (同时锁记录+记录前的GAP 。传说中的Next Key锁)
LOCK_INSERT_INTENTION(插入意向锁,其实是特殊的GAP锁)
锁的属性可以与锁模式任意组合。例如.
lock->type_mode 可以是Lock_X 或者Lock_S 
locks gap before rec  表示为gap锁:lock->type_mode & LOCK_GAP
locks rec but not gap 表示为记录锁,非gap锁:lock->type_mode & LOCK_REC_NOT_GAP
insert intention          表示为插入意向锁:lock->type_mode & LOCK_INSERT_INTENTION
waiting                       表示锁等待:lock->type_mode & LOCK_WAIT
三 小结
  本文算是简单的死锁分析入门,能够提供部分死锁分析的所需要的技术知识。死锁分析确是一门技术活儿,想要透彻的分析死锁的成因,我们必须要了解造成死锁的业务逻辑sql 的执行场景MySQL的锁机制各种锁之间的兼容性,必要时还需要透彻的理解源码。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用NAT网关轻松为单台云服务器设置多个公网IP
在应用中,有时会遇到用户询问如何使单台云服务器具备多个公网IP的问题。 具体如何操作呢,有了NAT网关这个也不是难题。
26746 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9569 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
9074 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13266 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
4078 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
21987 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
6980 0
+关注
yangyi_dba
数据库相关技术专家
972
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载