Online DDL and offline DDL operations

Online DDL

• Definition: During online DDL operations, users can still access the database, including performing read and write operations.
• Advantages: It has minimal impact on business operations and allows DDL operations to be executed while the system is running. It is typically used in scenarios with high availability requirements.
• Characteristics: Online DDL operations usually support concurrent access and require fewer locks, thereby reducing downtime.

Offline DDL

• Definition: During offline DDL operations, the relevant tables are locked, preventing users from accessing the database's related data.
• Advantages: It is simple to implement and suitable for situations where the impact on business operations is minimal.
• Characteristics: Offline DDL operations can cause downtime, affecting normal query and transaction processing.

Transactional Consistency

• Transaction: A transaction is a set of operations that either all succeed or all fail, and it ensures data consistency during its execution.
• Online DDL and offline DDL operations are typically independent. In some database management systems, DDL operations themselves may not be supported by transactions (especially offline DDL).

Transaction wait strategy

• Online DDL: Typically, you do not need to wait for the current transaction to end. It can be executed concurrently.
• Offline DDL: Typically, you need to wait for the current transaction to end. This is because the related tables are locked during the execution.

The following table lists the Online DDL operations supported in the MySQL mode of OceanBase Database V4.x.

The following table lists the Offline DDL operations supported in the MySQL mode of OceanBase Database V4.x.

How to determine whether a DDL operation is online

In OceanBase Database, whether a DDL operation is online or offline has a significant impact on business operations. This topic describes how to determine whether a DDL operation is online and provides the operation steps and considerations. For operations such as column type changes or mixed DDL operations, it is recommended to verify whether the operation is online before execution.

How to determine whether a DDL operation is online

For some operations, it is difficult to determine whether the operation is online or offline. Therefore, it is recommended to verify whether the operation is online in the case of mixed DDL operations.

Principle of offline DDL:

Offline DDL operations in OceanBase Database use the "rebuild table" method. Specifically, an offline DDL operation creates a temporary hidden table (which is invisible to users), migrates data from the original table to the new table, and then renames the temporary table to the original table name while deleting the original table. Therefore, the table_id changes after an offline DDL operation, and no DML operations are allowed during the DDL operation.

Based on this principle, you can determine whether a DDL operation is online.

Method:

Execute the following SQL statements before and after the DDL operation to check whether the table_id changes. If the table_id does not change, the DDL operation is online. If the table_id changes, the DDL operation is offline.

select distinct(table_id) from oceanbase.DBA_OB_TABLE_LOCATIONS where table_name='xxx';

Steps:

Follow the steps below to determine whether a DDL operation is online.

1. Create an empty table for verification:

   CREATE TABLE t10(a INT, b INT);
   

2. View the current table_id:

   SELECT distinct(table_id)
   FROM oceanbase.DBA_OB_TABLE_LOCATIONS
   WHERE table_name='t10';
   

3. Execute the expected DDL operation:

   ALTER TABLE t10 ADD c INT, ADD CONSTRAINT c_idx UNIQUE(c);
   

4. View the table_id again:

   SELECT distinct(table_id)
   FROM oceanbase.DBA_OB_TABLE_LOCATIONS
   WHERE table_name='t10';
   

5. If the table_id returned in step 2 and step 4 is the same, the DDL operation is online. If the table_id changes, the DDL operation is offline.