MySQL锁概述

作者 | 冯凯
来源 | 凯哥的Java技术活（ID：sugelakai）

MySQL锁分类

• S: Shared Locks，共享锁
• X: Exclusive Locks，排他锁(独占锁)
• Intention Locks，意向锁
• Record Locks，记录锁
• Gap Locks，间隙锁
• Next-Key Locks，记录锁和这条记录之前的间隙锁
• Insert intention Locks，插入意向锁

测试环境

MySQL 8.0版本

创建数据库：

CREATE DATABASE my_test

创建一个只有id字段的表t

CREATE TABLE t (id INT, PAIMARY KEY(id));

统一使用事务自动提交;

show variables like 'autocommit';

确认结果：

统一使用可重复读的隔离级别：

show variables like '%isolation%';

如果不一致，请修改，这也是MySQL默认的自动提交和隔离级别设置。

锁的位置

在介绍具体的锁之前，首先要注意MySQL是在不同的层有不同的锁。

应该见过MySQL的结构图：

那么应该知道MySQL有Server层和Engine层。

怎么查看每个层的锁?

Server层使用

SELECT * FROM performance_schema.metadata_locks \G;

Engine层使用

SELECT * FROM performance_schema.data_locks \G;

例如，当我们执行如下SQL，

LOCK TABLES t READ;

然后查看Server层的锁。

select * from performance_schema.data_locks \G;

可以看到在 LOCK TABLES t READ后，在server层有一个SHARED_READ_ONLY锁。

而在Engine层却没有锁。

知道了怎么查询server和engine的锁后，我们就可以开始我们的实验了!

Server Table Locks

Server Table Locks(Server 层 表级别锁)

执行如下三条SQL语句

按照我们预期，执行BEGIN后，显式开启了一个事务，然后我们执行了LOCK TABLES t READ，那么事务应该就会为t表加一个对应的锁。

但是我们查询，却发现是空的。

更诡异的是连事务都没有了!

(查询事务)

其实，这是因为Server层在执行LOCK TABLES 之前 自动进行一次COMMIT。

可以认为“LOCK TABLE + UNLOCK TABLE ”和“BEGIN + COMMIT”是互斥的。也就是说，LOCK TABLE 会自动结束掉事务，开始事务会自动UNLOCK TABLE。

InnoDB Table Locks

InnoDB Table Locks(InnoDB引擎的表级别锁)

由于lock tables会导致自动提交，而begin又会导致unlock tables。???

所以我们采用关闭自动提交的方式进行测试Table Locks。

1.关闭自动提交

SET AUTOCOMMIT = 0;

2.执行锁表语句

LOCK TABLE t READ, t1 WRITE

3.查看server锁

SELECT OBJECT_NAME,LOCK_TYPE,LOCK_STATUS,OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA='my_test' AND OBJECT_TYPE="TABLE";

结果如下：

4.查看Engine锁

SELECT 
         ENGINE_TRANSACTION_ID as trx_id,
         OBJECT_NAME as `table`,
         INDEX_NAME,
         LOCK_DATA,
         LOCK_MODE,
         LOCK_STATUS
       FROM performance_schema.data_locks;

结果如下：

可以看到，我们得到了两种锁，Server级别的和Engine级别的。

我们关注点主要放在innodeb 引擎的锁：S , X

其中S 就是 Shared Locks，是一个共享锁

X 就是 Exclusive Locks，是一个排他锁

意向锁

Intention Locks(意向锁)

另一种更常见的为innodb engine表上锁的方式为

开启事务

BEGIN

插入数据

INSERT INTO t VALUES (100);

查询Innodb的表锁

SELECT 
         ENGINE_TRANSACTION_ID as trx_id,
         OBJECT_NAME as `table`,
         INDEX_NAME,
         LOCK_DATA,
         LOCK_MODE,
         LOCK_STATUS
       FROM performance_schema.data_locks;

结果如下：

