mysql+innodb模式_Mysql之Innodb锁模式和死锁解析

一. 背景知识

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

1、事务的ACID属性

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

原子性(Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态。这里的“一致” 包含两个层面，一个是数据库层面的数据完整性约束，比如所有的内部数据结构(如B树索引或双向链表)必须是正确的。另一个层面是应用层的逻辑约束，比如银行的转账一方加了100元，那么另一方必须减100元(数额不能多也不能少)。

隔离性(Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性(Durable)：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

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

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

脏读(Dirty Reads)：一个事务正在对一条记录做修改，在这个事务完成并提交前，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。

不可重复读(Non-Repeatable Reads)：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变，这种现象就叫做“不可重复读”。

幻读(Phantom Reads)：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

3、事务的隔离级别

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

未提交读(Read Uncommitted)：最低的隔离级别，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。

已提交读(Read Committed)：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题，但两次读取同一份数据可能看到不同的结果(存在不可重复读问题)。

可重复读(Repeated Read)：在一个事务中，对于同一份数据的读取结果总是相同的(快照读，通过数据多版本并发控制实现，简称MVCC，也就是对当前的数据Copy一个版本，以后的读操作从这个版本中读取)，无论是否有其他事务对数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读问题。

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

序列化(Serializable)：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

二、锁模式

1、意向锁

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

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

共享锁(S)：允许一个事务去读数据，阻止其他事务获得相同数据集的排他锁。

排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

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

意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

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

“冲突-兼容”规则

兼容性

IS

IX

S

X

IS

兼容

兼容

兼容

互斥

IX

兼容

兼容

互斥

互斥

S

兼容

互斥

兼容

互斥

X

互斥

互斥

互斥

互斥

思考1：意向锁是怎么提高检测效率的？

答案：在加表锁的时可立即检测到是否存在意向锁，若存在，则说明有行锁，因此无须遍历整个表即可获得冲突结果。

思考2：为什么IX 与 X会产生冲突？与行级锁矛盾？

答案：IX，IS是表级锁，不会和行级的X，S锁发生冲突，锁的冲突是按级分开的。

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 会进行优化，将其降级为行锁，即仅锁住索引本身，而不是范围。

“冲突-兼容”规则2

考虑插入意向锁后的冲突规则为：

三、多版本并发(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是可以加上锁的。

插入死锁

2)索引不合理导致的死锁

由于InnoDB的锁是加在索引上的，因此索引不合理将直接导致锁定范围增大，发生锁冲突和死锁的的概率也随着增加。如果对索引和加锁机制不太了解的话，就很难定位死锁的原因。

3)插入意向间隙锁与间隙所冲突导致的死锁

当多个事务持有间隙锁，且事务相互插入对方锁定的索引间隙时，便造成了死锁。如图所示。

4)唯一键值冲突导致的死锁

这个场景主要发生在三个或三个以上的事务同时进行唯一键值相同的记录插入操作，如图所示。

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;