MDL optimization (metadata lock)

A metadata lock (MDL) is an internal database lock that ensures the consistency of table metadata during Data Definition Language (DDL) operations. Standard read/write transactions must acquire an MDL read lock on the target table, while DDL operations require an MDL write lock on the target table. When you run DDL statements, be aware of the following issues related to metadata locks:

• Blocking

  A long-running transaction holding an MDL read lock can block a DDL operation, causing it to fail after waiting too long to acquire an MDL write lock. Additionally, because MySQL uses a fair lock mechanism for MDLs, a blocked DDL statement will also block all new transactions that are queuing for an MDL read lock.

• Deadlock risk

  When multiple DDL statements and transactions run concurrently, they can request MDLs in different orders, which may lead to a deadlock.

• Exclusivity

  An MDL write lock is exclusive. When a DDL operation acquires an MDL write lock, all incoming transactions are blocked because they cannot obtain an MDL read lock. This can cause a traffic drop to zero. Although MySQL supports Online DDL, these operations still need to briefly acquire an MDL write lock at critical stages and cannot completely avoid locking the table.

If these issues occur during DDL execution, they can create a large number of blocked business connections and, in severe cases, cause a temporary traffic drop to zero. PolarDB-X provides targeted optimizations for these metadata lock issues to eliminate these risks.

Preemptible MDL optimization

PolarDB-X supports preemptible MDL optimization. This optimization eliminates the risk of blocking from metadata locks during DDL execution.

Supported versions

This feature is available in PolarDB-X version 5.4.17-16952556 and later.

How it works

When you run a DDL statement that requires an MDL write lock, if the wait time to acquire the lock is too long, PolarDB-X automatically terminates the long-running transaction's connection that is blocking the DDL.

The following table illustrates a scenario where a DDL statement and a new transaction are blocked by a long-running transaction without this optimization.

Table 1

Step	Session 1	Session 2	Session 3
1	begin;	-	begin;
2	insert into tb0 values(1); -- Acquires an MDL read lock on tb0.	-	-
3	-- The transaction is not committed to simulate a long-running transaction.	-	-
4	-	alter table tb0 add column col int; -- Tries to acquire an MDL write lock on tb0 and is blocked by the long-running transaction.	-
5	-	-	select id from tb0; -- Tries to acquire an MDL read lock on tb0 and is blocked.

The following output shows the execution process with the preemptible MDL optimization enabled. The steps are the same as in Table 1.

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb0 values(1);
Query OK, 1 row affected (0.16 sec)
mysql> select version();
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    46
-- session2
mysql> alter table tb0 add column col int;
Query OK, 0 rows affected (29.17 sec)
-- session3
mysql> select id from tb0;
Empty set (1.04 sec)

As shown in the output, the preemptible MDL optimization terminates the connection of the long-running transaction in Session 1. This allows the DDL statement in Session 2 to execute successfully and ensures that the new transaction in Session 3 runs normally.

Distributed metadata deadlock detection

PolarDB-X supports distributed metadata deadlock detection. This feature detects and breaks deadlocks involving metadata locks during DDL execution, allowing the DDL operations and other transactions to proceed.

Supported versions

This feature is available in PolarDB-X version 5.4.17-16952556 and later.

How it works

PolarDB-X periodically scans the lock-wait relationships between transactions and DDL statements. If a deadlock is detected, PolarDB-X automatically terminates one of the conflicting transactions to ensure that the DDL and other transactions can proceed.

The following table shows a typical deadlock scenario involving metadata locks.

Table 2

Step	Session 1	Session 2	Session 3	Session 4
1	begin; -- Starts transaction 1.	begin; -- Starts transaction 2.	-	-
2	insert into t1 values(1); -- Acquires an MDL read lock on t1.	insert into t2 values(1); -- Acquires an MDL read lock on t2.	-	-
3	-	-	alter table t1 add column col int; -- Starts DDL 1, which tries to acquire an MDL write lock on t1 and is blocked.	alter table t2 add column col int; -- Starts DDL 2, which tries to acquire an MDL write lock on t2 and is blocked.
4	insert into t2 values(2); -- Tries to acquire an MDL read lock on t2, but is blocked by DDL 2 due to the fair lock mechanism.	insert into t1 values(2); -- Tries to acquire an MDL read lock on t1, but is blocked by DDL 1 due to the fair lock mechanism.	-	-

The following output demonstrates how PolarDB-X resolves the deadlock from Table 2 by using distributed metadata deadlock detection. The execution steps are the same as those in the table.

-- Session 1
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.12 sec)
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.99 sec)

-- Session 2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.08 sec)
mysql> insert into t1 values(2); -- An MDL deadlock is detected. The connection is killed and the transaction is rolled back.
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    14
Current database: d0

-- Session 3
mysql> alter table t1 add column col int;
Query OK, 0 rows affected (2 min 38.30 sec)

-- Session 4
mysql> alter table t2 add column col int;
Query OK, 0 rows affected (2 min 59.85 sec)

After the deadlock scenario from Table 2 is created, PolarDB-X detects the deadlock and chooses to terminate transaction 2. This rollback allows transaction 1, DDL 1, and DDL 2 to complete.

Dual-version MDL optimization

For logically executed DDL, PolarDB-X supports dual-version metadata and corresponding dual-version metadata locks. This allows these DDL operations to run without ever locking tables or causing a traffic drop to zero.

Limitations

This optimization applies to all logically executed DDL statements in PolarDB-X. To determine if a DDL statement is executed logically, see Online DDL.

How it works

PolarDB-X implements logical DDL based on the Online Schema Change principle. It divides the metadata version of a logical DDL operation into multiple smaller versions, allowing the metadata to evolve safely within the PolarDB-X instance. For example, a CREATE GLOBAL INDEX DDL statement in PolarDB-X involves multiple version transitions: ABSENT(Vn), DELETE_ONLY(Vn+1), WRITE_ONLY(Vn+2), and PUBLISH(Vn+3). Furthermore, PolarDB-X binds a separate metadata lock to each of these minor versions.

Thanks to the Online Schema Change mechanism, two metadata versions can coexist, not only across different compute nodes in the cluster but also within a single compute node. Therefore, when a logical DDL operation begins to evolve the metadata version, PolarDB-X only acquires the MDL write lock for the old metadata version during each transition. This allows new transactions to access the new metadata version and acquire the corresponding MDL read lock.

With the dual-version MDL optimization, logical DDL operations in PolarDB-X can run without ever locking tables or causing a traffic drop to zero.