一文搞懂数据库锁知识

• 数据库锁知识(INNODB)
  • 库锁
  • 表锁
    • MDL锁
    • 意向锁
    • 在线DDL的效率问题
    • 锁升降级机制
  • 行锁
    • 四种隔离级别
    • 行锁的分类
    • 行记录锁（Record Locks）
    • 间隙锁(Gap Lock)
    • 临键锁(Next Key Lock)
  • AUTO-INC Locking
    文章已收录我的仓库：Java学习笔记与免费书籍分享

数据库锁知识(INNODB)

库锁

库锁主要分为两类：

• FTWRL（Flush tables with read lock），将数据库设置为只读状态，当客户端异常断开后，该锁自动释放，官方推荐使用的库锁。
• 设置global全局变量，即set global readonly=true，同样是将数据库设置为只读状态，但无论何时，数据库绝不主动释放锁，即时客户端异常断开连接。

Flush tables with read lock 具备更安全的异常处理机制，因此建议使用Flush tables with read lock而不是修改全局变量。

表锁

约定：为了方便，文章将SELECT语句归于MDL语句。

MDL锁

MDL锁，是一种表锁，也称元数据锁(metadata lock)，元数据锁是server层的锁，MYSQL下的所有引擎几乎都提供表级锁定，表级锁定分为表共享读锁（S锁）与表独占写锁（X锁）。

在我们执行DDL语句时，诸如创建表、修改表数据等语句，我们都需要申请表锁以防止DDL语句之间出现的并发问题，只有S锁与S锁是共享的，其余锁都是互斥的，这种锁不需要我们显示的申请，当我们执行DDL语句时会自动申请锁。

当然我们也可以显示的申请表锁：

• LOCK TABLE table_name READ; 使用读锁锁表，会阻塞其他事务修改表数据，而对读表共享。
• LOCK TABLE table_name WRITE; 使用写锁锁表，会阻塞其他事务读和写。

MDL锁主要用于解决多条DDL语句之间的并发安全问题，但除了DDL与DDL之间的问题，DDL与DML语句之间也会出现并发问题，因此INNODB下，还会存在一种隐式的上锁方式。

意向锁

事实上，为解决DDL和DML之间的冲突问题，在INNODB下，数据库还会为每一条DML语句隐式地加上表级锁，这并不需要我们显示的指定。

来看看数据库为什么这么做，我们假设事务A执行一条DDL语句ALTER TABLE test DROP COLUMN id;，而事务B正在执行两条DML语句SELECT * FROM，如果对数据库比较了解，你应该很快的就会发现其中存在一些并发安全问题：

事务A	事务B
BEGIN	BEGIN
SELECT * FROM test;
…	ALTER TABLE test DROP COLUMN id;
SELECT * FROM test;
COMMIT	COMMIT

这就产生了冲突现象，事务A执行的两条查询语句不一致，违背了事务的一致性，而为了解决这个问题，MYSQL引入了意向锁，官方将这种锁分为意向共享锁(IS锁)和意向排他锁(IX锁)，意向锁是由DML操作产生的，请注意区分与上文所说的S锁与X锁，意向共享锁表明当前表上存在某行记录持有行共享锁(区别表S锁)，意向排他锁表明当前表上存在某行记录持有行排他锁(区别表X锁)。

每执行一条DML语句都要申请意向锁，意向锁的类型是由行锁的类型决定的，例如SELECT语句会申请行共享锁，同时也会申请意向共享锁，UPDATE会申请意向排他锁，同一个表内的DML语句不会冲突，这意味着意向锁都是兼容的，要注意意向锁是由DML语句产生的，而DDL语句不会申请意向锁。

如上文说所，DDL语句申请的只是普通的X锁或S锁，但它必须根据规则要等待IX锁或IS锁释放。

表锁兼容性规则如下图所示：

现在可以正常执行了，事务B必须要等到事务A提交后才可执行：

事务A	事务B
BEGIN	BEGIN
SELECT * FROM test;(申请IX锁)
…	ALTER TABLE test DROP COLUMN id;(申请X锁，需要等待IX释放)
SELECT * FROM test;(重入)
COMMIT(释放IX锁)	COMMIT(释放X锁)

