1, the background
We have a table in our system that has a very high probability of being queried. Recently, there was a requirement to add a field to the table. However, when adding a field, it was found that multiple services in the system had timeout operations. What is the cause of this? After looking up the data, it was found that the DATABASE MDL lock + transaction caused.
2. What is MDL lock
MDL locks are metadata locks at the table level. Table level locks are divided into data locks and metadata locks. Generally, when we say “lock”, we generally refer to the added data lock. Like data locks, metadata locks have read and write locks.
MDL does not require display use and is added automatically during table operations. When the table to add, delete, change and check, will automatically add MDL read lock; MDL write locks are automatically added when adding or subtracting columns to a table.
- Read locks are not mutually exclusive, meaning that multiple threads can add, delete, modify, and check a table at the same time.
- Write lock exclusiveness. The MDL write lock can be acquired only after all other MDL locks are released. Once a write lock is acquired, no other thread can acquire the MDL read lock or write lock until the write lock is released. In other words, changing the structure of a table blocks operations on the table by other threads.
3. The necessity of MDL lock
MDL locks exist to ensure data consistency. Mysql > ALTER TABLE T DELETE COLUMN ‘col_1’; mysql > ALTER TABLE T DELETE COLUMN ‘col_1’; mysql > ALTER TABLE T DELETE COLUMN ‘col_1’; mysql > ALTER TABLE T DELETE COLUMN ‘col_1’;
4. Give examples
✅ : indicates the normal operation
❌ : indicates that the execution is stuck.
| Things a | Two things | Three things |
|---|---|---|
| start transaction; 1 ️ ✅ ⃣ | ||
| select * from customer; ✅ ️ 2 ⃣ | alter table customer add column_4 int null; ❌ 3 ️ ⃣ | |
| select * from customer; ❌ 4 ️ ⃣ | ||
| commit; 5 ️ ✅ ⃣ | 6 ️ ✅ ⃣ | 7 ️ ✅ ⃣ |
Explanation:
Step 1️ 2️ one. When performing step 2️, the SHARED_READ lock of Customer’s MDL is applied.
Step 3️ will be stuck, because the EXCLUSIVE lock of Customer’s MDL will be applied at this time, but the transaction of one cannot apply for the EXCLUSIVE lock since they are mutually EXCLUSIVE.
Step 4️ will also become stuck, as the EXCLUSIVE and SHARE_READ locks are mutually EXCLUSIVE and the EXCLUSIVE lock has a higher priority, so step 4️ also become stuck.
Step 5️ surrendered, release SHARE_READ lock on the table, and then the operation of 6️ and 7️ can be carried out.
If transaction two is executed first and transaction three is executed, it can succeed because the ALTER data DDL statement is independent of the transaction.