一个MySQL死锁的问题分析

两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。一般不需要人为介入,这种方法简单有效,在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。
表结构

create table `t` (`ID` int(11) not null AUTO_INCREMENT,`NAME` varchar(100) DEFAULT NULL,`AGE` int(11) DEFAULT NULL,`email` varchar(256) DEFAULT NULL,PRIMARY KEY (`ID`),KEY `ind_name` (`NAME`),KEY `ind_email` (`email`)) ENGINE=InnoDB;

数据

Insert into t(name,age,email) values('aaa',20,'aaa@mail.com');Insert into t(name,age,email) values('bbb',20,'bbb@mail.com');Insert into t(name,age,email) values('ccc',20,'ccc@mail.com');

两个并发sql

线程1: update t set age=30 where name='aaa';

线程2: delete t where email='bbb@mail.com';

死锁case

线程1等ind_name,线程2等Primary

Question

1.update/delete操作的流程?

答:update/delete操作,在数据库中,会被拆分为两步。第一步是当前读,读取满足条件的记录,并加锁;第二步真正的进行update/delete,根据读取到的记录,进行相应的更新或者是删除。

注意:

一般情况下,读取与更新/删除是交替进行的,先读取满足条件的一条记录,加锁,更新这条记录,然后再读取下一条满足条件的记录,加锁,并更新,直至读取到第一条不满足条件的记录为止。

2. update操作会加那些锁?

答:针对以上的update操作,采用的是读取一条,更新一条的处理流程。读取走的是ind_name索引,更新记录需要锁住记录。因此更新一条记录的加锁流程为:Ind_name索引加数据锁—>Primary索引加数据锁,若是RR隔离级别,ind_name索引记录前还需要加Gap锁(但Gap锁在此处不是造成死锁的原因)。

3. delete操作会加哪些锁?

答:针对以上的delete操作,scan(扫描)走的是ind_email索引。scan过程需要加上ind_email索引上的数据锁(RR隔离级别下需要加Gap锁),Primary索引上的数据锁;delete过程,还需要操作ind_name索引,因此还需要加上ind_name索引上的数据锁。

Ind_email索引加数据锁—>Primary索引加数据锁—>ind_name索引加数据锁

4. 死锁case产生的原因是什么?

答:分析2,3可以发现update操作的加锁顺序为ind_name—>primary,delete操作的加锁顺序为ind_email—>primary—>ind_name,加锁的顺序正好相反,在并发执行下,极有可能产生死锁。

5. 以上两条并发sql,会有多少种死锁可能性?

答:除了示例中的死锁外,以上update/delete并发操作,还有可能产生primary索引上的死锁:因为通过两个索引访问主键的顺序是不一致的,对于两条记录,如果恰好以相反的顺序范围primary索引,即可能产生主键上的死锁。

6. 如何尽可能避免死锁?

  • 以固定的顺序访问表和行。简单方法是对id列表先排序,后执行,这样就避免了交叉等待锁的情形
  • 大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。
  • 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
  • 降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
  • 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。

关于INNODB LOCK前两篇文章有介绍

MySQL InnoDB Lock(一)

MySQL InnoDB Lock(二)

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2016-10-01

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Google Dart

Dart语言指南(二) 顶

Dart是一种面向对象的语言 包含类和基于 mixin 的继承两部分。每个对象是一个类的实例, 并且 Object.是所有类的父类。 基于 mixin 的继承指...

361
来自专栏软件开发

JavaSE学习总结(四)——Java面向对象十分钟入门

面向对象编程(Object Oriented Programming,OOP)是一种计算机模拟人类的自然思维方式的编程架构技术,解决了传统结构化开发方法中客观...

2327
来自专栏一名合格java开发的自我修养

springMVC参数绑定

处理器形参中添加如下类型的参数处理注解适配器会默认识别并进行赋值。 1 HttpServletRequest 通过request对象获取请求信息 2 Htt...

612
来自专栏java一日一条

java面试小题系列(一)

643
来自专栏Java帮帮-微信公众号-技术文章全总结

Java基础-07(02).总结private,this,封装,static,成员方法变量,局部变量匿名对象

9:面向对象的练习题(掌握) (1)标准的手机类的定义和测试 (2)Demo类有求和方法,Test类进行测试。 什么时候定义成员变量? 当该变量是用来描...

2593
来自专栏python3

mysql-视图

视图是虚拟表或逻辑表,它被定义为具有连接的SQL SELECT查询语句。因为数据库视图与数据库表类似,它由行和列组成,因此可以根据数据库表查询数据。其内容由查询...

723
来自专栏xdecode

JDK并发包总结

线程1, 线程2分别去获取lock1, lock2, 触发死锁. 最终通过DeadlockChecker来触发线程中断.

552
来自专栏iOS 开发杂谈

iOS多线程之三:GCD的使用

一、什么是GCD GCD是Grand Central Dispatch的简称,它是基于C语言的。如果使用GCD,完全由系统管理线程,不需要编写线程代码。只需定...

752
来自专栏闻道于事

PL/SQL 编程(三 )程序包和包体,触发器,视图,索引

一、程序包和包体 程序包(package):存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序,则只能在...

3107
来自专栏北京马哥教育

十分钟带你了解 Python3 多线程核心知识

每个独立的线程有一个程序运行的入口、顺序执行序列和程序的出口。但是线程不能够独立执行,必须依存在应用程序中,由应用程序提供多个线程执行控制。 每个线程都有他自...

2455

扫描关注云+社区