MySQL 锁详解

原文章来自我的语雀知识库

锁的类型

在MySQL中，锁作用的对象是事务，当某个事务获取行锁、表锁、意向锁之后，在整个事务过程中都会占有这个锁，一般只有在commit或者rollback后释放。（不同事务隔离级别释放的时机可能不同）

由于事务会一直占用某个锁直到commit才会释放，所以有可能满足死锁的四个条件：
1：互斥条件：请求的资源在某一段时间内只能被一个进程占有。
2：不可剥夺条件：进程占有某个资源后，只能由自己主动释放，不能被其它进程强行夺走这个资源。
3：请求与保持条件：进程保持了至少一个资源，同时又请求一个被其它进程占有的资源，于是进程发生阻塞，但又对自己占有的资源保持不放。
4：循环请求条件：存在一条请求与保持的循环链，链中的每一条进程所持有的某个资源又被链中的下一个进程所请求。
一下三条sql语句可以帮助我们分析死锁发生的原因，以便我们调整业务sql语句、或者调整query语句所使用的索引等来预防死锁的发生。如果已经发生了死锁，可以通过KILL id来杀掉某个引起死锁的事务。

SELECT * FROM information_schema.INNODB_TRX;
-- 命令是用来查看当前运行的所有事务：
 
 SELECT * FROM information_schema.INNODB_LOCKs;
-- 命令是用来查看当前出现的锁；
 
SELECT * FROM information_schema.INNODB_LOCK_waits;
-- 命令是用来查看锁等待的对应关系；

共享锁、排他锁

行级共享锁（S Lock）：允许事务读一行数据。
加行级共享锁的例子：select * from demo where id = 1
行级排他锁（X Lock）：允许事务删除或更新一行数据。
加行级排它锁的例子：select * from demo where id = 1 for update
另外还有更粗粒度的表级共享锁、表级排他锁

此外，InnoDB支持多粒度锁定，即允许事务可以同时加行级和表级的锁。为了支持不同粒度上的加锁操作，InnoDB提供了意向锁。意向锁意味着事务希望在更细粒度上进行加锁。

意向锁

在事务向某几行记录加行级锁之前，需要先对这张表加上对应的意向锁。意向锁之间不互相冲突、意向锁与行级锁不会互斥，意向锁只会与表级别锁互斥。这是因为，如果某个事务想获取某张表的表锁时，需要遍历表的每条记录、确定每行记录都没有锁与之互斥，才能加上表锁，开销很大，所以在加行锁之前需要先对这张表加意向锁，这样获取表级锁时就方便地知道表中会不会存在行级锁与其互斥了。
意向共享锁（IS Lock）：表示事务想要获得一张表的某几行的共享锁
意向排他锁（IX Lock）：表示事务想要获得一张表的某几行的排他锁

事务A 和事务B都是行级别锁，则只需要看是否对相同行加锁，以及加的锁是否互相冲突
事务A 需要加表锁，则看这张表上的意向锁和事务A的表锁是否冲突

一致性非锁定读

一致性非锁定读是指InnoDB存储引擎通过多版本并发控制的方式来读取数据库中的数据。如果某行数据由于update或者delete操作被加上排他锁，当某个事务读取这行事务时，不必等待锁释放，而可以利用MVCC机制，使用一致性非锁定读去读取这行数据的一个历史版本。
可重复读隔离级别下的一致性非锁定读总是读取事务开始时的行数据版本。这是依赖启动事务时生成的Read View实现的。
读已提交隔离级别下的一致性非锁定读总是读取被锁定行的最新快照数据。这同样依赖Read View，但读已提交隔离级别在每条语句执行之前都会重新生成一次Read View。

分别分析两种隔离级别下，以上两个事务并行的结果会出现什么不同

可重复读隔离级别下

