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和加了共享锁的SELECT来说,都会创建MDL 共享读锁 |
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 | MDL共享读 |
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 | 与SHARE_READ和SHARE_WRITE兼容,与EXCLUSIVE互斥 |
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不能出现重复的情况,这就是通过自增锁实现的。