我所理解的MySQL五：锁及加锁规则

mysql教程栏目介绍MySQL的第五篇文章，关于锁及加锁规则。

MySQL 系列的第五篇，主要内容是锁（Lock），包括锁的粒度分类、行锁、间隙锁以及加锁规则等。

MySQL 引入锁的目的是为了解决并发写的问题，比如两个事务同时对同一条记录进行写操作，如果允许它们同时进行，那就会产生脏写的问题，这是任何一种隔离级别都不允许发生的异常情况，而锁的作用就是让两个并发写操作按照一定的顺序执行，避免脏写问题。

首先申明本文中所使用到的示例

CREATE TABLE `user`  (  `id` int(12) NOT NULL AUTO_INCREMENT,  `name` varchar(36) NULL DEFAULT NULL,  `age` int(12) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;insert into user values (5,&＃39;重塑&＃39;,5),(10,&＃39;达达&＃39;,10),(15,&＃39;刺猬&＃39;,15); 

本文所述示例都是在 MySQL InnoDB 存储引擎以及可重复读（Repeatable Read）隔离级别下。

1. 锁的粒度分类

从锁的粒度来看，MySQL 中的锁可以分为全局锁、表级锁和行锁三种。

1.1 全局锁

全局锁会将整个数据库都加上锁，此时数据库将处于只读状态，任何修改数据库的语句，包括 DDL（Data Definition Language）及增删改的 DML（Data Manipulation Language）语句都将被阻塞，直到数据库全局锁释放。

最常使用到全就锁的地方就是进行全库备份，我们可以通过以下的语句实现全局锁的加锁与释放锁操作：

-- 加全局锁flush tables with read lock;-- 释放全局锁unlock table; 

若客户端链接断开，也会自动释放全局锁。

1.2 表级锁

表级锁会将整张表加上锁，MySQL 中的表级锁有：表锁、元数据锁（Meta Data Lock）、意向锁（Intention Lock）和自增锁（AUTO-INC Lock）。

1.2.1 表锁

表锁的加锁和释放锁方式：

• 加锁：lock table tableName read/write;
• 释放锁：unlock table;

需要注意的是，表锁的加锁也限制了同一个客户端链接的操作权限，如加了表级读锁（lock table user read），那么在同一个客户端链接中在释放表级读锁以前，对同一张表（user 表）也只能进行读操作，无法进行写操作，而其他客户端链接对该表（user 表）只能进行读操作，无法进行写操作。

如加了表级写锁（lock table user write），在同一个客户端链接中可对表进行读写操作，而其他客户端链接既无法进行读操作也无法进行写操作。

1.2.2 元数据锁

第二种表级锁是元数据锁（MDL, Meta Data Lock），元数据锁会在客户端访问表的时候自动加锁，在客户端提交事务时释放锁，它防止了以下场景出现的问题：

如上表，sessionA 开启了一个事务，并进行一次查询，在这之后另外一个客户端 sessionB 给 user 表新增了一个 birthday 字段，然后 sessionA 再进行一次查询，如果没有元数据锁，就可能会出现在同一个事务中，前后两次查询到的记录，表字段列数不一致的情况，这显然是需要避免的。

DDL 操作对表加的是元数据写锁，对其他事务的元数据读写锁都不兼容；DML 操作对表加的是元数据读锁，可与其他事务的元数据读锁共享，但与其他事务的元数据写锁不兼容。

1.2.3 意向锁

第三种表级锁是意向锁，它表示事务想要获取一张表中某几行的锁（共享锁或排它锁）。

意向锁是为了避免在表中已经存在行锁的情况下，另一个事务去申请表锁而扫描表中的每一行是否存在行锁的系统消耗。

例如，sessionA 开启了一个事务，并对 id=5 这一行加上了行级排它锁，此时 sessionB 将对 user 表加上表级排它锁（只要 user 表中有一行被其他事务持有读锁或写锁即加锁失败）。

如果没有意向锁，sessionB 将扫描 user 表中的每一行，判断它们是否被其他事务加锁，然后才能得出 sessionB 的此次表级排它锁加锁是否成功。

而有了意向锁之后，在 sessionB 将对 user 表加锁时，会直接判断 user 表是否被其他事务加上了意向锁，若有则加锁失败，若无则可以加上表级排它锁。

意向锁的加锁规则：

• 事务在获取行级共享锁（S锁）前，必须获取表的意向共享锁（IS锁）或意向排它锁（IX锁）
• 事务在获取行级排它锁（X锁）前，必须获取表的意向排它锁（IX锁）

1.2.4 自增锁

第四种表级锁是自增锁，这是一种特殊的表级锁，只存在于被设置为 AUTO_INCREMENT 自增列，如 user 表中的 id 列。

自增锁会在 insert 语句执行完成后立即释放。同时，自增锁与其他事务的意向锁可共享，与其他事务的自增锁、共享锁和排它锁都是不兼容的。

1.3 行锁

行锁是由存储引擎实现的，从行锁的兼容性来看，InnoDB 实现了两种标准行锁：共享锁（Shared Locks，简称S锁）和排它锁（Exclusive Locks，简称X锁）。

这两种行锁的兼容关系与上面元数据锁的兼容关系是一样的，可以用下面的表格表示。

而从行锁的粒度继续细分，又可以分为记录锁（Record Lock）、间隙锁（Gap Lock）、Next-key Lock。

1.3.1 记录锁（Record Lock）

我们一般所说的行锁都是指记录锁，它会把数据库中的指定记录行加上锁。

假设事务A中执行以下语句（未提交）：

begin;update user set name=&＃39;达闻西&＃39; where id=5; 

InnoDB 至少会在 id=5 这一行上加一把行级排它锁（X锁），不允许其他事务操作 id=5 这一行。

需要注意的是，这把锁是加在 id 列的主键索引上的，也就是说行级锁是加在索引上的。

假设现在有另一个事务B想要执行一条更新语句：

update user set name=&＃39;大波浪&＃39; where id=5; 

这时候，这条更新语句将被阻塞，直到事务A提交以后，事务B才能继续执行。

2. 加锁规则

不知道大家有没有注意到，我在行锁部分描述记录锁、间隙锁加锁的具体记录时，用的是「至少」二字，并没有详细说明具体加锁的是哪些记录，这是因为记录锁、间隙锁和 Next-key Lock 的加锁规则是十分复杂的，这也是本文主要讨论的内容。

关于加锁规则的叙述将分为三个方面：唯一索引列、普通索引列和普通列，每一方面又将细分为等值查询和范围查询两方面。

需要注意的是，这里加的锁都是指排它锁。

在开始之前，先来回顾一下示例表以及表中可能存在的行级锁。

mysql> select * from user;
+----+--------+------+| id | name   | age  |
+----+--------+------+|  5 | 重塑   |    5 |
| 10 | 达达   |   10 |
| 15 | 刺猬   |   15 |
+----+--------+------+3 rows in set (0.00 sec) 

表中可能包含的行级锁首先是每一行的记录锁——(5,重塑,5),(10,达达,5),(15,刺猬,15)。

假设 user 表的索引值有最大值 maxIndex 和最小值 minIndex，user 表还可能存在间隙锁(minIndex,5),(5,10),(10,15),(15,maxIndex)。

共三个记录锁和四个间隙锁。

2.1 唯一索引列等值查询

首先来说唯一索引列的等值查询，这里的等值查询可以分为两种情况：命中与未命中。

当唯一索引列的等值查询命中时：

上表中 sessionB 的执行结果是除了 id=5 行的更新语句被阻塞，其他语句都正常执行。

sessionB 中的 insert 语句是为了检查间隙锁，update 语句是为了检查记录锁（行锁）。执行结果表明 user 表的所有间隙都没有被上锁，记录锁中只有 id=5 这一行被上锁了。

这个边界值与唯一索引列范围查询的原理是一样的，可以参照上文所述来理解，这里不多加赘述了。

《MySQL实战45讲》的作者丁奇认为这是一个 BUG，但并未被官方接收，如果要深究这个边界值的原理，可能就需要看 MySQL 的源码了。

3. 温故知新

1. MySQL 中的锁按粒度来分可以分为几种？分别描述一下。
2. MySQL 中行锁的加锁规则？
3. 请说出下面几条 SQL 的加锁区域：

select * from user where age=10 for update;select * from user where age>=10 and age<11 for update;select id from user where age>=10 and age<11 for update; 

更多相关免费学习推荐：mysql教程(视频)

以上就是我所理解的MySQL五：锁及加锁规则的详细内容，更多请关注 第一PHP社区 其它相关文章！