Why MySQL MDL(meta data lock) led to a deadlock?

Question

I'm learning MySQL locks and found some problems with the MDL.

The MySQL verison is 8.0.

There is a table named account using InnoDB.

session 1:

begin; // start the transaction

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

session 2:

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

Then I try to insert a record to table account in session 1, but MySQL tells me there is a deadlock and rollback session 1 automatically.

And I try something different:

session 1:

begin; // start the transaction

insert into account values ('test'); // acquire the MDL SHARED_WRITE lock

session 2:

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

Then I found it is OK to do the insert and select operations in session 1.

It really confused me, why I first use the select operation to acquire MDL SHARED_READ lock led to a deadlock?

Answer 1

Because there are different types of MDL shared locks, SHARED_READ and SHARED_WRITE.

session 1:

begin; // start the transaction

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

We can observe this 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:

To do an insert, session 1 would have to acquire a new type of MDL lock, but it can't do that while session 2 is queued waiting for a MDL EXCLUSIVE lock.

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

Thus both sessions are waiting for the other, and neither can proceed. This is a deadlock.

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

Second experiment:

session 1:

begin; // start the transaction

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

We can observe this 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 |

This is a different type of MDL. It allows inserts.

session 2:

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

Same as previous experiment, the DDL statement waits.

session 1:

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

There is no additional lock needed. Session 1 already has the MDL SHARED_WRITE lock it needs to do the insert, so it is able to proceed.