LHon(学习笔记)——MySQL锁

MySQL锁的学习

关于MySQL事务隔离级别及传播行为可以看另一篇文章

最近工作中遇到了一些和MySQL有关的问题，看了很多博客，发现每一篇博客的观点都有所不同，越看越迷惑，所以还是决定自己参考书籍《MySQL技术内幕 InnoDB存储引擎》梳理一下关于MySQL锁的一些认识…

什么是锁

数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。为了保证一致性，必须有锁的介入。

lock 与 latch

• 这两者都可以被称为锁，latch是一种轻量级的锁，其要求锁定的时间必须非常短，否则应用的性能会非常差，它存在的目的是为了保证并发线程操作临界资源的正确性，没有死锁检测机制。
• lock的对象是事务，锁定的是数据库中的对象，如表、页、行，它有死锁机制，一般要求在事务commit或rollback后进行释放（不同的事务隔离级别有所不同）。

InnoDB存储引擎中的锁

InnoDB存储引擎实现了如下两种标准的行级锁：

• 共享锁（S Lock）：允许事务读一行数据；
• 排他锁（X Lock）：允许事务删除或更新一行数据。

如果一个事务T1获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，但若有其他事务T3想要获取行r的排他锁，则其必须等待T1、T2释放行r上的共享锁，这个就是共享锁和排他锁的兼容性。

	X	S
X	不兼容	不兼容
S	不兼容	兼容

S和X锁都是行锁，兼容是指对同一记录（row）的兼容

InnoDB存储引擎还支持多粒度锁定，允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB支持一种额外的锁方式，称之为意向锁，其意向锁即为表级别的锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型，支持以下两种意向锁：

• 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；
• 意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁。

表级意向锁与行级锁的兼容情况如下表所示：

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

一致性非锁定读

一致性非锁定读是指InnoDB存储引擎通过行多版本控制机制的方法来读取当前执行时间数据库中行的数据。如果读取的行被加了X锁，此时的读取不会等待锁的释放，会去读取行的一个快照数据（该行之前版本的数据），该实现通过undo段来实现，undo段用来在事务中回滚数据，所以快照数据本身没有额外的开销，读取快照数据也不需要加锁（没有事务要修改历史数据）。另外需要注意的是，在不同事务隔离级别下，其读取的方式是不相同的，并不是每个事务隔离级别下都是采用的非锁定一致性读。

MVCC（多版本并发控制）：快照数据其实就是当前行数据 之前的历史版本，每行记录可能有多个版本，这种技术被称为行多版本技术，由此带来的并发控制，称为多版本并发控制，用来解决读写冲突问题，通过两个隐藏列来实现（事务ID，回滚指针）。

在事务隔离级别RC、RR下，InnoDB存储引擎使用非锁定的一致性读。它们对快照的定义是不同的：

• RC（读已提交）：非一致性读总是读取被锁定行的最新一份快照数据；
• RR（可重复读）：非一致性读总是读取事务开始时的行数据版本。

举个例子：

如下，会话A和会话B处于RC隔离级别下，事务A和事务C处于RR隔离级别下。

时间轴	Session A	Session B	Session C
1		begin; select * from test where id=1;	begin; select * from test where id=1;
2	begin; update test set id=3 where id=1;
3		select * from test where id=1;	select * from test where id=1;
4	commit;
5		select * from test where id=1;	select * from test where id=1;

时间轴1：此时会话B和会话C得到的结果是一样的；

时间轴2：会话A开启新事务去更新数据，未提交；

时间轴3：由于A未提交，所以此时两个得到的结果还是一样；

时间轴5：会话A提交了，B和C得到的结果就会有差异了，对于B，它处于RC隔离级别下，总是会去读取行的最新版本，如果行被锁定了，则读取该行版本最新一个快照，所以此时B得不到id=1的数据（被会话A删除了）。而对于会话C，它处于RR隔离级别下，总是读取事务开始时的行数据，所以它此时也能拿到id=1的数据。从数据库理论上来讲，隔离级别RC违反了ACID特性的隔离性。

