MySQL8表级锁

167 阅读12分钟

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锁通常具有更高的优先级 比锁,以确保尽快处理。

表锁的兼容性如下:

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

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


兼容性验证

读锁和读锁

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

image-20230720220956460

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

image-20230720221052789

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

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

image-20230721105804168

使用unlock tables解锁。


读锁和写锁

image-20230720221332999

image-20230720221434832

查看锁状态:

image-20230720221614025

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

使用unlock tables解锁。


写锁和写锁

image-20230720221849883

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

使用unlock tables解锁。


通过FLUSH语句加表锁

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

image-20230721110941973

上图中我使用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都会加上元数据锁,我们查看下元数据锁的表查看下。

image-20230721112531426

image-20230721112633809

解锁方式

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

  1. 断开会话

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

    image-20230721111852205

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

  2. 开启事务

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

    image-20230721112258652

元数据(MDL)锁

概念

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

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

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

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

查看MDL

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

image-20230721120103466

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

列解释:

列名解释
OBJECT_TYPE当前锁类型:取值有GLOBALSCHEMATABLEFUNCTIONPROCEDURETRIGGEREVENTCOMMITUSER LEVEL LOCKTABLESPACELOCKING SERVICE
OBJECT_SCHEMA对象所在的SCHEMA名字
OBJECT_NAME对象名称
LOCK_TYPE锁的具体类型,取值有:INTENTION_EXCLUSIVESHAREDSHARED_HIGH_PRIOSHARED_READSHARED_WRITESHARED_UPGRADABLESHARED_NO_WRITESHARED_NO_READ_WRITEEXCLUSIVE
LOCK_STATUS锁的状态,取值有:PENDINGGRANTEDVICTIMTIMEOUTKILLEDPRE_ACQUIRE_NOTIFYPOST_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快照读,会加什么样的元数据锁。

SELECT快照读,元数据锁演示

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

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

FOR SHARE

FOR SHARE

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

FOR UPDATE

FOR UPDATE

对于SELECT ... FOR UPDATE语句,加上了元数据锁中的共享写锁(SHARED_WRITE)。

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

语句(DQL)MDL锁类型说明
SELECT、SELECT...[FOR SHARELOCK IN SHARE MODE]共享读锁(SHARED_READ)对于普通SELECT和加了共享锁的SELECT来说,都会创建MDL共享读锁
SELECT...FOR UPDATE共享写锁(SHARED_WRITE)对于加了排他锁的SELECT语句说,会加MDL共享写锁。

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

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

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


表锁

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

image-20230721122647389

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


image-20230721122858729

加表级写锁的时候,系统会自动创建一个MDL写锁(SHARED_NO_READ_WRITE


DML

看下DML中INSERTUPDATEDELETE语句会加什么样的MDL锁。

image-20230721174057199

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

image-20230721174326691

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

image-20230721174613619

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

总结:

语句种类(DML)MDL锁类型
INSERT、UPDATE、DELETESHARED_WRITE

DDL

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


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

SQL命令锁类型
SELECT、SELECT...[FOR SHARELOCK IN SHARE MODE]SSSHARE_READMDL共享读
INSERT、UPDATE、DELETE、SELECT...FOR UPDATESHARE_WRITEMDL共享写
ALTER TABLE、CREATE INDEX等DDL语句EXCLUSIVEMDL排他锁
LOCK TABLES 表明 READ/WRITESHARED_READ_ONLY/SHARED_NO_READ_WRITE共享只读锁/共享只写锁

MDL锁类型兼容性

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

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

SHARE_READ和SHARE_READ

image-20230721192257982

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

image-20230721192400027


SHARE_READ和SHARE_WRITE

image-20230721191910087

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

image-20230721192105467

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

SHARE_READ和EXCLUSIVE

image-20230721192755621

查看锁情况:

image-20230721210438885

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

SHARE_READ和SHARED_READ_ONLY

image-20230721210900711

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

image-20230721210948331

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

SHARE_READ和SHARED_NO_READ_WRITE

image-20230721211208668

查看下锁信息:

image-20230721211244376

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

SHARE_WRITE和SHARE_WRITE

image-20230721212228012

查看MDL锁情况:

image-20230721212312695

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

SHARE_WRITE和EXCLUSIVE

image-20230721212615411

查看下MDL锁情况:

image-20230721212700001

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

SHARE_WRITE和SHARED_READ_ONLY

image-20230721212959706

查看MDL锁信息:

image-20230721213037832

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

SHARE_WRITE和SHARED_NO_READ_WRITE

image-20230721213419019

查看MDL锁信息:

image-20230721213508422

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

EXCLUSIVE和SHARED_READ_ONLY

image-20230721213751963

查看MDL锁信息:

image-20230721213903213

说明 EXCLUSIVE和SHARED_READ_ONLY是不兼容的。

EXCLUSIVE和SHARED_NO_READ_WRITE

image-20230722101820470

查看下MDL锁情况:

image-20230722101937978

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

兼容性总结

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

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

意向锁

概念

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

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

image-20230722201953748

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

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

image-20230722202311573

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

加锁方式

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

意向锁表信息

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

image-20230722203645733

上图中我开启事务,并执行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

image-20230722205447382

查看锁情况:

image-20230722205546894

SELECT...FOR SHARE会加上意向共享锁(IS),有了意向共享锁后能够使用lock tables t1 read加上表读锁吗?

image-20230722205823757

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

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

image-20230722205957110

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


DML所加的意向锁都是IX锁(意向排他锁)

image-20230722210809349

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

image-20230722210906354

可以看到加了一个意向排他锁(IX)。

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

image-20230722211005118

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

再看下能否加上表写锁。

image-20230722211118035

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


再看下UPDATE语句。

image-20230722211607723

IX锁与表读锁互斥

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

image-20230722211740010

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不能出现重复的情况,这就是通过自增锁实现的。