MySQL8表级锁

MySQL 8.0.34

MySQL8表级锁

MySQL中表级锁主要有表锁（注意区分表级锁）、意向锁、自增锁、元数据锁。

表锁

表锁是一种表级锁，他是一种显式锁，通常需要用户手动加锁和解锁（也存在特殊情况，比如行级锁升级为表级别），表锁分为两种，一种是读锁（S锁），一种是写锁（X锁），只能在一个会话中加锁和解锁，不能获取其他会话的锁以及释放其他会话的锁。表锁不仅仅能锁定表，也能锁定视图。

语法说明

加锁和解锁的语法如下：

# 加锁
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
    
# 锁类型
lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}
# 解锁
UNLOCK TABLES

LOCOL：他的作用是在满足MySIAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。

LOW_PRIORITY ：代表低优先级，已过期，后续版本中会删除，因为WRITE锁通常具有更高的优先级 比锁，以确保尽快处理。

表锁的兼容性如下：

当前锁/其他锁	读锁	写锁
读锁	兼容	互斥
写锁	互斥	互斥

也就是说，多个客户端可以对同一个表同时加读锁，但是不能同时加写锁，或者混合读写锁，举例说明：

---

兼容性验证

读锁和读锁

打开两个会话，同时对一个表加上表读锁。

查看锁状态，通过show open tables from d1查看数据库d1的表的锁状态。

因为两个会话都对表t1加了表读锁，所以In_use字段的值为2。

当然也可以同时对多个表进行加锁

使用unlock tables解锁。

---

读锁和写锁

查看锁状态：

可以看到只加上了一把锁，并且无论先加表读锁还是表写锁都没有关系，后加的锁都无法成功加锁，一直阻塞中，只有当之前的锁解锁后，后面的加锁语句才能成功。

使用unlock tables解锁。

---

写锁和写锁

可以确认写锁和写锁是排斥的。

使用unlock tables解锁。

---

通过FLUSH语句加表锁

还有一种加表锁的方式，就是使用FLUSH TABLES tbl_name [, tbl_name] … WITH READ LOCK，此语句会首先获取一个指定表的排他锁，因此他会等待这些表的事务完成，然后刷新这些表的表缓存，重新打开表，最后获取表锁（读取），接下来再将元数据排他锁降级为元数据共享锁，那么其他会话就只能读取表而不能修改表。

上图中我使用FLUSH TABLES tbl_name [, tbl_name] … WITH READ LOCK语句对t1加了锁，然后我通过show open tables from t1查询了打开的表，是能查到结果的（此处不用管t2表，因为也没操作t2表。），我们知道flush的作用是清理缓存并关闭表，此处能查到恰巧说明了该语句又重新打开了表。

上面我们提到了元数据锁的概念，此处只需要知道它的作用是维护表元数据的一致性，为了避免DML与DDL冲突，保证读写的正确性。

无论使用LOCK TABLSES还是使用FLUSH TABLES tbl_name WITH READ LOCK都会加上元数据锁，我们查看下元数据锁的表查看下。

解锁方式

上面我们已经说明通过UNLOCK TABLES可以手动解锁，除了此种方式还有其他的解锁方式。

0. 断开会话

   会话断开会自动释放表锁，验证下（这里我通过写锁和写锁的互斥性来验证）。

   

   当会话1中断开MySQL的连接，会话2则不再阻塞，加锁成功。

1. 开启事务

   当前加了表锁，但是当开启事务的时候，会自动解锁。

   

元数据（MDL）锁

概念

元数据锁（Metadata Lock，简称MDL）是表级锁中的一种，MDL锁主要作用是维护表元数据的数据一致性，为了避免DML与DDL冲突，保证读写的正确性。

元数据锁不仅仅可以应用到表上，也可以应用到schemas、存储过程、函数、触发器、计划事件、表空间上。

隐式加解锁，无需用户控制，系统自动完成。

DDL、DML、DQL以及表级锁都会加元数据锁。

查看MDL锁

我们可以通过PERFORMANCE_SCHEMA中的metadata_locks查看锁情况

因为我们查询了metadata_locks表，所以系统自动加了元数据锁，查询也加锁吗？是的。