一致性锁定读

InnoDB默认的事务隔离级别（RR）下，SELECT操作使用一致性非锁定读，但有时候需要显式的对数据库读取操作加锁来保证数据逻辑的一致性。对于SELECT操作，InnoDB支持以下两种一致性的锁定读操作。

• select … for update ： 对读取的行记录会加一个X锁，其他事务不能对已锁定的行加上任何锁（可以进行一致性非锁定读）。
• select … lock in share mode : 对读取的行记录会加上一个S锁，其他事务也可以对该行加S锁，但是加X锁会被阻塞。

这两个锁必须存在于事务中，当事务commit或者rollback后锁便会释放。

外键和锁

对于一个外键列，如果没有显式的对这个列加索引，InnoDB存储引擎会自动对其加一个索引，因为这样可以避免表锁。对于外键值的插入或更新，首先需要select父表，对于这个select操作，不是使用一致性非锁定读的方式，而是使用的select … lock in share mode 的方式（对父表对应行加S锁）。如果使用一致性非锁定读，会造成数据不一致性：

	session A	session B
1	begin; delete from parent where id = 3;
2		begin; insert into child select 2,3;

如上，此时两个会话均还未commit或rollback，如果此时会话B使用了一致性非锁定读的方式，会读到父表中id为3的记录，当两个会话都提交了，父表中id为3的数据便不存在了，而会话B依然将其插入到子表了。而使用select … lock in share mode 的方式，主动对父表加一个S锁，这个时候会话B便会被阻塞，因为会话A已经持有了X锁。

行锁的三种算法

InnoDB存储引擎有三种行锁算法：

• Record Lock：单个行记录上的锁；
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；
• Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时会使用隐式的主键来进行锁定。

对于Next-Key Lock，如果一个索引有10，11，13，20这四个值，那么该索引可能被Next-Key Locking的区间为（左开右闭）：

(-∞,10]，(10,11]，(11,13]，(13,20]，(20,+∞)

当查询的索引含有唯一属性时，InnoDB会将Next-key Lock降级为Record Lock，即仅锁住索引本身，而不是范围。

接下来创建表t，表t里面有一个列a，a是主键索引，插入a值为1、2、5的三行数据：

	Session A	Session B
1	begin; select * from t where a = 5 for update;
2		begin: insert into t select 4; commit;
3	commit;

如上，会话A会对a = 5加X锁，但会话B不会被阻塞，可以直接插入a = 4的记录，因为a是主键且唯一的，锁定的仅仅是5这个值，而不是(2,5)这个范围。若是辅助索引，则情况就会有所不同了。

创建表z，表里有列a、b，其中a是主键索引，b是辅助索引，插入(1,1)，(3,1)，(5,3)，(7,6)，(10,8)，这几行数据。

执行SQL①: select * from z where b = 3 for update;

对于列a，其只会对a = 5的索引上Record Lock，对于辅助索引b，锁定的范围便是(1,3)，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，所以还会有一个范围为(3,6)的锁。

select * from z where a = 5 lock in share mode;

insert into z select 4,2;

insert into z select 6,5;

上面的第一条SQL是显然不能够执行的，在上面的SQL①中已经对a = 5的值加上了X锁，后两条SQL主键索引没有问题，但是辅助索引分别命中了(1,3)、(3,6)，故同样也会被阻塞。

解决幻读（Phantom Problem）

幻读是指同一事务下，连续执行两次同样的SQL可能导致不同的结果，第二次的SQL语句可能返回之前不存在的行。

在默认的事务隔离级别下，即RR，InnoDB采用Next-key Locking机制来避免幻读，这里使用上面的表t来说明一下(t表有a值为1、2、5的三行记录)：

select * from t where a > 2 for update，这条语句锁住的不是5这单个值，而是(2,+∞)这个范围。

在事务隔离级别RC中仅采用Record Lock。

阻塞

因为不同锁之间的兼容性问题，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它占用的资源，这就是阻塞。

死锁

死锁是指两个或者两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。