这种隐式的上锁是在MYSQL5.5之后才引入的，在之前的版本中，执行DML操作并不会对表上锁，因此执行DDL操作不仅需要申请X锁，还需要遍历表中的每一行记录，判定是否存在行锁，如果的确存在，则放弃操作。因此在以前的版本中，是不支持在线DDL的，要想执行DDL操作就必须停止关于该表的一切活动，除此之外，执行DDL操作需要遍历所有行，这也是非常低效的。

有了这种隐式MDL锁之后，解决了DML与DDL操作之间的冲突，在线DDL变得可能，同时无须遍历所有行，只需要申请表锁即可。

所以说，这种隐式的表锁，解决了DML与DDL操作之间的冲突，使得数据库可以支持在线DDL，同时增加了执行DDL的效率。

注意一个包含关系，IX、IS、X、S锁均属于MDL锁。

在线DDL的效率问题

尽管在MYSQL5.5后提出隐式MDL锁后，在线DDL操作变得可能，但我们不得不来思考它的效率问题，考虑下图：

有三个事务，第一个事务执行DQL语句同时申请IS锁；第二个事务执行DDL语句同时申请X锁，X锁是排他的，因此必须等待事务一IS锁的释放，事务二被堵塞；事务三同样执行DQL语句，但由于写锁的优先级高于读锁，事务三不得不排在事务二的后面，事务三被堵塞(不只是数据库中，绝大多数场景下都是写锁优先)；如果后面有N个DQL语句，那么这N个语句都会被堵塞，而如果没有事务二，由于读是共享的，所有事务都不会堵塞，在线DDL使得整体效率变得异常低下。

这种现象产生的原因主要是使用了隐式MDL锁和写锁优先原则，因此我们很难根治这种现象，只能去缓解，MYSQL5.6版本后提出锁升降级机制。

锁升降级机制

在上述示例中，事务二以后的事务都必须等待事务二执行完毕，而事务二是一种DDL操作，DDL操作涉及到文件读写，会写REDO LOG并发起磁盘IO，这是非常缓慢的，既然无法改变写者优先的原则，MYSQL试图加快DDL操作的执行以减少后续事务的等待，但DDL操作本身已经很难再做改变了，MYSQL想到了一种曲线救国的方式——让它暂时放弃对写锁持有！

具体的流程为：

1. 事务开始，申请表级写锁；
2. 降级为表级读锁，使得后续DQL操作不被堵塞(DML仍被堵塞);
3. 执行具体更改。
4. 升级为写锁；
5. 事务提交，释放锁；

当DDL事务由写锁降级时，后续的DQL操作得以运行，提高了效率。

要理解这一升一降，当事务开始时，DDL操作由写锁降级为读锁时，由于读锁与写锁排斥，可以保证DDL更改表数据时不会有任何其他写表操作，避免了并发问题；当事务提交时，读锁升级为写锁，又可以保证同一时刻没有其他读表操作，即避免了读写不一致问题。

但假如有过多的读者，使得该锁无法从读锁升级为写锁，就可能存在饿死该DDL操作的问题，这是为了提高性能而带来的弊端。

行锁

行锁是对针对某一行记录上锁，是更细粒度的一种锁，在MYSQL中，只有INNODB执行行锁，而其他的引擎不支持行锁。

INNODB下实现了两种标准的行级锁(区别表锁中的S锁与X锁，这里的锁是行锁！)：

• 共享锁(S锁)，允许事务读一行数据。
• 排他锁(X锁)，允许事务修改或删除一行数据。

行锁在INNODB下是基于索引实现的，当索引未命中时，任何操作都将全表匹配查询，行锁会退化为表锁，数据库会先锁表，再执行全表检索。

因此要注意所有的行锁都是在索引上的。

四种隔离级别

1. 读未提交(Read uncommitted)。即事务可以读取其他事务还未提交的数据，事务完全是透明的，这种级别下连脏读都无法避免。
2. 读已提交(Read committed)。这种隔离级别下，事务可以通过MVVC读取数据而无须等待X锁的释放，但事务总是读取最新版本的记录，例如事务A正在修改某行数据，事务B读取两次，第一次读取发现A正在修改，数据被上锁，因此读取上一个版本的数据，此时A事务修改完毕并提交，事务B开始第二次读取，它总是会尝试读取最新版本的数据，于是事务B第二次读取了事务A修改后的数据，事务B两次读取不一致，发生了不可重复读问题。
3. 可重复读(Repeatable read)。INNDOB下默认的级别，与读已提交类似，唯一的区别是这种隔离级别下，在一个事务内，总是会读取一致的记录版本，一开始读什么，后面就读什么，不会发生不可重复读问题。起初存在幻读问题，后来引入Next Key Lock解决了这个问题。
4. 可串行化(Serializable)。禁止MVVC功能，不会出现问题，但效率低。

