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

相关文章

来自专栏北京马哥教育

优化临时表使用,SQL语句性能提升100倍

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SE...

2688
来自专栏禅林阆苑

mysql学习总结04 — SQL数据操作

mysql 中 SELECT 命令类似于其他编程语言的 print 或 write,可用来显示字符串、数字、数学表达式的结果等

1773
来自专栏技术碎碎念

sql server 2008 数据库的完整性约束

一、数据库完整性概述 1.数据库的完整性: ①数据库的完整性是指数据的正确性和相容性 ②数据库完整性是防止不合语义或不正确的数据进入数据库 ③完整性体现了是否真...

2964
来自专栏desperate633

深入理解四种数据库索引类型(- 唯一索引/非唯一索引 - 主键索引(主索引) - 聚集索引/非聚集索引 - 组合索引)唯一索引/非唯一索引主键索引(主索引)聚集索引/非聚集索引5.组合索引(联合索引)

1.唯一索引是在表上一个或者多个字段组合建立的索引,这个或者这些字段的值组合起来在表中不可以重复。

802
来自专栏个人随笔

MySQL 事物

事务是数据库处理操作,其中执行就好像它是一个单一的一组有序的工作单元。换言之,事务将永远不会是完全的,除非在组内每个单独的操作是成功的。如果事务中的任何操作失败...

3288
来自专栏云霄雨霁

数据库视图和索引

1372
来自专栏性能与架构

Mysql Join的实现原理

在MySQL中,只有一种Join算法,就是大名鼎鼎的NestedLoop Join 对左表进行遍历,拿一条数据和右表的每条数据进行比对,如果找到N条匹配的,此条...

3396
来自专栏PHP技术

MYSQL 优化常用方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更...

3228
来自专栏Java Edge

MySQL必知必会分页whereupdatelimit字符串截取order by排序ength和char_lengthreplace函数1 键2 数据库事务的ACID3 视图4 删除连接

33914
来自专栏架构师之旅

《干货系列》SQL语句-知无不言言无不尽

1.SQL , Structure Query Language,结构化查询语言,是一种申明式的语言。 SQL包括6部分: 1.DQL(Data Query ...

1815

扫码关注云+社区