码农的世界

Mysql之Innodb锁模式和死锁解析

2019-09-28  本文已影响0人  爱情小傻蛋

一. 背景知识

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。由于后面的死锁问题基本都是并发事务的前提下发生，因此我们先了解一下事务的相关知识。

1、事务的ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

2、并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

3、事务的隔离级别

上述并发问题中，“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。ISO/ANSI SQL92定义了4个事务隔离级别：

注意：存在幻读问题，可重复读只是针对普通的查询语句，1、若查询语句显示的加上锁如，lock in share mode或者for update，则此次查询会放弃Copy版本（快照读），采用当前最新版本（当前读），从而可以读到其它事务提交后（包括更新和插入）的最新数据；2、若当前事务恰好更新了本事务内的数据和其它事务提交后的数据，那么被更新的数据会同步到Copy版本（更新快照），因此之后的查询均可查询到被更新的数据。

二、锁模式

1、意向锁

innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

InnoDB默认使用行锁，行锁是对索引加的锁，而不是针对记录加的锁。因此，在不通过索引条件查询的时候，使用的是表锁。不管是表锁还是行锁，都有如下两种锁模式：

另外，为了提高表锁和行锁冲突检测效率，InnoDB提供了如下两种意向锁（Intention Locks），这两种意向锁都是表锁，是InnoDB在获取行锁前自动加上的：

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。那么锁与锁的“冲突-兼容”规则是怎样的呢？

兼容性 IS IX S X
IS 兼容 兼容 兼容 互斥
IX 兼容 兼容 互斥 互斥
S 兼容 互斥 兼容 互斥
X 互斥 互斥 互斥 互斥

2、行锁（Record Lock）

行锁是加在某一行数据对应的索引上，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集索引加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁，因此也无需等待锁（Create Table And Select, CATS 语句除外，CATS中的查询语句会隐式加上共享锁，原因是为了保证主从复制的正确性）；普通SELECT可通过如下形式显示给数据集索引加共享锁或排他锁：

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE

注意：即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析加锁机制时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。比如，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。

3、间隙锁（Gap Lock）

在事务隔离级别为可重复读的情况下，InnoDB还会出现间隙锁（Gap Lock），间隙锁是指加在索引之间的锁（开区间，不包括索引记录本身）。

由于可以阻止其它事务在索引间隙中插入新数据，因此，间隙锁可避免一部分幻读的产生，当然也可以通过如下语句来关闭间隙锁：

set innodb_locks_unsafe_for_binlog = ‘on’

我们可通过如下范围查询或者查询不存在记录来模拟间隙锁：

语句1：SELECT * FROM table_name WHERE index > 100 LOCK IN SHARE MODE （假设表中只有index为1,2,…101的记录, 那么会锁定101这条记录的索引以及大于101的索引间隙）
语句2：UPDATE table_name SET … WHERE index = 103 （假设表中只有index为1,2,…101,105,106的记录, 那么会锁定101至105之间的索引间隙）

注意：间隙锁没有区分排他和共享模式，并且只在UPDATE、DELETE以及特殊的SELECT语句中出现（INSERT语句采用了另外一种锁——插入意向间隙锁）

4、插入意向间隙锁（Insert Intention Gap Lock）

在InnoDB中，INSERT语句会在插入记录前，在记录索引所在的间隙加上一个插入意向间隙锁（Insert Intention Gap Lock），插入成功后会将插入意向间隙锁转换为对应记录索引上的排它锁。比如有3和5这两条记录，现要插入4这条记录，那么就会在索引区间（3,5）加Insert Intention Gap Lock，插入成功后会对4这条记录的索引加X Lock。

由于插入意向间隙锁是兼容的，因此在多事务同时写入不同数据至同一索引间隙（甚至是同一个非唯一索引）的时候，并不会发生锁等待，从而提高并发插入性能。

5、后码锁（ Next-Key Lock）

后码锁（Next-Key Lock）就是行锁（Record Lock）和间隙锁（Gap Lock）的结合体，它锁定记录索引本身以及记录索引的前后两个间隙。在事务隔离级别为可重复读且间隙锁开启的情况下， InnoDB会默认使用Next-Key Lock ，示例如下：

SELECT * FROM table_name WHERE index = 100 FOR UPDATE（假设表中存在index为95、100、105的记录, 那么会锁定100这条记录的索引以及（95,100）和（100,105）两个索引间隙）

注意：当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为行锁，即仅锁住索引本身，而不是范围。

三、多版本并发（MVCC）

四、死锁

1、锁类型介绍：

MySQL有三种锁的级别：页级、表级、行级。

2、死锁模型

死锁一般是两个或两个以上事务相互等待对方释放锁，形成死循环所造成的，如下图所示。


3、死锁条件

4、可能的几种死锁

需求：将投资的钱拆成几份随机分配给借款人。
起初业务程序思路是这样的：
投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。
例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2
B用户金额随机分为2份，分给借款人2，1

由于加锁的顺序不一样，死锁当然很快就出现了。

对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。

Select * from xxx where id in (xx,xx,xx) for update

在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁。

例如（以下会话id为主键）：
 
Session1:
mysql> select * from t3 where id in (8,9) for update;
+----+--------+------+---------------------+
| id | course | name | ctime               |
+----+--------+------+---------------------+
|  8 | WA     | f    | 2016-03-02 11:36:30 |
|  9 | JX     | f    | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+
rows in set (0.04 sec)
 
 
Session2:
select * from t3 where id in (10,8,5) for update;
锁等待中……
 
其实这个时候id=10这条记录没有被锁住的，但id=5的记录已经被锁住了，锁的等待在id=8的这里
不信请看
 
Session3:
mysql> select * from t3 where id=5 for update;
锁等待中
 
 
Session4:
mysql> select * from t3 where id=10 for update;
+----+--------+------+---------------------+
| id | course | name | ctime               |
+----+--------+------+---------------------+
| 10 | JB     | g    | 2016-03-10 11:45:05 |
+----+--------+------+---------------------+
row in set (0.00 sec)
 
 
在其它session中id=5是加不了锁的，但是id=10是可以加上锁的。

3、死锁预防策略

1）减少事务操作的记录数
2）合理设置索引（索引的粒度为一条记录）
3）对事务中要操作的记录进行排序
4）避免使用唯一键值约束

4、补充知识

1）查看事务隔离级别

SELECT @@tx_isolation;

2）设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

3）查看锁等待状态

SELECT * FROM information_schema.innodb_locks;

4）查看innodb状态(包含最近的死锁日志)

show engine innodb status;

上一篇下一篇

猜你喜欢

热点阅读