列解释：

列名	解释
OBJECT_TYPE	当前锁类型：取值有GLOBAL，SCHEMA，TABLE，FUNCTION，PROCEDURE，TRIGGER，EVENT，COMMIT，USER LEVEL LOCK，TABLESPACE，LOCKING SERVICE
OBJECT_SCHEMA	对象所在的SCHEMA名字
OBJECT_NAME	对象名称
LOCK_TYPE	锁的具体类型，取值有：INTENTION_EXCLUSIVE，SHARED，SHARED_HIGH_PRIO，SHARED_READ，SHARED_WRITE，SHARED_UPGRADABLE，SHARED_NO_WRITE，SHARED_NO_READ_WRITE，EXCLUSIVE，
LOCK_STATUS	锁的状态，取值有：PENDING，GRANTED，VICTIM，TIMEOUT，KILLED，PRE_ACQUIRE_NOTIFY，POST_RELEASE_NOTIFY

MDL锁种类

MDL锁的种类主要分为共享只读锁SHARED_READ_ONLY，共享写锁SHARED_NO_READ_WRITE，共享读锁SHARED_READ，共享写锁SHARED_WRITE，排他锁EXCLUSIVE。

有些奇怪呀？常见共享锁和排他锁的规则是读读兼容，读写、谢谢排斥嘛，这里为什么共享写锁？在MDL锁中这确实是比较特殊的存在，在MDL锁中，共享读锁（SHARED_READ）和共享写锁（SHARED_WRITE）确实是兼容的，他们跟排他锁EXCLUSIVE是互斥的。

DQL

DQL主要包含普通SELECT快照读，同时也可以针对SELECT加锁实现最新读。

首先看下SELECT快照读，会加什么样的元数据锁。

可以看到对于DQL快照读会加上元数据锁中的SHARE_READ锁，叫做元数据共享读锁。

再看下使用SELECT ... FROM TABLE FOR SHARE/UPDATE语句会加上什么锁。

FOR SHARE：

可以看到对于SELECT ... FOR SHARE语句加上了SHARE_READ锁，这跟不使用FOR SHARE是意向的效果。

FOR UPDATE：

对于SELECT ... FOR UPDATE语句，加上了元数据锁中的共享写锁（SHARED_WRITE）。

通过上面可以知道对于不同的DQL加的元数据锁的类型：

语句(DQL)	MDL锁类型	说明
SELECT、SELECT…[FOR SHARE	LOCK IN SHARE MODE]	共享读锁（SHARED_READ）
SELECT…FOR UPDATE	共享写锁（SHARED_WRITE）	对于加了排他锁的SELECT语句说，会加MDL共享写锁。

上面我们提出来为什么共享读和共享写是兼容的，到这里其实我们就能够理解了，比如针对一个表有如下两个语句操作。

0. SELECT * FROM t1 WHERE id = 1;
1. SELECT * FROM t1 WHERE id = 2 FOR UPDATE;

对同一个表id不同的两个语句执行了两种查询，每种查询都会创建元数据锁，但是两个SQL创建的锁是不同的。

---

表锁

给一张表加表锁的同时系统也会加上元数据锁。

加表级读锁的时候，系统会自动创建一个共享MDL读锁

---

加表级写锁的时候，系统会自动创建一个MDL写锁（SHARED_NO_READ_WRITE） 。

---

DML

看下DML中INSERT，UPDATE，DELETE语句会加什么样的MDL锁。

可以看到对于INSERT操作，系统加上了MDL共享写锁（SHARED_WRITE），验证完毕提交事务。

对于UPDATE操作，系统也是加上了MDL共享写锁（SHARED_WRITE），验证完毕提交事务。

对于DELETE操作，也是加上了MDL共享写锁（SHARED_WRITE），验证完毕提交事务。

总结：

语句种类（DML）	MDL锁类型
INSERT、UPDATE、DELETE	SHARED_WRITE

---

DDL

对于DDL语句，系统会自动加上MDL排他锁（EXCLUSIVE），此排他锁会阻塞所有的DQL、DML以及其他的DML。

---

总结：通过上面的例子我们可以得到如下结论。

