文档章节

MySQL Innodb表导致死锁日志情况分析与归纳

fdhay
 fdhay
发布于 2016/07/15 11:36
字数 3183
阅读 90
收藏 3
点赞 0
评论 0

案例描述
在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。
两个sql语句如下:
(1)insert into backup_table select * from source_table
(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'
teamUser表的表结构如下:
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
两语句对source_table表的使用情况如下:

死锁日志打印出的时间点表明,语句(1)运行过程中,当语句(2)开始运行时,发生了死锁。
当mysql检测出死锁时,除了查看mysql的日志,还可以通过show InnoDB STATUS \G语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱,所以,最好先使用pager less命令,通过文件内容浏览方式查看结果,会更清晰。(以nopager结束)
得到的死锁记录如下:


根据死锁记录的结果,可以看出确实是这两个语句发生了死锁,且锁冲突发生在主键索引上。那么,为什么两个sql语句会存在锁冲突呢?冲突为什么会在主键索引上呢?语句(2)得到了主键索引锁,为什么还会再次申请锁呢?
锁冲突分析
2.1 innodb的事务与行锁机制
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关,MyISAM不支持事务、采用的是表级锁,而InnoDB支持ACID事务、 行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句作为一个单独的事务来处理。
2.2 两语句加锁情况
在innodb默认的事务隔离级别下,普通的SELECT是不需要加行锁的,但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外,此案例中,语句(1)insert into teamUser_20110121 select * from teamUser会对表teamUser_20110121(ENGINE= MyISAM)加表锁,并对teamUser表所有行的主键索引(即聚簇索引)加共享锁。默认对其使用主键索引。
而语句(2)DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<'$daysago_1week'为删除操作,会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前缀索引,于是,还会对相关行的此非聚簇索引加排他锁。
2.3 锁冲突的产生
由于共享锁与排他锁是互斥的,当一方拥有了某行记录的排他锁后,另一方就不能其拥有共享锁,同样,一方拥有了其共享锁后,另一方也无法得到其排他锁。所 以,当语句(1)、(2)同时运行时,相当于两个事务会同时申请某相同记录行的锁资源,于是会产生锁冲突。由于两个事务都会申请主键索引,锁冲突只会发生 在主键索引上。
常常看到一句话:在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的。那就说明,单个SQL组成的事务锁是一次获得的。而此案例中,语句(2) 已经得到了主键索引的排他锁,为什么还会申请主键索引的排他锁呢?同理,语句(1)已经获得了主键索引的共享锁,为什么还会申请主键索引的共享锁呢?
死锁记录中,事务一等待锁的page no与事务二持有锁的page no相同,均为218436,这又代表什么呢?
我们的猜想是,innodb存储引擎中获得行锁是逐行获得的,并不是一次获得的。下面来证明。
死锁产生过程分析
要想知道innodb加锁的过程,唯一的方式就是运行mysql的debug版本,从gdb的输出中找到结果。根据gdb的结果得到,单个SQL组成的事 务,从宏观上来看,锁是在这个语句上一次获得的,但从底层实现上来看,是逐个记录行查询,得到符合条件的记录即对该行记录的索引加锁。
Gdb结果演示如下:

复制代码代码如下:


(gdb) b lock_rec_lock 
 Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070. 
 (gdb) c 
 Continuing. 
 [Switching to Thread 1168550240 (LWP 5540)] 
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1 “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070 
 2070 { 
 Current language: auto; currently c 
 (gdb) c 
 Continuing. 
 Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba “\200″, index=0x2aada730b8, thr=0x2aada74c18) at lock/lock0lock.c:2070 
 2070 { 
 (gdb) c 
 Continuing. 
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070 
 2070 { 
 (gdb) c 
 Continuing. 
 


(说明:”789\200″为非聚簇索引,”\200″为主键索引)

 

Gdb结果显示,语句(1)(2)加锁的获取记录为多行,即逐行获得锁,这样就解释了语句(2)获得了主键索引锁还再次申请主键索引锁的情况。
由于语句(1)使用了主键索引,而语句(2)使用了非聚簇索引,两个事务获得记录行的顺序不同,而加锁的过程是边查边加、逐行获得,于是,就会出现如下情况:

于是,两个事务分别拥有部分锁并等待被对方持有的锁,出现这种资源循环等待的情况,即死锁。此案例中被检测时候的锁冲突就发现在page no为218436和218103的锁上。
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚,此次事务(1)解锁并回滚,语句(2)继续运行直至事务结束。
innodb死锁形式归纳
死锁产生的四要素:互斥条件:一个资源每次只能被一个进程使用;请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;不剥夺条件:进程 已获得的资源,在末使用完之前,不能强行剥夺;循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
Innodb检测死锁有两种情况,一种是满足循环等待条件,还有另一种策略:锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度 时,innodb也会判断为死锁(这是提高性能方面的考虑,避免事务一次占用太多的资源)。这里,我们只考虑满足死锁四要素的情况。
死锁的形式是多样的,但分析到innodb加锁情况的最底层,因循环等待条件而产生的死锁只有可能是四种形式:两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。
以下首先介绍innodb聚簇索引与非聚簇索引的数据存储形式,再以事例的方式解释这四种死锁情况。
4.1聚簇索引与非聚簇索引介绍
聚簇索引即主键索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序,聚簇索引的索引页面指针指向数据页面。非聚簇索引(即第二主键索 引)不重新组织表中的数据,索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述,那么,聚簇索引的叶节点就是数据节点,而非聚簇 索引的叶节点仍然是索引节点,通常是一个指针指向对应的数据块。
而innodb在非聚簇索引叶子节点包含了主键值作为指针。(这样是为了减少在移动行或数据分页时索引的维护工作。)其结构图如下:

当使用非聚簇索引时,会根据得到的主键值遍历聚簇索引,得到相应的记录。
4.2四种死锁情况
在InnoDB中,使用行锁机制,于是,锁通常是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。
即将分享的四种死锁的锁冲突分别是:不同表的相同记录行索引锁冲突、主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级造成锁队列阻塞。
不同表的相同记录行锁冲突
案例:两个表、两行记录,交叉获得和申请互斥锁

条件:
A、 两事务分别操作两个表、相同表的同一行记录
B、 申请的锁互斥
C、 申请的顺序不一致

主键索引锁冲突
案例:本文案例,产生冲突在主键索引锁上
条件:
A、 两sql语句即两事务操作同一个表、使用不同索引
B、 申请的锁互斥
C、 操作多行记录
D、 查找到记录的顺序不一致

主键索引锁与非聚簇索引锁冲突
案例:同一行记录,两事务使用不同的索引进行更新操作

此案例涉及TSK_TASK表,该表相关字段及索引如下:
ID:主键;
MON_TIME:监测时间;
STATUS_ID:任务状态;
索引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

条件:
A、 两事务使用不同索引
B、 申请的锁互斥
C、 操作同一行记录

当执行update、delete操作时,会修改表中的数据信息。由于innodb存储引擎中索引的数据存储结构,会根据修改语句使用的索引以及修改信息 的不同执行不同的加锁顺序。当使用索引进行查找并修改记录时,会首先加使用的索引锁,然后,如果修改了主键信息,会加主键索引锁和所有非聚簇索引锁,修改 了非聚簇索引列值会加该种非聚簇索引锁。
此案例中,事务一使用非聚簇索引查找并修改主键值,事务二使用主键索引查找并修改主键值,加锁顺序不同,导致同时运行时产生资源循环等待。
锁升级造成锁队列阻塞
案例:同一行记录,事务内进行锁升级,与另一等待锁发送锁队列阻塞,导致死锁

条件:
A、 两事务操作同一行记录
B、 一事务对某一记录先申请共享锁,再升级为排他锁
C、 另一事务在过程中申请这一记录的排他锁

避免死锁的方法
InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险,这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说,建议的避免死锁的方法如下:
1.如果使用insert…select语句备份表格且数据量较大,在单独的时间点操作,避免与其他sql语句争夺资源,或使用select into outfile加上load data infile代替 insert…select,这样不仅快,而且不会要求锁定
2. 一个锁定记录集的事务,其操作结果集应尽量简短,以免一次占用太多资源,与其他事务处理的记录冲突。
3.更新或者删除表格数据,sql语句的where条件都是主键或都是索引,避免两种情况交叉,造成死锁。对于where子句较复杂的情况,将其单独通过sql得到后,再在更新语句中使用。
4. sql语句的嵌套表格不要太多,能拆分就拆分,避免占有资源同时等待资源,导致与其他事务冲突。
5. 对定点运行脚本的情况,避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断,如果事务意外结束,重新运行该事务,减少对功能的影响。

本文转载自:http://www.jb51.net/article/32651.htm

共有 人打赏支持
fdhay
粉丝 9
博文 66
码字总数 2796
作品 0
成都
高级程序员
减少InnoDB死锁发生的方法有什么?MySQL学习

  死锁的情况发生在不同的的事务相互之间拥有对方需要的锁,而导致相互一直无限等待。死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁,但事务对表的操作顺序不相同。   ...

mo默瑶 ⋅ 05/23 ⋅ 0

InnoDB死锁是什么情况?怎么规避?

  死锁的情况发生在不同的的事务相互之间拥有对方需要的锁,而导致相互一直无限等待。死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁,但事务对表的操作顺序不相同。   ...

zhouzhou2018 ⋅ 05/23 ⋅ 0

MySQL 在并发场景下的问题及解决思路

原文出处:李平 1、背景 对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安...

李平 ⋅ 05/11 ⋅ 0

MySQL基础(三)【MySQL事务与存储引擎】

3.1-数据库事务 什么是事务 一系列有序的数据库操作: 要么全部成功 要么全部回退到操作前的状态 中间状态对其他连接不可见 事务的基本操作: 基本操作 说明 start transaction 开始事务 co...

yanfeilai528 ⋅ 04/14 ⋅ 0

mysql服务性能优化—my.cnf配置说明详解

此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里,对各参数添加了中文说明 这配置已经优化的不错了,如果你的mysql没有什么特殊情况的话,可以直接使用该配置参数 MYSQL服...

xiaocao13140 ⋅ 05/21 ⋅ 0

mysql的InnoDB行锁的一点反思

刚参加工作时,遇到的mysql死锁的问题。具体情况如下: 网页有个统计访问次数功能,然后这个次数num是int类型,网页每次被访问一次就增1,然后运营在跑批量任务的时候,造成数据量这个字段死...

狐狈筱筱生 ⋅ 06/21 ⋅ 0

一次mysql无缘无故的挂掉,使用innodb_force_recovery修复

最近遇到了一个比较奇怪的问题,在大家都在睡午觉的时候,突然手机响了起来,我为了不吵醒其他人拿起了手机看了看监控信息,我去,居然是数据库down了,这是一台运行很久的数据库服务器,当我...

legehappy ⋅ 05/23 ⋅ 0

通过查看mysql 配置参数、状态来优化你的mysql

通过查看mysql 配置参数、状态来优化你的mysql mysql的监控方法大致分为两类: 1.连接到mysql数据库内部,使用show status,show variables,flush status 来查看mysql的各种性能指标。 直接...

优惠券活动 ⋅ 05/03 ⋅ 0

InnoDB 存储引擎的主要知识点介绍

本文转载自:Draveness,略有修改 原文链接:『浅入浅出』MySQL 和 InnoDB · 面向信仰编程 作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上...

踏雪无痕SS ⋅ 06/13 ⋅ 0

深入理解MySQL――锁、事务与并发控制 这才是正确的!

本文对锁、事务、并发控制做一个总结,看了网上很多文章,描述非常不准确。如有与您观点不一致,欢迎有理有据的拍砖! mysql服务器逻辑架构 每个连接都会在mysql服务端产生一个线程(内部通过...

java高级架构牛人 ⋅ 04/24 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

istio 文档

https://istio.io/docs/concepts/ https://istio.io/docs/concepts/traffic-management/handling-failures/ https://istio.io/docs/concepts/traffic-management/rules-configuration/......

xiaomin0322 ⋅ 19分钟前 ⋅ 0

编程语言的作用及与操作系统和硬件的关系

一、编程语言的作用及与操作系统和硬件的关系 作用:编程语言是计算机语言,是一种程序员与计算机之间沟通的介质,通过编程语言可以使得计算机能够根据人的指令一步一步去工作,完成某种特定...

slagga ⋅ 30分钟前 ⋅ 0

runtime实现按钮点击事件

也不能说是实现吧,,,就是有点类似于RAC里边的写法,不用给btn添加另外的点击事件,就那个add...select...这样子很不友好,来看下代码: [self.btn handleControlEvent:UIControlEventTou...

RainOrz ⋅ 30分钟前 ⋅ 0

Windows系统运维转linux系统运维的经历

开篇之前,首先介绍一下我的背景把:我是一个三线城市的甲方运维。最近,在《Linux就该这么学》书籍的影响下和朋友小A(Linux运维已经三年了,工资也比我的高很多)的影响下,决定转行。最近...

linux-tao ⋅ 31分钟前 ⋅ 0

zip压缩工具,tar打包工具

zip压缩工具 zip打包工具跟前面说到的gzip,bz2,xz 工具最大的不一样是zip可以压缩目录。如果没有安装,需要使用yum install -y zip 来安装。安装完之后就可以直接使用了,跟之前提到的压缩...

李超小牛子 ⋅ 38分钟前 ⋅ 0

使用npm发布自己的npm组件包

一、注册npm账号 官网:https://www.npmjs.com/signup 注册之后需要进行邮箱验证,否则后面进行组件包发布时候会提示403错误,让进行邮箱核准。 二、本地新建一个文件夹,cd进入后使用npm i...

灰白发 ⋅ 40分钟前 ⋅ 0

010. 深入JVM学习—垃圾收集策略概览

1. 新生代可用GC策略 1. 串行GC(Serial Copying) 算法:复制(Copying)清理算法; 操作步骤: 扫描年轻代中所有存活的对象; 使用Minor GC进行垃圾回收,同时将存活对象保存到“S0”或“S...

影狼 ⋅ 41分钟前 ⋅ 0

JVM性能调优实践——JVM篇

在遇到实际性能问题时,除了关注系统性能指标。还要结合应用程序的系统的日志、堆栈信息、GClog、threaddump等数据进行问题分析和定位。关于性能指标分析可以参考前一篇JVM性能调优实践——性...

Java小铺 ⋅ 42分钟前 ⋅ 0

误关了gitlab sign-in 功能的恢复记录

本想关sign-up的,误点了sign-in 退出后登录界面提示: No authentication methods configured 一脸懵逼.. 百度后众多方案说修改application_settings 的 signin_enabled字段; 实际上新版本字段...

铂金蛋蛋 ⋅ 42分钟前 ⋅ 0

登录后,后续请求接口没有带登录cookie可能原因

1.XMLHttpRequest.withCredentials没设置好,参考https://developer.mozilla.org/zh-CN/docs/Web/API/XMLHttpRequest/withCredentials...

LM_Mike ⋅ 43分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部