MySQL 使用元数据锁定来管理对数据库对象的并发访问并确保数据一致性。元数据锁定不仅适用于表,还适用于模式和存储程序(过程、函数、触发器和预定事件)。
元数据锁定确实涉及一些开销,随着查询量的增加而增加。多个查询尝试访问相同对象的次数越多,元数据争用就越多。
元数据锁定不是表定义缓存的替代品,它的互斥量和锁不同于
LOCK_open
互斥量。以下讨论提供了有关元数据锁定如何工作的一些信息。
如果给定的锁有多个等待者,优先级最高的锁请求首先得到满足,与
max_write_lock_count
系统变量相关的异常。写锁请求的优先级高于读锁请求。但是,如果
max_write_lock_count
设置为某个较低的值(比如 10),如果读取锁定请求已被传递以支持 10 个写入锁定请求,则读取锁定请求可能优先于挂起的写入锁定请求。通常不会发生此行为,因为
max_write_lock_count
默认情况下具有非常大的值。
语句逐个获取元数据锁,而不是同时获取,并在此过程中进行死锁检测。
DML 语句通常按照语句中提及表的顺序获取锁。
DDL 语句、LOCK TABLES
和其他类似语句试图通过按名称顺序获取显式命名表上的锁来减少并发 DDL 语句之间可能出现的死锁数。对于隐式使用的表(例如也必须锁定的外键关系中的表),可能会以不同的顺序获取锁。
例如,RENAME TABLE
是一个按名称顺序获取锁的 DDL 语句:
此
RENAME TABLE
语句重命名tbla
为其他内容,并重命名tblc
为tbla
:RENAME TABLE tbla TO tbld, tblc TO tbla;
该语句按顺序在 、 和 上获取元数据锁
tbla
(tblc
因为tbld
按照tbld
名称tblc
顺序):这个略有不同的语句也重命名
tbla
为其他内容,并重命名tblc
为tbla
:RENAME TABLE tbla TO tblb, tblc TO tbla;
在这种情况下,该语句按顺序在 、 和 上获取元数据锁
tbla
(tblb
因为tblc
在名称顺序tblb
之前 ):tblc
这两个语句都按此顺序获取tbla
和
tblc
上的锁,但不同之处在于剩余表名的锁是在 之前还是之后获取tblc
。
当多个事务并发执行时,元数据锁获取顺序可能会影响操作结果,如以下示例所示。
从两个具有相同结构的表x
开始
。x_new
三个客户端发出涉及这些表的语句:
客户 1:
LOCK TABLE x WRITE, x_new WRITE;
x
该语句在和
上按名称顺序请求和获取写锁x_new
。
客户 2:
INSERT INTO x VALUES(1);
该语句请求并阻止等待 上的写锁定
x
。
客户 3:
RENAME TABLE x TO x_old, x_new TO x;
x
该语句在、x_new
和
上按名称顺序请求排他锁
x_old
,但在等待锁时阻塞
x
。
客户 1:
UNLOCK TABLES;
x
该语句释放和
上的写锁x_new
。客户端 3的独占锁请求
x
比客户端 2 的写锁请求具有更高的优先级,因此客户端 3 在 上获取其锁x
,然后在x_new
和上获取其锁x_old
,执行重命名并释放其锁。然后客户端 2 获取其对 的锁定
x
,执行插入,然后释放其锁定。
锁定获取顺序导致
RENAME TABLE
在INSERT
. x
发生插入的表是在客户端 2 发出插入并被客户端 3 重命名为时命名的
x_new
表x
:
mysql> SELECT * FROM x;
+------+
| i |
+------+
| 1 |
+------+
mysql> SELECT * FROM x_old;
Empty set (0.01 sec)
现在从命名的表x
和
new_x
具有相同结构的表开始。同样,三个客户端发出涉及这些表的语句:
客户 1:
LOCK TABLE x WRITE, new_x WRITE;
new_x
该语句在和
上按名称顺序请求和获取写锁x
。
客户 2:
INSERT INTO x VALUES(1);
该语句请求并阻止等待 上的写锁定
x
。
客户 3:
RENAME TABLE x TO old_x, new_x TO x;
new_x
该语句在、old_x
和
上按名称顺序请求排他锁
x
,但在等待锁时阻塞
new_x
。
客户 1:
UNLOCK TABLES;
x
该语句释放和
上的写锁new_x
。对于x
,唯一未决的请求是由客户端 2 发出的,因此客户端 2 获取其锁,执行插入,然后释放锁。对于
new_x
,唯一未决的请求是由客户端 3 发出的,它被允许获取该锁(以及 上的锁old_x
)。x
在 Client 2 插入完成并释放其锁之前,重命名操作仍会因锁定而阻塞。然后客户端 3 获取锁x
,执行重命名,并释放它的锁。
在这种情况下,锁获取顺序导致
INSERT
在
RENAME TABLE
. x
插入的 是原来的,
x
现在
old_x
通过重命名操作重命名为:
mysql> SELECT * FROM x;
Empty set (0.01 sec)
mysql> SELECT * FROM old_x;
+------+
| i |
+------+
| 1 |
+------+
如果并发语句中的锁获取顺序对应用程序的操作结果产生影响,如前例所示,您可以调整表名以影响锁获取顺序。
为了确保事务的可序列化性,服务器不得允许一个会话在另一个会话中未完成的显式或隐式启动的事务中使用的表上执行数据定义语言 (DDL) 语句。服务器通过获取事务中使用的表的元数据锁并推迟释放这些锁直到事务结束来实现这一点。表上的元数据锁可防止更改表的结构。这种锁定方法意味着在事务结束之前,一个会话中的事务正在使用的表不能由其他会话在 DDL 语句中使用。
这个原则不仅适用于事务表,也适用于非事务表。假设一个会话开始一个使用事务表
t
和非
事务表的事务nt
,如下所示:
START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;
服务器在 和 上都持有元数据锁t
,nt
直到事务结束。如果另一个会话尝试对任一表执行 DDL 或写锁定操作,它将阻塞,直到事务结束时释放元数据锁定。例如,如果第二个会话尝试以下任何操作,它就会阻塞:
DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;
如果服务器为语法上有效但在执行过程中失败的语句获取元数据锁,则它不会提前释放锁。锁释放仍然延迟到事务结束,因为失败的语句被写入二进制日志并且锁保护日志一致性。
在自动提交模式下,每个语句实际上都是一个完整的事务,因此为该语句获取的元数据锁仅保留到该语句的末尾。
一旦准备好语句,语句期间获取的元数据锁
PREPARE
就会被释放,即使准备发生在多语句事务中也是如此。