SQL命令	锁类型
SELECT、SELECT…[FOR SHARE	LOCK IN SHARE MODE]SS	SHARE_READ
INSERT、UPDATE、DELETE、SELECT…FOR UPDATE	SHARE_WRITE	MDL共享写
ALTER TABLE、CREATE INDEX等DDL语句	EXCLUSIVE	MDL排他锁
LOCK TABLES 表明 READ/WRITE	SHARED_READ_ONLY/SHARED_NO_READ_WRITE	共享只读锁/共享只写锁

MDL锁类型兼容性

上面介绍了针对不同的SQL命令所加的MDL所类型，接下来查看各种锁类型的兼容性。

0. SHARE_READ和SHARE_READ
1. SHARE_READ和SHARE_WRITE
2. SHARE_READ和EXCLUSIVE
3. SHARE_READ和SHARED_READ_ONLY
4. SHARE_READ和SHARED_NO_READ_WRITE
5. SHARE_WRITE和SHARE_WRITE
6. SHARE_WRITE和EXCLUSIVE
7. SHARE_WRITE和SHARED_READ_ONLY
8. SHARE_WRITE和SHARED_NO_READ_WRITE
9. EXCLUSIVE和SHARED_READ_ONLY
10. EXCLUSIVE和SHARED_NO_READ_WRITE

---

SHARE_READ和SHARE_READ

SHARE_READ和SHARE_READ是兼容的，再查看下此时MDL锁情况。

---

SHARE_READ和SHARE_WRITE

可以看到未阻塞，再查看下MDL锁情况。

可以看到确实是有两个MDL锁。

SHARE_READ和EXCLUSIVE

查看锁情况：

可以看到MDL排他锁的状态是PENDING，所以SHARE_READ和EXCLUSIVE是互相排斥的。

SHARE_READ和SHARED_READ_ONLY

查看你下此时的加锁情况：

可以看到都获取到了锁，所以SHARE_READ和SHARED_READ_ONLY是兼容的。

SHARE_READ和SHARED_NO_READ_WRITE

查看下锁信息：

可以看到是状态是PENDING，所以SHARE_READ和SHARED_NO_READ_WRITE是排斥的。

SHARE_WRITE和SHARE_WRITE

查看MDL锁情况：

可以看到两个MDL共享读锁确实是兼容的。

SHARE_WRITE和EXCLUSIVE

查看下MDL锁情况：

从锁状态上看，也是EXCLUSIVE状态是PENDING，说明SHARE_WRITE和EXCLUSIVE是排斥的。

SHARE_WRITE和SHARED_READ_ONLY

查看MDL锁信息：

两图一直，处于阻塞状态，说明SHARE_WRITE和SHARED_READ_ONLY不兼容。

SHARE_WRITE和SHARED_NO_READ_WRITE

查看MDL锁信息：

同样是PENDING状态，说明SHARE_WRITE和SHARED_NO_READ_WRITE不兼容。

EXCLUSIVE和SHARED_READ_ONLY

查看MDL锁信息：

说明 EXCLUSIVE和SHARED_READ_ONLY是不兼容的。

EXCLUSIVE和SHARED_NO_READ_WRITE

查看下MDL锁情况：

PENDING状态印证了上面的alter xxx语句阻塞，表明EXCLUSIVE和SHARED_NO_READ_WRITE不兼容。

兼容性总结

通过上面的测试，我们可以得出如下结论：

语句种类	MDL锁	兼容性说明
SELECT、SELECE…[FOR SHARE	LOCK IN SHARE MODE]	SHARE_READ
INSERT、DELETE、UPDATE、SELECT…FOR UPDATE	SHARE_WRITE	与SHARE_READ和SHARE_WRITE兼容，与EXCLUSIVE互斥
表锁（LOCK TABLES 表名 READ/WRITE）	SHARE_READ_ONLY / SHARE_NO_READ_WRITE	SHARE_READ_ONLY 与SHARE_READ兼容，与SHARE_WRITE互斥； SHARE_NO_READ_WRITE与SHARE_READ_ONLY 和SHARE_WRITE都互斥。
DDL (ALTER…、CREATE…等)	EXCLUSIVE	与所有MDL锁互斥。

意向锁

