MySQL MDL(元数据锁)为什么会导致死锁

因为有不同类型的MDL共享锁，SHARED_READ和SHARED_WRITE.

session 1:个个个个

begin; // start the transaction

select * from table account; 
// acquire the MDL SHARED_READ lock

我们可以观察到这个MDL:

mysql> select object_name, lock_type, owner_thread_id from performance_schema.metadata_locks;
+----------------+--------------+-----------------+
| object_name    | lock_type    | owner_thread_id |
+----------------+--------------+-----------------+
| account        | SHARED_READ  |              64 |

session 2:个个

alter table account add columnA int; 
// try to acquire the MDL EXCLUSIVE lock, wait for session 1 to release lock

mysql> select object_name, lock_type, owner_thread_id from performance_schema.metadata_locks;
+----------------+---------------------+-----------------+
| object_name    | lock_type           | owner_thread_id |
+----------------+---------------------+-----------------+
| account        | SHARED_READ         |              64 |
| NULL           | INTENTION_EXCLUSIVE |              65 |
| NULL           | INTENTION_EXCLUSIVE |              65 |
| NULL           | INTENTION_EXCLUSIVE |              65 |
| account        | SHARED_UPGRADABLE   |              65 |
| test/account   | INTENTION_EXCLUSIVE |              65 |
| #sql-255_17    | EXCLUSIVE           |              65 |
| account        | EXCLUSIVE           |              65 |

session 1:个个个个

要执行插入操作，会话1必须获取一种新类型的MDL锁，但当会话2排队等待MDL独占锁时，它无法做到这一点.

mysql> insert into account () values ();
// try to acquire a MDL SHARED_WRITE lock
// wait for session 2 to release its queued lock request

因此，两个会话都在等待对方，两个会话都无法继续进行.这是一个僵局.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

第二个实验:

session 1:个个个个

begin; // start the transaction

select * from table account for update; 
// acquire the MDL SHARED_WRITE lock

我们可以观察到这个MDL:

mysql> select object_name, lock_type, owner_thread_id from performance_schema.metadata_locks;
+----------------+--------------+-----------------+
| object_name    | lock_type    | owner_thread_id |
+----------------+--------------+-----------------+
| account        | SHARED_WRITE |              64 |

这是一种不同类型的MDL.它允许插入.

session 2:个个

alter table account add columnA int; 
// try to acquire the MDL EXCLUSIVE lock, wait for session 1 to release lock

与前面的实验一样，DDL语句等待.

session 1:个个个个

mysql> insert into account () values ();
Query OK, 1 row affected (0.00 sec)

不需要额外的锁.会话1已经具有执行插入所需的MDL SHARED_WRITE锁，因此它能够继续.