可以看到在Innodb引擎上，有一个IX 锁，也就是Intention Exclusive。在Server层也有一个SHARED_WRITRE。

在这个事务中，我们继续操作，查询另一个表。

SELECT * FROM t1;

再次查询Innodb的表锁，发现没有任何变化。

只是会在Server层有锁。也就是说普通的SELECT语句不会为Engine层加锁，只会在Server层加一个SHARED_READ锁。这条查询在Server层就被保护了起来。

但是如果用SELECT ... FOR SHARE/UPDATE的话就不一样了。

继续执行如下SQL语句

SELECT * FROM t1 FOR SHARE

就会发现Innodb为t1加了IS锁，Intention shared locks.

表锁先说到这里，我们暂时梳理一下各个锁之间的关系

 

 

• 当表存在X锁时，所有其他的锁都需要等待。
• 当存在S锁时，X，IX需要等待，S与其理解为共享，倒不如理解为保护读。只允许共享读，其他的有关X的，全都排斥掉。
• 当存在IX锁时，X和S需要等待
• 当存在IS锁时，X需要等待

Record Locks

Record Locks(记录级别锁)

Innodb engine有许多关于record locks。

例如一个表的记录为10,20,30。

记录就是如下，每条记录以及之前的间隙。

• S,REC_NOT_GAP。共享的行锁，不包含间隙
• X,REC_NOT_GAP。独占的行锁，不包含间隙
• S,GAP。共享锁，某条记录之前的间隙
• X,GAP。独占锁，某条记录之前的间隙
• S。可以认为是S,REC_NOT_GAP + S,GAP
• X。可以认为是X,REC_NOT_GAP + X,GAP
• X,GAP,INSERT_INTENTION。间隙插入意向锁，某行之前的的间隙。能够与其他插入锁兼容
• X,INSERT_INTENTION。插入意向锁。可以理解为锁住最后一条记录之后的间隙。

他们的兼容关系如下：

 

 

如果某个记录存在S,REC_NOT_GAP，那么就是这行记录不能被修改，可以共享读。所以他与该记录的X互斥。

如果某个记录存在X,REC_NOT_GAP，那么就是这行记录被独占。所以与任何其他的操作本记录的锁互斥。

综合举例

假如我们在一个事务中执行如下SQL语句，那么现在的Server和Engine都存在什么锁呢?

BEGIN;
SELECT * FROM t FOR SHARE;
DELETE FROM t WHERE id=6;
INSERT INTO t VALUES (4);

首先是查询

SELECT * FROM t FOR SHARE;

按照我们猜想，应该是Server层有一个SHARED_READ锁，然后每个记录都有一个S锁。

然后DELETE，首先肯定是在表上加一个IX锁，声明下面有工人在干活了(删除记录)，然后记录级别的话应该是有一个X，NOT GAP的锁的，因为我们是针对这个记录本身删除的，应该会有一个X锁，不包括间隙。

然后INSERT，首先发现表上有IX锁，OK，应该是有一个和DELETE差不多，怎么着也得有一个X锁。

结果如下：

发现结果有点不对劲。

S倒是比较容易解释，因为我们执行了SELECT * FROM t FOR SHARE

删除的记录上有X REC_NOT_GAP，也说的通。

为什么新插入的记录(5)是S,GAP锁呢?

这是因为，在插入之前，5后面本来是200，本来就有空隙，是S,GAP锁，插入5之后，5之前肯定也要产生新的间隙，那么就是从200继承过来的S,GAP了。

那么5这条记录本身为什么没有任何的锁呢?

其实在添加完后，会更新该记录的DB_TRX_ID，暂时理解成为这个事务对这个记录的声明的锁。

TODO

本文测试了几波关于Server和Engine锁，介绍了如何查看锁，以及锁的互斥性。篇幅原因先到这里。接下来的文章，会结合隔离级别结合理解下MySQL的隔离级别和锁是怎么组合工作的。