时间	事务A	事务B
1	BEGIN;
2	SELECT id FROM parent WHERE id=1;
3		BEGIN;
4		UPDATE parent SET id=3 WHERE id=1;
(直到事务B提交之前，会一直占有id=1这行记录的排他锁)
5	SELECT id FROM parent WHERE id=1;
使用一致性非锁定读（即使用MVCC的方式读取一行被加了排他锁的记录的历史版本而不会被排他锁阻塞，不过即使这行记录没有被加锁，同样会使用MVCC的方式去读取），由于是可重复读隔离级别，所以事务A全程只用开事务时创建的Read View来进行一致性非锁定读，所以此时仍然查出结果为
|id|
|1|
6		COMMIT;
7	SELECT id FROM parent WHERE id=1;
结果仍为
|id|
|1|
这是因为，id=1的那行记录被修改为id=3后，实际上原本的那条id=1的记录只是被删除了，然而MySQL的删除只是将deleted标记位置位，所以这里仍能查找到这条被删除的记录的旧版本记录。
8	COMMIT;

读已提交隔离级别下

时间	事务A	事务B
1	BEGIN;
2	SELECT id FROM parent WHERE id=1;
3		BEGIN;
4		UPDATE parent SET id=3 WHERE id=1;
(直到事务B提交之前，会一直占有id=1这行记录的排他锁)
5	SELECT id FROM parent WHERE id=1;
由于是读已提交隔离级别，所以事务A会先新建一个Read View，再进行一致性非锁定读，然而此时事务B还没有提交，所以读出来的记录没变
|id|
|1|
6		COMMIT;
7	SELECT id FROM parent WHERE id=1;
由于是读已提交隔离级别，所以事务A会先新建一个Read View，再进行一致性非锁定读，由于事务B已经提交了，所以此时创建的Read View中事务B被标记为可见，此时事务B的修改结果对事务A是可见的，所以会查出来id=3。
|id|
|3|
8	COMMIT;

一致性锁定读

MySQL在可重复读和读已提交隔离级别下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读(locking read）操作:

• SELECT…FOR UPDATE
• SELECT…LOCK IN SHARE MODE

SELECT…FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。SELECT…LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。锁会一直保持直到这个事务提交。

自增长与锁

自增长在数据库中是非常常见的一种属性，也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时,会通过一致性锁定读的方式来获取计数器的值。为了提高插入的性能，AUTO-INC Locking采用一种特殊的表锁机制，这种锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。
虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成(虽然不用等待事务的完成)。其次，对于INSERT…SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。但MySQL5.1.22版本之后提供了一种轻量级互斥量的自增长实现机制提高了自增长值插入的性能。
SELECT MAX(auto_inc_col)FROM t FOR UPDATE;

外键约束与锁

外键约束

外键约束用来保证参考完整性。具体来说，子表的外键列必须指向主表中的某行数据，这是一种从属关系，相对的，主表的某行数据在删除时，默认情况下要求这行数据在其子表中没有附属的数据。
外键约束是一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

FOREIGN KEY [index_name](index_col_name) REFERENCES table_name(col_name)
[ON DELETE reference_option]
[ON UPDATE reference_option];
在MySQL数据库上定义外键列时，会自动给这个列加上索引。
外键约束还可以定义两个参数，分别定义了父表执行DELETE和UPDATE操作时，对子表
做出的操作。
可定义的子表操作有：CASCADE、SET NULL、NO ACTION、RESTRICT
CASCADE表示当父表发生DELETE或UPDATE操作时，
对相应的子表中的数据也进行DELETE或UPDATE操作。

SET NULL表示当父表发生DELETE或UPDATE操作时，
相应的子表中的数据被更新为NULL值，但是子表中相对应的列必须允许为NULL值。

NOACTION表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。

RESTRICT表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。

如果定义外键时没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。

外键约束导致阻塞

当在有外键约束的表中插入一行数据时，需要先去父表中查询是否存在这行数据的外键值，查询父表使用的是一致性锁定读SELECT…LOCK IN SHARE MODE，即主动给父表的对应行数据加一个S锁，如果父表的这行数据已经加上了X锁，那么子表的插入操作将被阻塞。
例如下面这个例子，事务A给id=3的这行记录加上了X锁，那么事务B在事务A提交或者回滚之前，INSERT外键值为3的操作就会被阻塞。

行级锁的3种算法

InnoDB存储引擎有3种行锁的算法，其分别是:
Record Lock:记录锁，单个行记录上的锁
Gap Lock:间隙锁，锁定一个范围，但不包含记录本身
Next-Key Lock : Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身，这个范围是左开右闭的

以下除特殊说明，均为可重复读隔离级别。（因为读已提交级别下，仅使用记录锁。）

比较反常识的是，InnoDB搜索引擎对行的查询（一致性锁定读）一般都使用临键锁。

例如列a的非唯一索引有索引键10、13、15、20，可以被加锁的区间就有（-INF,10]、(10,13]、(13,15]、(15,20]、(20,+INF)，当使用SELECT * FROM tb WHERE a=13;时，实际上会加一个临键锁和一个间隙锁，锁住(10,13]以及(13,15)。因为列a没有UNIQUE约束，假设说只加记录锁，锁住a=13这行记录，那么还是可以插入一条a=13的记录。这时候再执行SELECT * FROM tb WHERE a=13;就会查出两条记录，发生了幻读。所以说，对于非唯一索引，只能加临键锁。然而，如果是唯一索引，就能优化成只加记录锁。