隔离级别	脏读	不可重复读	幻读（Phantom Read）
读未提交（Read uncommitted）	可能	可能	可能
读已提交（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	不可能
可串行化（Serializable）	不可能	不可能	不可能

行锁的分类

INNODB下有三种行锁，根据不同的条件使用不同的锁。

为了统一，我们执行如下SQL语句，建立test表：

drop table if exists test;
create table test(
	a int,
    b int,
    primary key(a),
    key(b)
)engine=InnoDB charset=utf8;

insert into test select 1, 1;
insert into test select 3, 1;
insert into test select 5, 3;
insert into test select 7, 6;
insert into test select 10, 8;

a(主索引)	b(普通索引)
1	1
3	1
5	3
7	6
10	8

行记录锁（Record Locks）

记录锁锁住唯一索引上的行记录，请注意这里是锁住记录而不是锁住索引，这意味着你无法绕开索引去访问记录。

行记录锁何时生效？仅当查询列是唯一索引等值查询时(等值查询就是where xxx = xxx)，next key lock会降级为行记录锁。

为什么查询列是唯一索引等值查询时，可以使用行记录锁呢？其实很简单，由于唯一索引列仅对应唯一的行记录，当我们执行等值查询时，已经确保了我们只会访问这一条行记录，因此对该记录上锁，使得其他操作无法无法影响该记录，并且插入新记录的操作会由于主键冲突而被拒绝，幻读问题也不会产生，并发安全得以保证。

要注意MYSQL默认条件下是使用next key lock的，而仅仅在条件满足时降级为行记录锁。而使用Record Lock的冲要条件是查询的记录是唯一的。

其他条件下难道不可以使用行记录锁吗？答案是不可以！其他任意条件，我们都无法满足行记录锁的重要条件。

如果不是等值查询，那么必然会出现多个结果，在RR级别下，我们来看一个范围查询，考虑事务A与事务B：

事务A	事务B
BEGIN;	BEGIN;
SELECT * FROM test WHERE id >= 1 FOR UPDATE;(上X锁，禁止读取快照)	…
…	INSERT INTO test SELECT 101, 5;
SELECT * FROM test WHERE id >= 1 FOR UPDATE;	…
COMMIT;	COMMIT;

试想，如果仅锁住一条记录，事务A前后两次将读出不同的结果，第二次读取将多一条记录，即幻读现象！因此，我们必须要锁住一个范围。

再考虑非唯一索引下的等值查询，想想为什么这种情况下不能加行记录锁。

其实也很简单，回到我们之前说的，行记录锁锁的是一条记录而不是索引值，例如语句SELECT * FROM test WHERE b = 1 FOR UPDATE;，该语句对应的是两条记录，行记录锁只能锁住一条记录，而另一条记录却可以随意修改，并且可以增加其他记录，产生幻读！这是冲突的，因此无法使用行记录锁，请再次理解锁记录数据而非锁条件值这句话。

间隙锁(Gap Lock)

间隙锁锁住的是一个范围，但不会锁住记录本身，即锁条件值而非锁记录数据，这是与行记录锁相反的，我们等到研究临键锁时再所说何时使用间隙锁，因为间隙锁是在条件符合时由临键锁退化而成的。

临键锁(Next Key Lock)

临键锁与间隙锁仅在RR(可重复读)隔离级别下生效，目的是为了解决幻读问题，而RC级别下连不可重复读都无法解决，更别说幻读问题了。

临键锁也是一个范围锁，但与间隙锁不同，临建锁不仅会锁住一个范围，还会锁住记录本身，锁住记录本身采用的是行记录锁，是基于唯一索引的锁，可将临键锁看作是间隙锁和行记录锁的结合。

临键锁采用临键锁算法(Next Key Locking)，对一个范围执行左开右闭的封锁，闭区间意味着该记录也被锁住。

临键锁规则

