Mysql的死锁与索引的关系

前言

在工作过程中，我们经常会碰到mysql的死锁问题，也是我们非常头疼的问题，今天老顾尝试着和小伙伴们一起学习一下mysql锁的相关知识点。

锁分类

我们经常说的就是 表锁、以及行锁

表锁：对整张表加锁

行锁：对表中的某条记录加锁

mysql数据库引擎支持的锁类型是不同的

MyISAM 只支持到表级锁

InnoDB 可以支持到行级锁

表锁

对整张表加锁，锁的颗粒度大，资源消耗小，并发请求低；表锁有2种模式：

1、表共享锁：对同一表的不阻塞读，但阻塞写。

2、表独占锁：对同一表的操作，不管是读写，都阻塞。

显式加表锁：lock tables {tb_name} read/write

显式释放锁：unlock tables

行锁

我们今天的重点就是行锁，也就是死锁的根源所在。行级别的锁颗粒度小，开销比较大

锁模式：

1、共享锁（读锁）S锁：对同一行的操作，读不阻塞，写阻塞

2、排他锁（写锁）X锁：对同一行的操作，读写都阻塞

3、意向共享锁IS：一个事务想要加S锁，首先先获得该表的IS锁

4、意向排他锁IX：一个事务想要加X锁，首先先获得该表的IX锁

意向锁本质就是表锁，那为什么要有意向锁呢？意向锁用来标识该表上有数据被加锁或将被加锁，对于表级别的请求（lock table....），就可以直接判断是否有锁冲突，不需要逐行检查锁的状态

InnoDB锁方式

InnoDb的默认隔离级别RR（可重复读），在RR下读取数据的方式：

1、快照读：事务开启执行第一个SELECT语句后会获取一个数据快照，直到事物结束读取到的数据都是一致的

普通的 select… 查询都是快照读

2、当前读：读取的数据的最新版本，并且在读的时候不允许其它事物修改当前记录

select… lock in share mode(S锁)select… for update(X锁)

加锁方式：

普通 select… 查询 (不加锁)

普通 insert、update、delete… (隐式加写锁)

select…lock in share mode (加读锁)

select…for update (加写锁)

解锁方式：

提交/回滚事物（commit/rollback）

kill 阻塞进程

锁与索引

InnoDB的行锁是通过给索引上的索引项加锁来实现的

即使在建表的时候没有指定主键，InnoDB会默认创建一个DB_ROW_ID的自增字段为表的主键，并且其主键索引（聚簇索引）为GEN_CLUST_INDEX主键索引也被称为聚簇索引

行锁类型

Record Lock：对对应的索引记录项加锁，称记录锁

Gap Lock：对索引项之间的间隙加锁，加锁之后间隙范围内不允许插入数据，防止发生幻读

Next-key Lock：可以理解为Record Lock+Gap Lock（InnoDB行锁默认加的是 Next-key Lock）

记录锁

这个比较好理解，就是把表中的记录锁定。

间隙锁

编程的思想源于生活，生活中的例子能帮助我们更好的理解一些编程中的思想。

生活中排队的场景，小明，小红，小花三个人依次站成一排，此时，如何让新来的小刚不能站在小红旁边，这时候只要将小红和她前面的小明之间的空隙封锁，将小红和她后面的小花之间的空隙封锁，那么小刚就不能站到小红的旁边。

这里的小红，小明，小花，小刚就是数据库的一条条记录。

他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。

加锁规则

行级锁默认加 next-key lock，查询过程中访问到的索引项都会加锁，而根据不同的索引也有不同的加锁规则：

唯一索引等值查询：

当索引项存在时，next-key lock 退化为 record lock；

当索引项不存在时，默认 next-key lock，访问到不满足条件的第一个值后next-key lock退化成gap lock

唯一索引范围查询：

默认 next-key lock，(特殊’<=’ 范围查询直到访问不满足条件的第一个值为止)

非唯一索引等值查询：

默认next-key lock ，索引项存在/不存在都是访问到不满足条件的第一个值后next-key lock退化成gap lock

非唯一索引范围查询：

默认 next-key lock，向右访问到不满足条件的第一个值为止

针对这几种情况分别举例说明一下，假设我有以下数据：

在上面的数据表我们可以得到5个next-key lock 区间：

唯一索引(id)：(-∞,1]，(1,4]，(4,6]，(6,9] ,（9,+supremum]

非唯一索引(age)：(-∞,18]，(18,21]，(21,23]，(23,26] ,（26,+supremum]

案例：唯一索引等值查询

索引项存在

eg：select * from user where id=4 for update

加锁情况：

默认加next-key lock (1,4]，因索引项存在，则next-key lock退化为 record key，只对id=4的这个索引项的record key

索引项不存在

eg：select * from user where id=5 for update

加锁情况：

默认加next-key lock (4,6]，访问不满足条件id=6后next-key lock退化为grap lock，加锁范围(4，6)

案例：唯一索引范围查询

eg: '>'

select * from user where id > 4 for update

加锁情况：默认next-key lock (4,6]，(6,9]，(9,+suprenum]

