暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / MySQL之锁详解（一）：MDL（元数据锁）、latch（闩锁）

MySQL之锁详解（一）：MDL（元数据锁）、latch（闩锁）

GrowthDBA 2022-05-07
2627
上篇文章MySQL之事务隔离级别和锁简介，简单学习了MySQL的四种事务隔离级别，知道了每种事务隔离级别下可以解决哪些问题。同时也简单学习了MySQL Server层、InnoDB存储引擎层的锁、锁算法相关概念。
今天我们就将上篇文章中介绍的内容进行拆分、细化、详解，因个人水平有限，希望尽最大努力给大家说明白，说不明白的地方希望能和大家一起研究探讨。开始今天的学习吧。

MDL（元数据锁）

01

MDL锁的查看

LOOK

到目前为止，我们只知道了元数据锁（Metadata Lock，MDL）是MySQL 5.5版本引入的，用于解决或者保证DDL操作与DML等操作之间的一致性。也举了一个mysqldump备份的例子，由于还没有学习mysqldump备份原理，可能部分小伙伴还不是很理解，所以，今天再举一个简单的栗子🌰，大家一看便知。
在举例子之前，为方便我们查看MDL锁信息，需要做一些配置，然后就可以通过performance_schema.metadata_locks表来查看MDL锁的相关信息了。有两种配置方式：

  • 如果MySQL实例未启动，在实例启动时开启，需要在my.cnf配置文件中添加以下配置：

