一个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 条评论
登录 后参与评论

相关文章

来自专栏源哥的专栏

oracle中如何删除重复数据

        我们可能会出现这种情况,某个表原来设计不周全,导致表里面的数据数据重复,那么,如何对重复的数据进行删除呢?         重复的数据可能有这样...

893
来自专栏菜鸟致敬

【2】My SQL 管理

(备注,请忽略奇奇怪怪的大小写不统一) 启动及关闭 MySQL 服务器 首先,我们需要通过以下命令来检查MySQL服务器是否启动:(centos测试) ps ...

3509
来自专栏蓝天

测试mktime和localtime_r性能及优化方法

2142
来自专栏LanceToBigData

MySQL(十一)之触发器

上一篇介绍的是比较简单的视图,其实用起来是相对比较简单的,以后有什么更多的关于视图的用法,到时候在自己补充。接下来让我们一起了解一下触发器的使用! 一、触发器概...

2268
来自专栏Ryan Miao

Ubuntu18.04(linux)安装MySQL

Ubuntu18.04 安装mysql或者mariadb之后,发现普通用户和远程都没有权限连接。

1102
来自专栏用户2442861的专栏

mysql update,insert常用

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/haluoluo211/article/d...

943
来自专栏帘卷西风的专栏

关于mysql常用时间函数

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog) 

642
来自专栏Aox Lei

Mycat基本使用教程

路径在: /usr/local/mycat/conf/rule.xml function必须在tableRule的下面, 否则会报错找不到

3032
来自专栏运维

Zabbix历史数据处理办法

SELECT TABLE_NAME AS "Table", round(((data_length + index_length) / 1024 / 1024)...

772
来自专栏青青天空树

mysql用户创建及授权

一、 创建用户:  命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';  说明:usern...

681

扫码关注云+社区