eg: '<'

select * from user where id < 4 for update

加锁情况：默认next-key lock (-∞,1]，(1,4]

eg: '>=' (可以拆成 > 和 =)

select * from user where id >= 4 for update

加锁情况：

'>4': 范围查询 默认next-key lock (4,6]，(6,9]，(9,+suprenum]

'=4': 等值查询 默认next-key lock退化为record key，id=4的索引项

‘>=4’: 合并在一起加锁范围为：[4,6]，(6,9]，(9,+suprenum]

eg: '<='（可以拆成 < 和 =）

select * from user where id <= 4 for update

加锁情况:

‘<4’: 范围查询 默认next-key lock (-∞,1]，(1,4]，这里有点特殊范围查询，需要访问不满足条件（<=4）的第一个值为止,因此找到了6；加锁范围 (-∞,1]，(1,4]，(4,6]

'=4': 等值查询 默认next-key lock退化为record key，id=4的索引项

‘<=4’: 合并在一起加锁范围为：(-∞,1]，(1,4]，(4,6]

案例：非唯一索引等值查询

索引项存在

eg：select * from user where age=21 lock in share model

加锁情况：

默认加next-key lock (18,21]， (21,23]；

访问到不满足条件age=23后next-key lock退化为grap key

加锁范围(18,21]， (21,23)

索引项不存在

eg：select * from user where age=19 lock in share model

加锁情况：

默认加next-key lock (18,21]，

访问不满足条件age=21后next-key lock退化为grap lock

加锁范围(18，21)

案例：非唯一索引范围查询

eg：'>'

select * from user where age > 21 lock in share model

加锁情况：

默认加next-key lock (18,21]， (21,23]，(23,26]， (26,+suprenum]，

向右访问到不满足条件第一个值为止 (18,21]， (21,23]，(23,26]， (26,+suprenum]

加锁范围 (18,21]， (21,23]，(23,26]， (26,+suprenum]

eg：'<'

select * from user where age < 21 lock in share model

加锁情况：

默认加next-key lock (-∞,18]， (18,21]，

向右访问到不满足条件第一个值为止 (-∞,18]， (18,21]，(21,23]

加锁范围 (-∞,18]， (18,21]，(21,23]

eg：'>=' (可以拆成 > 和 =)

select * from user where age >= 21 lock in share model

加锁情况：

'= 21' 等值查询 (18,21]， (21,23)

合并加锁范围 (18,21]，(21,23]，(23,26]， (26,+suprenum]

eg：'<='（可以拆成 < 和 =）

select * from user where age < 21 lock in share model

加锁情况：

‘< 21 ’: 范围查询 (-∞,18]， (18,21]， (21,23]

合并加锁范围 (-∞,18]， (18,21]，(21,23]

细心一点你会发现上面例子中：

唯一索引的查询用的是 select … for update非唯一索引的查询用的是 select … lock in share model

for update 加的是写锁，写锁默认认为会对数据做更改，不管查询有没有涉及到回表都会对聚簇索引(主键索引)加锁lock in share model 加的是读锁，如果没有涉及到回表（像覆盖索引），不会对聚簇索引(主键索引)加锁

死锁

死锁指的是两个或两个以上的事物在执行过程中争抢锁资源而造成相互等待的情况

表锁不会出现死锁，主要还是针对InooDB的行锁，可以看下面的例子：

添加图片注释，不超过 140 字（可选）

会话A与会话B 互相产生了死锁。

监控分析锁问题

# 查询InnoDB锁的整体情况# 可以重点查看Innodb_row_lock_waits和Innodb_row_lock_time_avg这两个值# 如果数值较大，说明锁之间的竞争大show status like 'innodb_row_lock%';

#可以通过INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS这三个表#分析可能存在的锁的问题select * from information_schema.INNODB_TRX; # 查看所有事务select * from information_schema.INNODB_LOCKS; # 查看锁select * from information_schema.INNODB_LOCK_WAITS; # 查看锁等待

解决死锁：

超时等待，事物超时自动回滚(innodb_lock_wait_timeout 默认50s)

主动死锁检测，事物请求锁的时候采用 wait-for graph 等待图的方式进行死锁检测（innodb_deadlock_detect 默认on）

发现死锁也可以人为 kill 进程

总结

MySQL锁分为全局锁、表级锁以及行级锁，不同的存储引擎支持锁的粒度有所不同，MyISAM 只支持到表级锁，InnoDB 则可以支持到行级锁，锁的粒度决定了业务的并发度，因此更推荐使用InnoDB

InnoDB默认最小加锁粒度为行级锁，并且锁是加在索引上，如果SQL语句未命中索引，则走聚簇索引的全表扫描，表上每条记录都会上锁，导致并发能力下降，增大死锁的概率，因此需要为表合理的添加索引，线上查询尽量命中索引

行级锁默认加 next-key lock，而根据不同的索引也有不同的加锁规则，我们可以根据加锁规则，分析加锁区间锁粒度的减小提高了并发度的同时也增加了死锁的风险，查询应尽量考虑减少锁的范围