[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

  • 如果在MySQL实例正在运行时开启，需要更新performance_schema.setup_instruments表：

UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';
然后，我们就可以查看performance_schema.metadata_locks表来获取当前MySQL实例的MDL锁信息了：
SELECT * FROM performance_schema.metadata_locks;

performance_schema.metadata_locks表有以下的列（具体释义如下）：

列名‍‍‍描述‍‍‍
OBJECT_TYPE元数据锁子系统中使用的锁类型。该值为GLOBAL、SCHEMA、TABLE、FUNCTION、PROCEDURE（当前未使用）、EVENT、COMMIT、USER LEVEL LOCK（表示使用GET_LOCK()获得的锁）、TABLESPACE或LOCKING SERVICE之一。
OBJECT_SCHEMA锁对象所在的SCHEMA（库名）。
OBJECT_NAME锁对象的名称（表名）。
OBJECT_INSTANCE_BEGIN检测对象在内存中的地址。
LOCK_TYPE该值为INTENTION_EXCLUSIVE、SHARED、SHARED_HIGH_PRIO、SHARED_READ、SHARED_WRITE、SHARED_UPGRADABLE、SHARED_NO_WRITE、SHARED_NO_READ_WRITE或EXCLUSIVE之一。。
LOCK_DURATION来自元数据锁子系统的锁定持续时间。该值是STATEMENT、TRANSACTION或EXPLICIT之一。STATEMENT和TRANSACTION值分别表示在语句或事务端隐式释放的锁。EXPLICIT值表示在语句或事务结束中幸存下来并通过显式操作释放的锁，例如使用FLUSH TABLES WITH READ LOCK获取的全局锁。
LOCK_STATUS来自元数据锁子系统的锁定状态。该值为PENDING、GRANTED、VICTIM、TIMEOUT、KILLED、PRE_ACQUIRE_NOTIFY或POST_RELEASE_NOTIFY。
SOURCE包含产生事件的检测代码的源文件的名称以及检测发生的文件中的行号。这使您能够检查源代码以确定所涉及的确切代码。
OWNER_THREAD_ID请求元数据锁的线程。
OWNER_EVENT_ID请求元数据锁定的事件ID。
另外，TRUNCATE TABLE不允许用于performance_schema.metadata_locks表。需要重点关注的字段是LOCK_STATUS，这个字段展示是每个锁的状态：

  • 当请求并立即获得元数据锁时，将插入状态为GRANTED的行。

  • 当请求元数据锁但未立即获得时，将插入状态为PENDING的行。

  • 当授予之前请求的元数据锁时，其行状态将更新为GRANTED。

  • 当元数据锁被释放时，其行将被删除。

  • 当死锁检测器取消挂起的锁定请求以打破死锁（ER_LOCK_DEADLOCK）时，其行状态将从PENDING更新为VICTIM。

  • 当挂起的锁定请求超时（ER_LOCK_WAIT_TIMEOUT）时，其行状态将从PENDING更新为TIMEOUT。

  • 当授予的锁或挂起的锁请求被终止时，其行状态从GRANTED或PENDING更新为KILLED。

  • VICTIM、TIMEOUT和KILLED状态值很简短，表示锁定行即将删除。

  • PRE_ACQUIRE_NOTIFY和POST_RELEASE_NOTIFY状态值很简短，表示元数据锁定子系统在进入锁获取操作或离开锁释放操作时通知感兴趣的存储引擎。这些状态值已添加到MySQL 5.7.11中。

有了上面的设置和知识铺垫，我们现在就来举这个MDL元数据锁的栗子🌰：

①首先，在一个Session中开启一个事务，并且做查询操作：

mysql> begin;mysql> select * from test_lock.l;

②查看一下MDL元数据锁的加锁情况：

mysql> SELECT * FROM performance_schema.metadata_locks;

可以看到，共输出2条信息，观察发现，所有的查询语句（包含当前会话执行的对performance_schema.metadata_locks表的查询）都会加上SHARED_READ（共享读）类型的锁，锁类型（锁粒度）为TABLE（表）、锁对象是test_lock库下的l表，元数据锁的线程为39085（用于区分后面其他会话的元数据锁）。

③在一个新的Session中执行一个DDL语句：

mysql> drop table test_lock.l;

由图观之，DDL操作会被阻塞。

④再来查看一下MDL元数据锁的加锁情况：

mysql> SELECT * FROM performance_schema.metadata_locks;

可以看到，除了第②步的加锁信息外，又额外多出3条记录，DDL操作会在TABLE粒度的test_lock库下的l表加一个EXCLUSIVE（独占/排他）锁，并且状态是PENDING（请求元数据锁但未获得）。另外两条记录，通过相同的OWNER_THREAD_ID（29087）可以看出，都是出自DDL操作的会话申请的元数据锁，锁粒度分别是GLOBAL（全局）和SCHEMA（实例/库）级别，锁的类型都是INTENTION_EXCLUSIVE（意向排他）锁。

由此可以得到结论：新Session中的DDL会被阻塞的原因是因为Session 1中的SELECT语句会申请TABLE级别MDL的SHARED_READ锁，Session 2中的DDL语句会申请TABLE级别MDL的EXCLUSIVE锁（同时会在全局、SCHEMA级别申请意向排他锁），因为共享读锁和排他锁互斥，所以导致了DDL阻塞。同样，锁兼容矩阵在MDL元数据锁中也适用。

02

MDL锁的实现原理

PRINCIPLE

从上面的例子中可以看出，如果没有MDL锁，Session 2的就可以直接执行DROP操作，从而导致Session 1的事务报错。正因为MySQL 5.5版本MDL锁的加入，避免了这种问题的发生。

上篇文章，我们知道InnoDB存储引擎层已经有了IS、IX这样的意向锁，是不是觉得可以直接用来实现上述例子的并发控制。但由于MySQL是Server-Engine架构，所以MDL锁是在Server层中实现。另外，从performance_schema.metadata_locks表中的信息可以看出：MDL锁还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁，这是InnoDB存储引擎层不能直接实现的锁。

但与InnoDB存储引擎层的锁实现一样，MDL锁也是类似对一棵B+树的各个对象从上至下进行加锁。但是MDL锁对象的层次更多，简单来看有如下的层次：

上图中显示了最常见的4种MDL锁的对象，并且注明了常见的SQL语句会触发锁。与InnoDB存储引擎层类似的是，某些类型的MDL锁会从上往下一层层进行加锁。比如LOCK TABLE xxx WRITE这样的SQL语句，其首先会对GLOBAL级别加INTENTION_EXCLUSIVE锁，再对SCHEMA级别加INTENTION_EXCLUSIVE锁，最后对TABLE级别加SHARED_NO_READ_WRITE锁。
mysql> LOCK TABLE test_lock.l WRITE;mysql> SELECT * FROM performance_schema.metadata_locks;

这里最令人意外的是还有COMMIT对象层次的锁，其实这主要用于XA事务中。比如分布式事务已经PREPARE成功，但是在XA COMMIT之前有其他会话执行了FLUSH TABLES WITH READ LOCK这样的操作，那么分布式事务的提交就需要等待。

mysql> FLUSH TABLES WITH READ LOCK;mysql> SELECT * FROM performance_schema.metadata_locks;

(EXPLICIT值表示在语句或事务结束中幸存下来并通过显式操作释放的锁)

除了上图罗列出的对象，还有TABLESPACE、FUNCTION、PROCEDURE、EVENT等其他对象类型，其实都是为了进行并发控制。只是这些在MySQL数据库中都不常用，故不再赘述。目前MDL锁有以下锁模式，锁之间的兼容性可见源码/mysql-server-mysql-[version]/sql/mdl.cc：

锁模式‍‍‍对应SQL语句‍‍‍
MDL_INTENTION_EXCLUSIVE涉及GLOBAL对象、SCHEMA对象操作会加此锁。
MDL_SHAREDFLUSH TABLES WITH READ LOCK

MDL_SHARED_HIGH_PRIO

仅对MyISAM存储引擎有效。
MDL_SHARED_READSELECT（DQL）查询语句。
MDL_SHARED_WRITEINSERT、UPDATE、DELETE（DML）语句。
MDL_SHARED_WRITE_LOW_PRIO仅对MyISAM存储引擎有效。
MDL_SHARED_UPGRADABLEALTER TABLE ...
MDL_SHARED_READ_ONLYLOCK TABLE ... READ
MDL_SHARED_NO_WRITEFLUSH TABLES ..., ..., ... FOR EXPORT
MDL_SHARED_NO_READ_WRITELOCK TABLE ... WRITE
MDL_EXCLUSIVEALTER TABLE ... PARTITION BY …、DROP TABLE ...

到这里会发现MDL锁的开销并不比InnoDB存储引擎层的（row-level）行锁要小，而且这可能是一个更为密集的并发瓶颈。MySQL 5.6和5.5版本通常通过调整如下两个参数来进行并发调优:

  • metadata_locks_cache_size：MDL锁的缓存大小

  • metadata_locks_hash_instances：通过分片来提高并发度，与InnoDB AHI（Adaptive Hash Index，自适应Hash索引）类似。

MySQL 5.7版本MDL锁的最大改进之处在于将MDL锁的机制通过lock free算法来实现，从而提高了在多核并发下数据库的整体性能提升。

小提示

到现在为止，是不是可以解决大家很多疑惑。比如直接使用LOCK TABLE ...、FLUSH TABLE ... FOR EXPORT、FLUSH TABLES WITH READ LOCK等SQL语句，实际上这种锁都属于MDL元数据锁的范畴，且都是在MySQL Server层实现的（和InnoDB存储引擎一点关系也没有），并且MDL锁是可以直接将锁加在GLOBAL、SCHEMA、TABLE对象上的，即全局锁、SCHEMA锁、表锁。这里的“表锁”一定要和InnoDB存储引擎层的IS（意向共享）、IX（意向排他）这种table-level（“表级别的锁”）区分开来，因为InnoDB存储引擎是标准的row-level（行级别）行锁，是不会直接给“表”这个对象加锁的，后面InnoDB存储引擎加锁分析的时候会再详细解释的。

latch（闩锁）

01

latch的查看

LOOK

latch，一般被称为闩（shuān）锁（轻量级的锁），因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。
在InnoDB存储引擎中，latch又可以分为mutex（互斥量）和rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测机制。
而InnoDB存储引擎层的Lock（锁）的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般Lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同），同时，Lock如大多数数据库一样，是有死锁检测机制的。如下图所示，lock与latch的区别：

对于InnoDB存储引擎中的latch，可以通过以下命令进行查看：
mysql> SHOW ENGINE INNODB MUTEX;

在DEBUG版本下，可以看到latch更多信息，如下所示：
mysql> SHOW ENGINE INNODB MUTEX;+--------+-------------------------+---------------------------------------------------------------------------------+| Type   | Name                    | Status                                                                          |+--------+-------------------------+---------------------------------------------------------------------------------+| InnoDB | &kernel_mutex:srv0srv.c | count=54, spin_waits=6, spin_rounds=60, os_waits=3, os_yields=3, os_wait_time=0 || InnoDB | log0log.c:833           | os_waits=2                                                                      || InnoDB | rw_lock_mutexes         | count=0, spin_waits=0, spin_rounds=0, os_waits=0, os_yields=0, os_wait_time=0   |+--------+-------------------------+---------------------------------------------------------------------------------+3 rows in set (0.01 sec)
由上可得，列Type显示总是为InnoDB，列Name显示的是latch的信息以及所在源码的位置（行数）。列Status比较复杂，具体详解如下：
名称‍‍‍描述‍‍‍
countmutex被请求的次数。
spin_waitsspin lock（自旋锁）的次数，InnoDB存储引擎latch在不能获得锁时首先进行自旋，若自旋后还不能获得锁，则进入等待状态。
spin_rounds自旋内部循环的总次数，每次自旋的内部循环是一个随机数，spin_rounds/spin_waits表示平均每次自旋所需的内部循环次数。
os_waits表示操作系统等待的次数。当spin lock通过自旋还不能获得latch时，则会进入操作系统等待状态，等待被唤醒。
os_yields进行os_thread_yield唤醒操作的次数。
os_wait_times操作系统等待时的时间，单位是ms（毫秒）。
还有一种方法可以查看latch相关信息：
mysql> SHOW ENGINE INNODB STATUS\G

  • rounds，表示spin（自旋）一次空转多少圈，也就是返回来询问的次数；
  • OS waits，表示sleep，当突然增长比较快时，说明latch争用比较严重：
1. 如果OS waits值比较高，说明出现latch争用，可能出现异常SQL的情况；
2. 获取latch的代价：42.00 RW-shared, 62.00 RW-excl。

02

latch详解

DETAIL

其实latch相关的内容是偏向底层的，我的理解也不是很深，以下内容来自于一些网上的资料，仅供大家参考。我对latch的理解就是一种存在于内存中的内存锁，之前讲关于InnoDB Buffer Pool的时候，提到过很多List（链表），如LRU List，通俗来讲，latch就是在内存中保护List完整性的内存锁结构。

latch分为两种：

1、kernel mutex for lock_sys/trx_sys/log_sys，X（调用内核级别的锁）

2、rw-lock for page_hash，S、X、SX（Page管理时候使用的锁，读一个表数据，会加S（共享）的latch，全表扫描，读取过程会加很多的Page，但是发生数据更新时会加X（排他）的latch，这样就会产生相互等待【事务层面不会等待，但是Page层面会发生等待】，系统负载高的时候，可能会造成InnoDB的Page信号等待时间太长，超过600s，InnoDB会发出Crash信号），这种情况的解决办法就是：创建合理的索引。

SX rw-lock是MySQL 5.7的新特性，有助于提升索引访问效率（针对索引更新的模式）。想要获取latch（rounds），需要先spin wait（spins），如果获取不到会进入sleep状态（OS waits）。innodb_spin_wait_delay，两次spin wait之间的随机等待时间，设置为0可禁止等待间隙。

latch锁争用示意图：

1、A、B线程，只读（R），共享（S），不冲突。
2、C线程，写（W），排他（X），冲突，需要spin wait → OS waits等待。
降低latch争用的方法：

①优化、提高SQL执行效率，减少latch的耗时；

②事务不要过大，事务尽量要快速执行；

③适当调高innodb_buffer_pool_instances参数。

小提示
spin lock（自旋锁）：是一种典型的对临界资源进行互斥访问的手段，它是基于系统原子操作为基础，自旋锁最多只能被一个可执行线程持有，如果一个执行线程试图获得一个被已经持有（争用）的自旋锁，那么该线程就会一直进行忙循环-旋转-等待锁重新可用，要是锁未被争用，请求锁的执行线程就可以立即得到它，继续执行。
mutex（互斥量）：可视作是spin lock的可睡眠版本，同样是线程无法继续向前执行，但spin lock是"spin（自旋）"，导致该CPU上无法发生线程切换；而mutex是"block（阻塞）"，可以发生线程切换，让所在CPU上的其他线程继续执行。阻塞既可以发生在线程试图获取mutex时，也可以发生在线程持有mutex时。
spin lock（自旋锁）和mutex（互斥量）的异同：
  • 相同点：
1、为保护共享资源提出的锁机制：为了解决对某项资源的互斥使用。
2、在任何时刻，最多只能有一个执行单元获得锁。
3、一个执行单元要想访问被锁保护的共享资源，必须先得到锁，在访问完共享资源后，必须释放锁。
  • 不同点：
对于mutex，如果资源已被占用，资源申请者只能进入睡眠状态。但是spin lock不会引起调用者睡眠，如果spin lock已被别的执行单元保持，调用者就一直循环等待（也就是spin自旋的由来）。
也可以理解为：mutex.release()等同于latch。

小结


今天学习了MDL（元数据锁）和latch（闩锁）的相关知识，下面来简单做个总结：

  • LOCK TABLE ...、FLUSH TABLE ... FOR EXPORT、FLUSH TABLES WITH READ LOCK、DQL、DML、DDL等SQL语句的加锁范围都会在MDL元数据锁的加锁范畴内。

  • MDL元数据锁是针对MySQL Server层来说的，和InnoDB存储引擎没有任何关系，锁兼容矩阵在MDL元数据锁中也同样适用。

  • latch（闩锁）是内存结构中的一种轻量级的锁，在MySQL数据库中，latch是用于保护内存中List Page完整性的锁结构，latch可以分为有mutex、SX rw-lock（spin lock），SX rw-lock是MySQL 5.7的新特性，针对Page粒度加的内存锁，有助于提升索引访问效率（针对索引更新的模式）。

  • spin lock（自旋锁）：最多只能被一个可执行线程持有，如果一个执行线程试图获得一个被已经持有（争用）的自旋锁，那么该线程就会一直进行忙循环-旋转-等待锁重新可用，spin lock是"spin（自旋）"，导致该CPU上无法发生线程切换，要是锁未被争用，请求锁的执行线程就可以立即得到它，继续执行。

  • mutex（互斥量）：可视作是spin lock的可睡眠版本，同样是线程无法继续向前执行，mutex是"block（阻塞）"，可以发生线程切换，让所在CPU上的其他线程继续执行。阻塞既可以发生在线程试图获取mutex时，也可以发生在线程持有mutex时。

  • 对于mutex，如果资源已被占用，资源申请者只能进入睡眠状态。但是spin lock不会引起调用者睡眠，如果spin lock已被别的执行单元保持，调用者就一直循环等待（也就是spin自旋的由来）。

  • mutex.release()等同于latch。

latch闩锁不怎么好理解，我的理解也不是很深刻，所以文章就只能写到这种程度了，仍希望能帮助到大家，也欢迎大家一起研究讨论。不过话说回来，latch锁是偏向于底层内核层面的知识，在我们日常使用MySQL过程中用的非常少，如果实在理解不了，那就当科普了解一下。

MDL元数据锁配合上performance_schema.metadata_locks表还是很通俗易懂的，但一定要区分和InnoDB存储引擎层锁的区别。MDL元数据锁可以这样理解：不管执行任何SQL，只要过MySQL Server层，就会用到MDL锁。在不破坏事务ACID的前提下，根据不同的锁类型来实现数据库资源的并发控制访问。MDL锁也是从上往下GLOBAL → SCHEMA → TABLE → XA COMMIT一层一层加锁的。

下篇文章就会开始讲解关于InnoDB存储引擎下不同隔离级别的加锁情况分析了，大家拭目以待！今天的内容就这么多了，每天进步一点点，我们下篇文章见！~



参考资料

  • InsideMySQL公众号-姜承尧-《深入理解MySQL的MDL元数据锁》

  • 姜承尧《MySQL技术内幕：InnoDB存储引擎 第2版》

  • https://dev.mysql.com/doc/refman/5.7/en/performance-schema-metadata-locks-table.html

  • https://blog.csdn.net/luolaihua2018/article/details/119547041

  • https://blog.csdn.net/u011721450/article/details/110002582

  • https://zhuanlan.zhihu.com/p/90508284

  • http://www.wowotech.net/kernel_synchronization/spinlock.html


扫描二维码关注

获取更多精彩

GrowthDBA

end


元数据mysqlmysql update语句mdltable
文章转载自GrowthDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论