InnoDB 存储引擎既支持表锁，也支持行锁。表锁实现简单，占用资源较少，不过粒度很粗，并发性能差。行锁粒度更细，可以实现更精准的并发控制。

表级锁

1. 表级别 S 锁、X 锁

表锁的语法是 LOCK TABLES … READ/WRITE，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。表锁分为读锁和写锁，也就是共享锁（S）和排他锁（X）。

# InnoDB存储引擎会对表t加表级S锁
LOCK TABLES t READ;
# InnoDB存储引擎会对表t加表级X锁
LOCK TABLES t WRITE;

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。InnoDB 的厉害之处还是实现了更细粒度的行锁。

2. 元数据锁

另一类表级的锁是 MDL（metadata lock)，也叫元数据锁。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。假设一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，这肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。

• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的，但却是你不能忽略的一个机制。比如下面这个例子，给一个小表加个字段，却导致整个库挂了。给一个表加字段、修改字段或加索引，需要扫描全表的数据。因此在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没事，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。因为所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

如果查询语句频繁且客户端有重试机制，即超时后会再起一个新 session 再请求的话，那这个库的线程很快就会爆满。你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

如何安全地给小表加字段？

首先我们要解决长事务，事务不提交就会一直占着 MDL 锁。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。如果你要变更的表是一个热点表，请求很频繁，这时候 kill 可能未必管用，因为新的请求马上就来了。这时可以在 alter table 语句里设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃，之后再重试这个过程。

3. 表级别 AUTO-INC 锁

在使用 MySQL 过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值。系统实现这种自动给 AUTO_INCREMENT 修饰的列递增赋值的原理主要是两个：

• 采用 AUTO-INC 锁，即在执行插入时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一个事务在持有 AUTO-INC 锁时，可以保证一个语句中分配的递增值是连续的。

如果我们的插入语句在执行前不确定具体要插入多少条记录，比方说使用 INSERT … SELECT、REPLACE … SELECT 或者 LOAD DATA 这种插入语句，一般是使用 AUTO-INC 锁为 AUTO_INCREMENT 修饰的列生成对应的值。注意，AUTO-INC 锁的作用范围只是单个插入语句，插入语句执行完成后，这个锁就被释放了。

• 采用一个轻量级锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的 AUTO_INCREMENT 列的值后，就释放该轻量级锁，并不需要等到整个插入语句执行完才释放锁。如果我们的插入语句在执行前就可以确定具体要插入多少条记录，那么一般采用轻量级锁的方式对 AUTO_INCREMENT 修饰的列赋值。这种方式可以避免锁定表，提升插入性能。

InnoDB 提供了 innodb_autoinc_lock_mode 系统变量来控制使用哪种方式来为 AUTO_INCREMENT 修饰的列进行赋值，其可选值如下：

不过当 innodb_autoinc_lock_mode 值为 2 时，可能会造成不同事务中的插入语句为 AUTO_INCREMENT 修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。

行级锁

MySQL 的行锁是在引擎层由各存储引擎自己实现的。但并非所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，那同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

1. Record Locks

这种类型的行锁就是仅仅把一条记录锁上，官方名称为 LOCK_REC_NOT_GAP，我们称为记录锁。Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会使用隐式的主键来进行锁定。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

在 InnoDB 1.0 版本之前，用户只能通过 SHOW ENGINE INNODB STATUS 命令来查看当前数据库中锁请求的信息，然后再判断事务锁的情况。从 InnoDB 1.0 开始，在 INFORMATION_SCHEMA 架构下添加了表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通过这三张表，用户可以更简单地监控当前事务并分析可能存在的锁问题。

二阶段锁协议

在下面的操作序列中，事务 B 的 update 语句执行时会是什么现象呢？假设字段 id 是表 t 的主键。

实际上事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这就是两阶段锁协议。因此，如果一个事务中需要锁多个行，要把最可能造成锁冲突、最影响并发度的锁尽量往后放。

2. Gap Locks

我们说 MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有个问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加锁。因此，InnoDB 提出了 Gap Locks 的锁，这种锁会锁定一个范围，但不锁定记录本身。官方名称为 LOCK_GAP，我们称为 gap 锁或间隙锁。