再举一个例子，一张表有a、b两个列，其中列a为主键，列b为一个普通索引列。表中有记录（1，1）、（3，1）、（5，3）、（7，6）、（10，8）。执行SELECT * FROM tb WHERE b=3 FOR UPDATE;，由于存在两个索引，所以需要分别锁定。对于聚集索引，需要对a=5的记录加记录锁。对于列b的辅助索引需要对（1，3]加临键锁，对(3，6)加间隙锁。在该事务未提交回滚之前，以下三条sql都会被阻塞。

#a=5的记录被排他锁上了
SELECT*FROM z WHERE a=5 LOCK IN SHARE MODE;
#间隙(1,3]被锁住了
INSERT INTO z SELECT 4,2;
#间隙(3,6)被锁住了
INSERT INTO z SELECT 6,5;

以上是对于单个值的查询，对于范围查询则是加上整个查询范围的临键锁。
例如sqlSELECT * FROM tb WHERE a>2 FOR UPDATE;会对（2，+INF）这个区间加X锁，在这个范围内的插入操作是不被允许的。

在MySQL的可重复读隔离级别下，一致性非锁定读使用了MVCC机制，可以避免不可重复读问题，一致性锁定读使用临键锁，避免幻读问题。
而读已提交隔离级别，加锁只使用记录锁，所以不能避免幻读问题。

丢失更新问题

这实际上比较类似于Java中的两个线程并发进行i++操作导致的线程安全问题，在Java中，两个线程会将主内存中i的值读入到各自工作内存中，进行加1后再写回，那么就可能导致i只加了1。
解决方法就是，更新某行记录前需要先使用一致性锁定读对其加锁，保证读取、更新操作只有当前事务能进行，即保证SELECT、UPDATE是一个原子操作。

阻塞超时

当某个事务等待另一个事务的锁资源时会导致阻塞，这个等待的超时时间默认是50秒、且默认不回滚。超时之后，会在阻塞的位置抛出异常。但默认情况下，MySQL并不对这个异常进行回滚，需要配置参数innodb_rollback_on_timeout为ON才会回滚。即不回滚也不COMMIT就意味着发生异常之前的操作仍被保留，这是一种十分危险的状态。

MySQL解除死锁

当满足死锁的四个条件时，即发生死锁。按照第8点的内容，可以想到利用MySQL的超时回滚机制来回滚循环等待链中的某个事务，但默认来说，这会回滚第一个开始阻塞的事务，因为它阻塞的时间最长，最先触发超时回滚。但如果这个事务恰好进行了很多的操作，占用了很多的undo log，那么回滚这个事务需要花费的代价就更大，所以需要一种更好的解决方案。
MySQL采用wait-for graph的方式来进行死锁检测，具体来说，等待图保存了事务等待锁的信息链表。可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，MySQL会选择回滚其中占用undo log量最小的事务。