MDL锁获取顺序和优先先

官方文档：

https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html

 

如果给定的锁有多个等待者，则满足最高优先级的锁请求，写锁请求的优先级高于读锁请求。

但是，如果 max_write_lock_count设置为某个较低的值（比如 10），如果读锁请求已经被传递给 10 个写锁请求，那么读锁请求可能比挂起的写锁请求更受欢迎。通常不会发生此行为，因为 max_write_lock_count默认情况下具有非常大的值。

语句是一个一个获取元数据锁，而不是同时获取元数据锁，并在进程中进行死锁检测。

DML语句通常按语句中提到的表的顺序获取锁。

DDL语句，LOCK TABLE语句和其它类似语句，是按照表名称顺序来获取元数据锁，对于隐式使用的表（比如外键），可能会以不同的顺序获取锁。

 

例如，RENAME TABLE是一个按照表名称获取锁的DDL语句。

此RENAME TABLE语句重命名tbla为tbld表，tblc重命名为 tbla：

RENAME TABLE tbla TO tbld, tblc TO tbla;

该语句获取元数据锁的顺序是tbla tblc tbld，（因为按名称排序，tbld在tblc后边）

 

这个RENAME TABLE语句将 tbla 重命名为tblb,  将 tblc 重命名为 tbla

RENAME TABLE tbla TO tblb, tblc TO tbla;

这种情况下，获取元数据锁的顺序是 tbla   ,   tblb   ,   tblc   （因为按名称排序，tblb 在 tblc 前边）

 

当多个事务并发执行时，元数据锁获取顺序会影响操作结果，如下所示，

x 和 x_new 两个表具有相同的表结构，三个客户端同时发出这些语句。

先创建 x 和 x_new 表

mysql> create table x (id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> create table x_new like x;
Query OK, 0 rows affected (0.01 sec)

 

session1：

LOCK TABLE x WRITE, x_new WRITE;

 

session2（进行锁等待状态）：

mysql> INSERT INTO x VALUES(1);

 

session3（进行锁等待状态）：

注意：加锁顺序是 x , x_new , x_old ，在对 x 表加锁时就遇到了锁等待，所以还没有对 x_new , x_old 表申请加锁。

mysql> RENAME TABLE x TO x_old, x_new TO x;

 

session1（释放 x , x_new 表锁）：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

session1释放锁资源后， session2 并没有最先获取到 x 表的元数据写锁请求，

session3 请求的是排它锁，排它锁优先级高于写锁请求，所以session3 拿到排它锁，再释放之后，session2才会到到 x 表的写锁请求。

session3获取锁的顺序是 x , x_new , x_old

 

RENAME TABLE 操作在 INSERT 之前进行了。

也就是说执行顺序是session1 --> session3  --> session2

gh-ost 软件在最后的切表阶段，就很精妙的利用了Mysql这个特性。

gh-ost原理：https://zhuanlan.zhihu.com/p/83771721

mysql> select * from x;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select * from x_old;
Empty set (0.00 sec)

 

 

 

实验2：创建x , new_x 表，

创建测试表

mysql> drop table x,x_old;
Query OK, 0 rows affected (0.01 sec)

mysql> create table x(id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> create table new_x like x;
Query OK, 0 rows affected (0.01 sec)

 

 

同样三个客户端发起相同的SQL语句。

session1：

LOCK TABLE x WRITE, new_x WRITE;

 

session2（进行锁等待状态）：

mysql> INSERT INTO x VALUES(1);

 

session3（进行锁等待状态）：

注意：加锁顺序是 new_x , old_x ，x 在对 new_x 表加锁时就遇到了锁等待，所以还没有对 old_x , x 表申请加锁。

mysql> RENAME TABLE x TO old_x, new_x TO x;

 

session1（释放 x , new_x 表锁）：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

session1释放 x , new_x 锁资源，

对于 x 表，只有session2 在等待获取锁，所以sesion1 释放锁资源后，session2会马上拿到锁资源，

对于 new_x 表，只有 session3  在等待获取锁，但 session3 不止要获取 new_x 表上的锁，还要获取 old_x , x 表上的锁。

其实，session2 在插入未完成之前，不会释放x 表锁，RENAME TABLE操作还是会处理锁等待状态。

 

mysql> SELECT * FROM x;
Empty set (0.01 sec)

mysql> SELECT * FROM old_x;
+------+
| i    |
+------+
|    1 |
+------+

 

如果并发语句中的锁获取顺序对操作结果的应用程序产生影响，如前面的示例，您可以调整表名以影响锁获取的顺序。