## Module 5: Evolving the Schema with Non-Blocking Online DDL

![demo_203-tidb-x-online-ddl.jpg 85%](https://lab-static.pingcap.com/images/2025/11/27/d7bde260caeff9b78d19ea3c1b9c4469ae41ecda.jpg)

In TiDB Cloud, set the system variable `tidb_enable_metadata_lock` to `OFF` to prevent DML transaction locks from blocking DDL operations, ensuring truly online, non-blocking DDL changes.

The major benefit is that DML and DDL statements can be executed concurrently on the same table without blocking each other, as DDL operations are performed asynchronously. This makes it very easy to evolve the table schema in TiDB. In general, as long as your application code is updated in advance to support both the old and new schemas, database administrators (DBAs) can execute DDL operations to change the table structure without introducing any planned business downtime.

### Step 1. Establish two database connections

- Click [Terminal 2](ssh://monitor1-ssh#2) to open one more terminal.

- In [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB if you are not already connected.

  ```bash
  mysql \
   --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \
   -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
  ```

- In [Terminal 2](ssh://monitor1-ssh#2), create another database connection.

  ```bash
  mysql \
   --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \
   -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
  ```

### Step 2. Understand the metadata lock default behavior

- In both [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2), confirm that the metadata lock is enabled by default.

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_enable_metadata_lock';
  ```

  ```output
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | tidb_enable_metadata_lock | ON    |
  +---------------------------+-------+
  1 row in set (0.01 sec)
  ```

- Let's investigate the scenario of adding a column as a schema change.

- In [Terminal 1](ssh://monitor1-ssh#1), execute the following transaction control and DML commands to update a book's record. Note that in addition to the row-level lock, the table metadata lock is also active. Since the transaction has not ended, both locks remain in place after these commands are executed.

  ```sql
  BEGIN;
  UPDATE books SET stock = stock+1 WHERE id = 1;
  ```

  ```output
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  ```

- In [Terminal 2](ssh://monitor1-ssh#2), run the following DDL command to add a column from the same table. Notice that this DDL operation is **blocked** because an active DML transaction is still in progress on the same table in another session.

  ```sql
  ALTER TABLE books ADD COLUMN (valid_for_training INT);
  ```

- Back to [Terminal 1](ssh://monitor1-ssh#1), release the metadata lock by ending the transaction.

  ```sql
  COMMIT;
  ```

  ```output
  Query OK, 0 rows affected (0.02 sec)
  ```

- Return to [Terminal 2](ssh://monitor1-ssh#2). You will notice that the DDL operation only completes after the transaction lock is released—this causes a delay (for example, 1 minute and 37.93 seconds in the sample output). The actual wait time will depend on how long you take to switch to Terminal 1 and end the transaction.

### Step 3. Enable Non-Blocking Online DDL

- In [Terminal 1](ssh://monitor1-ssh#1), execute the following command to turn off metadata lock.

  ```sql
  SET GLOBAL tidb_enable_metadata_lock = OFF;
  ```

- In both [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2), confirm that the metadata lock had been turned off.

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_enable_metadata_lock';
  ```

  ```output
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | tidb_enable_metadata_lock | OFF   |
  +---------------------------+-------+
  1 row in set (0.01 sec)
  ```

- Let's investigate the scenario of adding a column as a schema change again. This time DML will not block DDL.

- In [Terminal 1](ssh://monitor1-ssh#1), execute the following transaction control and DML commands to update a book's record.

  ```sql
  BEGIN;
  UPDATE books SET stock = stock+2 WHERE id = 1;
  ```

  ```output
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  ```

- In [Terminal 2](ssh://monitor1-ssh#2), run the following DDL command to add a column to the same table. You will see that the DDL executes successfully and is not blocked by the ongoing DML operation in the other session. This is the result of Non-Blocking Online DDL.

  ```sql
  ALTER TABLE books ADD COLUMN (isbn INT);
  ```

  ```output
  Query OK, 0 rows affected (0.65 sec)
  ```

- Switch back to [Terminal 1](ssh://monitor1-ssh#1) and attempt to commit the transaction. Because the `books` table schema was changed after the transaction began, the commit will fail and the transaction will be rolled back. This is also a result of Non-Blocking Online DDL.

  ```sql
  COMMIT;
  ```

  ```output
  ERROR 8028 (HY000): Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later]
  ```

- At this point, you may wonder: if Non-Blocking Online DDL can cause in-flight DML operations to fail with an `ERROR 8028` and roll back, why is this beneficial? The key is that DDL commands are typically planned maintenance events. For example, when adding an `isbn` column to the `books` table to meet business requirements, you can first update your application to handle the new column and expect the `ERROR 8028`. If the error occurs, the application can simply retry the transaction. The advantage is that the database does not require your business application (represented here by DML) to halt while DDL operations are running. By combining TiDB's Non-Blocking DDL with a CI/CD-enabled application, you can achieve completely online schema changes without any downtime.

- We also provide hands-on labs for specific programming languages to demonstrate exactly how to work with `ERROR 8028` and Online DDL, such as the Module 5 of [Working with TiDB Cloud using mysql-connector-python](/labs/demo_405) and [Working with TiDB Cloud using JDBC](/labs/demo_701).

In summary, whether to enable or disable the `tidb_enable_metadata_lock` system variable depends on the specific requirements and workload characteristics of your application:

  | `tidb_enable_metadata_lock` | Pros                                               | Cons                                         |
  | :---------------------------| :--------------------------------------------------| :--------------------------------------------|
  | ON (default)                | DML operations are not affected by schema mutation | DML operations can block DDL                 |
  | OFF                         | DML operations won't block DDL                     | DML operations should handle schema mutation |

- Restore the configuration to its default setting.

  ```sql
  SET GLOBAL tidb_enable_metadata_lock = ON;
  ```

- Now, let's move on to the next module to continue our learning.