上图中为 number 值为 8 的记录加了 gap 锁，意味着不允许别的事务在 number 值为 8 的记录前边的间隙插入新记录，其实就是 number 列的值 [3, 8] 这个区间的新记录是不允许立即插入的。比方说有另外一个事务再想插入一条 number 值为 4 的新记录，它定位到该条新记录的下一条记录的 number 值为 8，而这条记录上又有一个 gap 锁，所以就会阻塞插入操作，直到拥有这个 gap 锁的事务提交后，这条新记录才可以被插入。

这个 gap 锁的提出仅仅是为了防止插入幻影记录而提出的，虽然有共享 gap 锁和独占 gap 锁这样的说法，但是它们起到的作用都是相同的。而且如果你对一条记录加了 gap 锁（不论是共享 gap 锁还是独占 gap 锁），并不会限制其他事务对这条记录加记录锁或者继续加 gap 锁，因为 gap 锁的作用只是为了防止插入幻影记录。

给一条记录加了 gap 锁只是不允许其他事务往这条记录前边的间隙插入新记录，那对于最后一条记录之后的间隙，也就 number 值为 20 的记录之后的间隙该咋办呢？也就是说给哪条记录加 gap 锁才能阻止其他事务插入 number 值在 [20, +∞) 这个区间的新记录呢？这时候应该想起分析数据页时介绍的两条伪记录了：

• Infimum 记录，表示该页面中最小的记录。
• Supremum 记录，表示该页面中最大的记录。

为了实现阻止其他事务插入 number 值在 [20, +∞) 这个区间的新记录，我们可以给索引中的最后一条记录，也就是 number 值为 20 的那条记录所在页面的 Supremum 记录加上一个 gap 锁：

这样就可以阻止其他事务插入 number 值在 (20, +∞) 这个区间的新记录。

3. Next-Key Locks

有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，所以 InnoDB 提出了一种称为 Next-Key Locks 的锁，官方的类型名称为 LOCK_ORDINARY，我们也可以简称为 next-key 锁。比如我们把 number 值为 8 的那条记录加一个 next-key 锁：

next-key 锁本质上就是一个记录锁和一个 gap 锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

4. Insert Intention Locks

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的 gap 锁，如果有的话，插入操作需要等待，直到拥有 gap 锁的那个事务提交。但是 InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB 把这种类型的锁命名为 Insert Intention Locks，官方的类型名称为 LOCK_INSERT_INTENTION，我们也可以称为插入意向锁。比如我们把 number 值为 8 的那条记录加一个插入意向锁：

下面我们举个例子来理解这个插入意向锁的功能：

比方说现在 T1 为 number 值为 8 的记录加了一个 gap 锁，然后 T2 和 T3 分别想向表中插入 number 值为 4、5 的两条记录，所以现在为 number 值为 8 的记录加的锁的示意图如下所示：

从图中可以看到，由于 T1 持有 gap 锁，所以 T2 和 T3 需要生成一个插入意向锁的锁结构并处于等待状态。当 T1 提交后会把它获取到的锁都释放掉，这样 T2 和 T3 就能获取到对应的插入意向锁了，T2 和 T3 之间也并不会相互阻塞，它们可以同时获取到 number 值为 8 的插入意向锁，然后执行插入操作。事实上，插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

5. 隐式锁

前边说过，一个事务在执行 INSERT 操作时，如果即将插入的间隙已经被其他事务加了 gap 锁，那么本次的 INSERT 操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般情况下 INSERT 操作是不加锁的。那如果一个事务首先插入了一条记录，然后另一个事务

• 立即使用 SELECT … LOCK IN SHARE MODE 语句读取这条事务，也就是在要获取这条记录的 S 锁，或者使用 SELECT … FOR UPDATE 语句读取这条事务，也就是要获取这条记录的 X 锁，该咋办？如果允许这种情况的发生，那么可能产生脏读问题。

• 立即修改这条记录，也就是要获取这条记录的 X 锁，该咋办？如果允许这种情况的发生，那么可能产生脏写问题。

我们把聚簇索引和二级索引中的记录分开看一下：

情景一：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的事务 id。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列代表的的就是当前事务的事务 id，如果其他事务此时想对该记录添加 S 锁或 X 锁时，首先会看一下该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个 X 锁，然后自己进入等待状态。

情景二：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的事务 id，如果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃事务 id，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复情景一的做法。

因此，一个事务对新插入的记录可以不显式的加锁，但是由于事务 id 的存在，相当于加了一个隐式锁。别的事务在对这条记录加 S 锁或者 X 锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。