概念

意向锁是另外一种表级锁，为了避免DML语句在执行的时候行锁与表锁冲突而设计的意向锁，通过意向锁使得在加表锁的时候无需检查每行数据是否加锁。

假设有如下一种表，已经在id=3的记录上有锁了。

此时如果想给表加上表级锁，就需要循环整个表记录，对于上面的表需要扫描3次才能获取到表内数据锁情况，如果表的数据两很大，则扫描次数也会很大，这无疑降低了效率。

所以MySQL设计：当我们执行DML的时候，同时给表加上一个意向锁。

这样在加表级锁的时候，发现有意向锁，就可以根据策略绝对是否能够加锁，无需扫描表数据。

加锁方式

意向锁无需认为手动加锁以及解锁，他是一种隐式锁，由MySQL自己控制。

意向锁表信息

意向锁的信息存储在SCHEMAPERFORMANCE_SCHEMA.data_locks下。

上图中我开启事务，并执行select * from t1 where id = 1 for share语句，该语句会自动加上一个意向共享锁（IS）。

也会加上一个行级锁中的记录锁。

data_locks表的列信息如下：

列名	说明
ENGINE	存储引擎名字，比如INNODB
ENGINE_LOCK_ID	存储引擎持有或者请求到的锁的ID
ENGINE_TRANSACTION_ID	事务ID
THREAD_ID	创建锁的会话线程ID
EVENT_ID	引起锁的时间ID
OBJECT_SCHEMA	锁定表的SCHEMA名
OBJECT_NAME	锁定的表名
PARTITION_NAME	分区名
SUBPARTITION_NAME	子分区名
INDEX_NAME	锁定的索引名
OBJECT_INSTANCE_BEGIN	锁的内存地址
LOCK_TYPE	锁的类型，依赖存储引擎，在INNODB中，TABLE代表表级锁，RECORD代表行级锁
LOCK_MODE	锁模式，依赖存储引擎，在INNODB中，取值主要有 S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN，其中S代表共享锁、X代表排他锁，GAP代表间隙锁等。
LOCK_STATUS	锁状态，依赖存储引擎，在INNODB中，GRANTED代表获取到了锁（持有锁），WAITING等待。
LOCK_DATA	锁关联数据（如果没有则是NULL），在INNODB中，如果是一个行级锁（LOCK_TYPE=RECORD）则显示一个具体的值，否则显示NULL。如果是主键索引上的锁则值为主键。当锁是在辅助索引上时，则显示辅助索引的值，并附加上主键值。

详解

---

SELECT…FOR SHARE

查看锁情况：

SELECT…FOR SHARE会加上意向共享锁（IS），有了意向共享锁后能够使用lock tables t1 read加上表读锁吗？

并未阻塞，所以IS锁和表读锁是兼容的。

再看下IS和表写锁是否是兼容的：

从途中可以看到lock tables t1 write被阻塞住了，所有IS锁和表写锁是互斥的。

---

DML所加的意向锁都是IX锁（意向排他锁）

上图中执行了一个INSERT语句，查看下加的元数据锁类型：

可以看到加了一个意向排他锁（IX）。

接下来在看下他与表锁的兼容情况。

加表读锁，结果阻塞，无法成功，说明意向排他锁与表读锁是互斥的。

再看下能否加上表写锁。

可以看到也是无法加上表写锁的得到结论：

---

再看下UPDATE语句。

IX锁与表读锁互斥。

在看下IX锁与写锁兼容性：

IX锁与表写锁互斥。

综上可以得出如下结论：

意向共享锁（IS）与表读锁是兼容的，与表写锁是排斥的，意向排他锁（IX）与表锁（无论读锁还是写锁）都是互斥的，说明意向锁和表锁严格遵守读写锁的【读读共享】【读写排他】【写写排他】的特性。

自增锁

自增锁是表级锁中的一种，他是一种隐式锁，又系统自动加锁和解锁，该锁的唯一用处就是保证自动主键的数据一致性、准确性。

自增主键的表是很常见的一种表设计方式，比如：

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ID是自增的，数据库必须保证，多个会话添加数据的时候，ID不能出现重复的情况，这就是通过自增锁实现的。