Online and offline DDL operations

Online DDL

• Definition: During an online DDL operation, users can continue to access the database, including reading and writing data.
• Advantages: Online DDL operations have minimal impact on business operations and can be performed while the system is running, making them ideal for high-availability scenarios.
• Characteristics: They usually support concurrent access and involve fewer locks, thereby reducing downtime.

Offline DDL

• Definition: During an offline DDL operation, the related tables are locked, preventing users from accessing the corresponding data in the database.
• Advantages: Simple and easy to implement, suitable for scenarios with minimal impact on business operations.
• Characteristics: Offline DDL operations cause some downtime, affecting normal queries and transaction processing.

Transaction correlation

• Transaction: A transaction is a group of operations that must either all succeed or all fail, ensuring data consistency during execution.
• Online and offline DDL operations are usually performed separately. In some database management systems, DDL operations may not be controlled by transactions (especially offline DDL).

Transaction wait strategies

• Online DDL: Typically does not need to wait for the current transaction to end and can be executed concurrently.
• Offline DDL: Usually needs to wait for the current transaction to end since the related table is locked during execution.

The following table describes the online DDL operations supported by OceanBase Database V4.x in MySQL mode.

The following table describes the offline DDL operations supported by OceanBase Database V4.x in MySQL mode.

Determine whether a DDL operation is an online operation

In OceanBase Database, the online/offline attribute of a DDL operation has significant impact on business. This section describes how to determine whether a DDL operation is an online operation. For some DDL operations, such as changing the type of a column or a hybrid DDL operation, we recommend that you first determine whether the operation is an online DDL operation.

Method for determining whether a DDL operation is an online operation

It is impossible to list all online and offline operations. Before you perform a hybrid DDL operation, we recommend that you first determine whether the hybrid DDL operation is an online operation.

Technical mechanism of offline DDL operations:

Offline DDL operations in OceanBase Database adopt the "table rebuild" method. Specifically, an offline DDL operation creates a temporary hidden table (invisible to users) and migrates data from the original table to the new table. After the data migration is completed, the temporary table is renamed to the name of the original table, and the old table is deleted. Therefore, after an offline DDL operation is completed, the table_id changes. You must not perform DML operations on the table during the DDL operation.

You can use this principle to determine whether a DDL operation is online DDL.

Method:

Execute the following SQL statement before and after a DDL operation is performed. If the table ID does not change, the DDL operation is an online operation. If the table ID changes, the DDL operation is an offline operation.

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

Procedure:

Perform the following steps to determine whether a DDL operation is an online operation:

1. Create an empty table for verification.

   CREATE TABLE t10(a INT, b INT);
   

2. Query the current table ID.

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

3. Perform a DDL operation.

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

4. Query the table ID again.

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

5. If the table IDs returned in Step 2 and Step 4 are the same, the DDL operation is an online operation. If they are different, the DDL operation is an offline operation.