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可以手动解锁，除了此种方式还有其他的解锁方式。
-
断开会话
会话断开会自动释放表锁，验证下（这里我通过写锁和写锁的互斥性来验证）。
当会话1中断开MySQL的连接，会话2则不再阻塞，加锁成功。
-
开启事务
当前加了表锁，但是当开启事务的时候，会自动解锁。
元数据（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共享写锁。 |
上面我们提出来为什么共享读和共享写是兼容的，到这里其实我们就能够理解了，比如针对一个表有如下两个语句操作。
- SELECT * FROM t1 WHERE id = 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所类型，接下来查看各种锁类型的兼容性。
- SHARE_READ和SHARE_READ
- SHARE_READ和SHARE_WRITE
- SHARE_READ和EXCLUSIVE
- SHARE_READ和SHARED_READ_ONLY
- SHARE_READ和SHARED_NO_READ_WRITE
- SHARE_WRITE和SHARE_WRITE
- SHARE_WRITE和EXCLUSIVE
- SHARE_WRITE和SHARED_READ_ONLY
- SHARE_WRITE和SHARED_NO_READ_WRITE
- EXCLUSIVE和SHARED_READ_ONLY
- 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不能出现重复的情况，这就是通过自增锁实现的。
1368
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
