MySQL的锁事

什么是锁

在MySQL中，Lock与Latch都可以被称为锁。Latch一般称为门闩，是一种轻量级锁，底层实现基于OS的Mutex(互斥量）或rwlock(读写锁）机制。其目的是保证并发线程操作临近资源的准确性。
Lock的对象是事物，用来锁定数据库中的对象，如表，页，行。且一般Lock的对象在事物提交或回滚后进行释放。两者进一步对比如下：

MySQL表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是并发的ddl操作破坏了事务的隔离级别，导致前后执行的结果不一致以及主从复制上可能导致的问题，保证读写的正确性。

你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
• MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

InnoDB中的锁

InnoDB有2种标准的行级锁：

• 共享锁（S Lock) ，允许事物读取一行的数据, 也叫读锁（read lock)
• 排它锁（X Lock)，允许事物删除或更新一行数据, 也叫（write lock)

根据事物对行的读写操作，两种锁的兼容性如下：

也就是说：只有共享锁与共享锁兼容，排它锁与任何锁都不兼容。
进一步理解就是：如果一个事物对某行记录加了X锁，则其他事物要加X锁，或S锁都需要等待。 如果一个事物对某行加了S锁，则其他事物只允许加S锁，其他锁都需要等待之前所的释放。

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

除此之外，InnoDB为了支持不同粒度的加锁操作，还支持一种锁类型：意向锁（Intention Lock)，主要用于控制细粒度的对象

意向锁也有2种类型：

• 意向共享锁(IS)，事物想要获取一张表某几行的共享锁
• 意向排它锁(IX)，事物想要获取一张表某几行的排它锁

综合行级锁与意向所的类型，组合的兼容情况如下：

一致性非锁定读

一致性非锁定读（consistent nonlocking read)是指InnoDB存储引擎通过行多版本控制（multi version）的方式来读取当前读取数据库中行的数据。如果读取的行只在执行Delete或Update操作，这时的读取不会等待行锁的释放。此时会读取行的一个快照数据。如图：

之所以成为非锁定读，是因为不需要等待行上的X锁释放。快照数据时值该行之前版本的数据，底层实现基于undo log来实现，是一个逻辑上的快照版，比如版本2需要基于undo log在版本1的基础上才能得到具体的数据。而undo log用来在事物中回滚数据，因此快照数据本身没有额外的开销。 同时快照数据是不需要上锁的，因此没有事物需要对历史版本数据进行修改操作。

所以，非锁定读机制极大的提高了数据库的并发性。在InnoDB默认设置下，读取不会占用和等待表上的锁。但是在不同的事物隔离级别下，读取的方式有所不同。

行的快照数据就是当前行的历史版本，每行可能有多个版本。多个事物对行数据的并发读写，也成为基于行的多版本并发控制（Multi Verstion Concurrency Control,MVCC)

InnoDB在默认的可重复读事物隔离级别下（Repeatable Read), InnoDB使用非锁定一致性读，也就是事物开始时的快照版本，也叫一致性读。

对于读提交事物隔离级别（Read Commited), 非锁定读取的是最新的快照版本，也叫非锁定的当前读。

一致性锁定读（当前读）

在默认的可重复读事物隔离级别下，对于常规的读场景，读取某行时，此时改行正在执行Delete或Update操作，MySQL采用非锁定的一致性读。
但是某些情况，用户需要显示的对数据库读取操作进行加锁以保证数据逻辑一致性。
InnoDB对于SELECT语句支持两种一致性的锁定读（locking read)

• SELECT …. FOR UPDATE, 对读取行加一个X锁，其他事物只能等待
• SELECT … LOCK IN SHARE MODE，对读取行加一个S锁，其他事物可以加S锁，X锁则需要等待

对于一致性非锁定读(普通Select)，本身采用的一致性读，即使读取的行被其他会话执行了SELECT FOR UPDATE 或 SELECT LOCK IN SHARE MODE, 也是按一致性规则读取。

此外 SELECT FOR UPDATE 或 SELECT LOCK IN SHARE MODE的加锁前提必需要在一个事物中， 也就是说需要执行：BEGAIN/END，Start Transaction/End, 或 SET autocommit=0 然后COMMIT

而对于SELECT FOR UPDATE/LOCK IN SHARE MODE,Update/Delete 本身而言，则采用的是当前读。

AUTO-INC Locking

对于主键自增长的表，MySQL内部采用： SELECT MAX(auto_inc col) FROM table for update 用来初始化计数器的值，然后缓存到内存里。每次获取时，通过一个轻量级的互斥量锁来获取新的计数器值，在SQL执行完成后立即释放该锁，而不用等待事物的完成。这种机制很大的提高了自增长插入的性能。

行锁的算法

InnoDB引擎有3中行锁算法，分别是：

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

Record Locking会锁定索引记录（主键索引Id)，如果没有索引，则使用隐式的主键来进行锁定。
在RC事物级别中，采用的是Record Locking 算法。

在RR事物级别中，Next-Key Locking 对于行的查询都是采用这种锁定算法，每个next-key lock是前开后闭区间，对于一个索引有10，11，13，20这四个主键值，那么该索引可能被Next-Key Locking的区间为：
(-∞，10]，（10，11]， (11,13]，(13, 20]， （20，+∞）

幻读

在RR隔离级别下，Next-Key Locking主要用于解决幻读（Phantom Problem，即幻想问题），是指在同一事物下，连续两次同一的SQL可能导致不同的结果，第二次SQL的执行可能返回执行不存在的行。InnoDB通过采用Next-Key Locking算法来避免幻读问题。

举个例子演示一下，在RC隔离级别下，会出现幻读，在RR隔离级别下则不会。
创建测试表，新增3条数据。

create table t (
    a int
) engine=InnoDB;
insert into t select 1;
insert into t select 2;
insert into t select 5;
mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 sec)

set transaction_isolation=’read-uncommitted’;

set transaction_isolation=’repeatable-read’;
设置隔离级别为RR后，在会话1中，开启事物，执行select * from t where a > 2 for update，
在会话2中执行insert into t select 4 会被阻塞。会话1提交后，会话2继续执行。

所以：InnoDB在RR中解决了幻读问题，其背后采用Next-Key Locking。
执行select * from t where a > 2 for update时，锁住的是（2，+∞）这个范围，并增加了X锁。因此在这个范围执行的任何插入都是不允许的，从而避免幻读。

MySQL加锁规则

1. 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
2. 原则2：查找过程中访问到的对象才会加锁。
3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁（Record Lock)
4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。