1. 对非唯一索引查询的上一区间与下一区间上临键锁，对区间右侧的值采用行记录锁，封锁非唯一索引对应的唯一索引的行记录，此时区间符合左开右闭原则。

2. 对于区间右侧的值，如果不在查询范围内，则将该区间降级为间隙锁，不再封锁行记录。

3. 如果是唯一索引下的等值查询，则降级为行记录锁。

来看几个示例以理解临键锁，以及为什么能避免幻读。

我们首先来看看普通索引下的等值搜索，我们通过开启多个终端模拟并发。

假设事务A执行下列语句：

BEGIN; 
SELECT * FROM test WHERE b = 1 FOR UPDATE;
//不提交，模拟并发。

同时开启另一个终端，事务B执行下列语句：

BEGIN;
SELECT * FROM test WHERE a = 3 FOR UPDATE;
SELECT * FROM test WHERE a = 5 FOR UPDATE;
INSERT INTO test SELECT -1, -1;

结果是什么呢？除了SELECT * FROM test WHERE a = 7 FOR UPDATE;正常执行外，其余两句都被堵塞。

让我们分析一下这个结果，事务A执行SELECT * FROM test WHERE b = 1 FOR UPDATE;，根据规则1，我们对普通索引b的左右区间上临键锁，此时b在 (负无穷，1] 和 (1，3] 内上锁；根据规则2，由于b=3与我们的查询b=1不符，因此右区间退化，此时锁范围为 (负无穷，1] 和 (1，3) ；

SELECT * FROM test WHERE a = 3 FOR UPDATE;语句查询 a = 3的记录，但根据临建锁规则，对 b = 1对应的唯一索引上行记录锁，因此 a = 1 & a= 3两条记录都被上锁，该语句堵塞。

SELECT * FROM test WHERE a = 5 FOR UPDATE;语句查询 a = 5的记录，由于右区间降级为间隙锁，b = 3 不在锁记录，即该记录为被上锁，查询成功。

INSERT INTO test SELECT -1, -1;语句插入一条记录，但由于 b 在 (负无穷，1)处存在间隙锁(等于1处是行记录锁)，插入记录b=-1在范围内，被锁住，堵塞。

你可能会觉得如果插入一条 b = 1的语句呢？例如INSERT INTO test SELECT -1, 1;，由于间隙锁锁住的是(负无穷，1)，而b等于1的所有记录是被行记录锁住，这看上去插入一条 b = 1的语句没有任何问题，产生幻读。

但实际上，这仍然是被间隙锁锁住的，要理解这一点，必须从B+树层面去解释，由于b=1两边范围均被锁住，而插入算法中定位叶子节点是一定需要用到左右区间的，因此插入被堵塞，从而避免了幻读。

为何等值查询语句也需要范围锁？前面已经解释过了，非唯一索引等值查询会查询多条语句，行记录锁只能锁一条，如果每条都上行记录锁，效率太低了，因此需要一个范围锁。

类似地，范围语句也会遵守临键锁规则，例如语句select * from test where b >= 1;，最终锁的范围为(负无穷, 1] & (1, 正无穷)；

事务A	事务B
BEGIN;	BEGIN;
SELECT * FROM test WHERE id >= 1 FOR UPDATE;(上范围锁)	…
…	INSERT INTO test SELECT 101, 5;(b = 5落入范围内，堵塞等待)
SELECT * FROM test WHERE id >= 1 FOR UPDATE;	…
COMMIT;	COMMIT;

此时幻读现象便不再发生。

AUTO-INC Locking

自增长锁，在InnoDB引擎中，每个表都会维护一个表级别的自增长计数器，当对表进行插入的时候，会通过以下的命令来获取当前的自增长的值。

SELECT MAX(auto_inc_col)  FROM user FOR UPDATE;

插入操作会在这个基础上加1得到即将要插入的自增长id，然后在一个事务内设置id。

为了提高插入性能，自增长的锁不会等到事务提交之后才释放，而是在相关插入sql语句完成后立刻就释放，这也导致了一些事务回滚之后，id不连续。

由于自增长会申请写锁，尽管不用等到事务结束，但仍然降低了数据库的性能，5.1.2版本后InnoDB支持互斥量的方式来实现自增长，通过互斥量可以对内存中的计数器进行累加操作，比AUTO-INC Locking要快些。