All Products
Search

This Product

    PolarDB:MDL optimization

    Document Center

    PolarDB:MDL optimization

    Last Updated:Dec 05, 2024

    A metadata lock (MDL) is an internal lock of a database that is used to ensure the consistency of table metadata used by transactions during the execution of DDL statements. For a common read/write transaction, you must acquire the read MDL of the corresponding table. For a DDL operation, you must acquire the write MDL of the corresponding table. During the execution of DDL statements, you must pay attention to the following issues related to MDLs:

    • Blocking risks

      Read MDLs held by long-running transactions block the execution of DDL statements. This leads to a DDL failure due to the unsuccessful long-time request for write MDLs. In addition, MDLs in MySQL are fair locks. Therefore, if DDL statements are blocked because read MDLs are held by long-running transactions to wait for write MDLs, new transactions are also blocked when they are queuing for read MDLs.

    • Deadlock risks

      If multiple DDL statements and transactions are executed at the same time, metadata deadlocks may occur because of the different orders in which MDLs are requested.

    • Exclusion risks

      Write MDLs are exclusive. After write MDLs are acquired for DDL statements, all new transactions will wait because they cannot request read MDLs. This results in a traffic drop to zero. Although the online DDL feature is supported in MySQL, such DDL statements only prevent table locking as much as possible throughout the process. In some key steps, short-term write MDL acquisition is required.

    If the preceding issues occur during the execution of DDL statements, service connections may be accumulated and blocked. In severe cases, a temporary service traffic drop to zero may occur, resulting in serious consequences. PolarDB-X provides optimizations for the preceding MDL-related issues, eliminating the preceding risks during the execution of DDL statements.

    Preemptible MDL optimization

    PolarDB-X supports preemptible MDL optimization, which eliminates the potential risks due to the blocking of MDLs during the execution of DDL statements.

    Supported versions

    This feature is supported only in PolarDB-X V5.4.17-16952556 or later.

    Description

    During the execution of a DDL statement for which a write MDL is required, if the time waiting for the write MDL exceeds the threshold, PolarDB-X automatically stops blocking the connection to which the long-running transaction for this DDL statement belongs.

    The following table describes the scenario in which DDL statements and new transactions are blocked by earlier long-running transactions if preemptible MDL optimization is unavailable.

    Table 1

    Step

    Session 1

    Session 2

    Session 3

    1

    begin;

    -

    begin;

    2

    insert into tb0 values(1);

    -- Acquire the read MDL of tb0.

    -

    -

    3

    -- Keep the transaction uncommitted for a long time to simulate a long-running transaction.

    -

    -

    4

    -

    alter table tb0 add column col int;

    -- Attempt to acquire the write MDL of tb0. This statement is blocked by the long-running transaction.

    -

    5

    -

    -

    select id from tb0;

    -- Attempt to acquire the write MDL of tb0. This statement is blocked.

    The following figure shows the execution process after preemptible MDL optimization is enabled. The steps are the same as those described in Table 1, and the key steps are marked in the following figure.

    image.png

    As shown in the preceding figure, the connection to which the long-running transaction in Session 1 belongs is closed because of preemptible MDL optimization. This ensures that the DDL statement in Session 2 and the new transaction in Session 3 can be executed.

    Distributed metadata deadlock detection

    PolarDB-X supports distributed metadata deadlock detection. This feature can detect and break deadlocks that are caused by MDLs during the execution of DDL statements. This way, DDL statements and transactions can be properly executed.

    Supported versions

    This feature is supported only in PolarDB-X V5.4.17-16952556 or later.

    Description

    PolarDB-X periodically scans the lock waits between transactions and DDL statements. If a deadlock is detected, PolarDB-X automatically stops a common transaction to ensure the normal execution of DDL statements and other transactions.

    The following table describes a typical deadlock scenario that involves an MDL.

    Table 2

    Step

    Session 1

    Session 2

    Session 3

    Session 4

    1

    begin;

    -- Start Transaction 1.

    begin;

    -- Start Transaction 2.

    -

    -

    2

    insert into t1 values(1);

    -- Acquire the read MDL of t1.

    insert into t2 values(1);

    -- Acquire the read MDL of t2.

    -

    -

    3

    -

    -

    alter table t1 add column col int;

    -- Initiate DDL Statement 1 and attempt to acquire the write MDL of t1. This statement is blocked.

    alter table t2 add column col int;

    -- Initiate DDL Statement 2 and attempt to acquire the write MDL of t2. This statement is blocked.

    4

    insert into t2 values(2);

    -- Attempt to acquire the read MDL of t2. However, because an MDL is a fair lock, this statement is blocked by DDL Statement 2.

    insert into t1 values(2);

    -- Attempt to obtain the read MDL of t1. However, because an MDL is a fair lock, this statement is blocked by DDL Statement 1.

    -

    -

    The following figure shows how the deadlock issue described in Table 2 is resolved in a PolarDB-X instance for which the distributed metadata deadlock detection feature is enabled. The steps are the same as those described in Table 2, and the key steps are marked in the following figure.

    image.png

    After the deadlock scenario is generated in the sequence specified in Table 2, PolarDB-X detects the deadlock and closes the connection to which Transaction 2 belongs. Transaction 2 is rolled back, and Transaction 1, DDL Statement 1, and DDL Statement 2 are executed.

    Dual-version MDL optimization

    For logically executed DDL statements, PolarDB-X supports dual-version metadata and the corresponding dual-version MDLs. This ensures that no table is locked, and the traffic does not drop to zero throughout the execution of such DDL statements.

    Limits

    This feature is supported for all logically executed DDL statements in PolarDB-X. For more information about how to check whether a DDL statement is logically executed, see Online DDL.

    Description

    The logical execution of DDL statements in PolarDB-X is implemented based on the Online Schema Change (OSC) capability. A metadata version for such DDL statements is divided into multiple minor versions. This way, the metadata version can securely evolve in an PolarDB-X instance. For example, multiple times of version switching for ABSENT(Vn), DELETE_ONLY(Vn +1), WRITE_ONLY(Vn +2), and PUBLISH(Vn +3) are involved throughout the process of executing the CREATE GLOBAL INDEX statement in PolarDB-X. In addition, PolarDB-X associates MDLs with metadata minor versions. This way, the metadata of each version has a separate MDL.

    Based on the OSC mechanism, two metadata versions are allowed not only between different compute nodes (CNs) in a cluster but also within one CN at a specific point in time. Therefore, each time the metadata version evolves for logically executed DDL statements, PolarDB-X acquires the MDL write lock corresponding only to the metadata of the earlier version. In this case, new transactions can access the metadata of the new version and request the read MDL corresponding to the metadata of the new version.

    Based on the dual-version MDL optimization, PolarDB-X ensures that no table is locked and the traffic does not drop to zero throughout the execution of logically executed DDL statements.