How OceanBase Offline DDL Works: Table Locks, Execution Time, and Step‑by‑Step Analysis

1. Customer Questions

• Does OceanBase add a table lock when executing Offline DDL? • How to estimate the execution time of Offline DDL?

2. Offline DDL Overview

2.1 Mechanism

OceanBase implements Offline DDL by creating a hidden temporary table, copying the original table’s data, renaming the hidden table to the original name, and deleting the old table. The table_id changes after the operation.

Note: DML operations are not allowed during Offline DDL execution.

2.2 Test Scenario

We modify the column type of a table t10 with 300,000 rows (no index on the column) and observe the process using gv$ob_locks and gv$session_longops.

obclient [evan_db]> SHOW CREATE TABLE t10;

SELECT DISTINCT table_id FROM oceanbase.dba_ob_table_locations WHERE table_name='t10';

ALTER TABLE t10 MODIFY COLUMN a INT(11);

2.3 DDL Process

2.3.1 Environment

OceanBase version: v4.2.1.10

Tenant type: MySQL

2.3.2 Execution Steps

Lock the table ( tablet_id=200014, table_id=500037) and start a transaction.

Check table dependencies to ensure no conflicts.

Create a hidden table __hidden_500040_500037table_schema (table_id=500040) and copy the schema.

Define the new column definition (change a from VARCHAR(12) to INT(11)).

Record DDL operation in internal logs.

Update t10 metadata to point to the hidden table.

Validate schema consistency between __all_table_history and __all_table.

Synchronize global DDL state.

Complete DDL and switch table_id to 500040.

2.3.3 Lock Observation

SELECT * FROM oceanbase.gv$ob_locks WHERE type='TM' AND table_id=500037;

2.3.4 Progress Monitoring

SELECT * FROM oceanbase.gv$session_longops\\G;

The log shows the DDL task (ID 33491) progressing through states such as PREPARE, WAIT, OBTAIN SNAPSHOT, REPLICA BUILD, COPY DEPENDENT OBJECTS, and finally SUCCESS.

2.3.5 Result Verification

SELECT ret_code, user_message FROM oceanbase.__all_ddl_error_message ORDER BY gmt_modified DESC LIMIT 1;

The query returns ret_code = 0 and user_message = 'Successful ddl', confirming success.

3. Q&A

Q1: Does Offline DDL add a table lock?

Yes, OceanBase acquires a table lock (type TM) during the operation, which can be verified via gv$ob_locks.

Q2: How to estimate Offline DDL execution time?

Execution time depends on data volume and active transactions. Recommended methods include cloning the tenant or migrating data to a test tenant and measuring the DDL duration.

4. Tips

Online DDL does not support mixed operations (e.g., adding a column and an index together); use Offline DDL for such cases.

Check table_id before and after DDL to distinguish Offline from Online DDL.

Run DDL during low‑traffic periods.

5. References

Online DDL and Offline DDL Operations – https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000252799

DDL Status Issues and Handling – https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000569397?back=kb

GV$OB_LOCKS Documentation – https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219780

OceanBase DDL Implementation vs MySQL – https://open.oceanbase.com/blog/10844647504

Source code: ob_schema_service.h – https://github.com/oceanbase/oceanbase/blob/develop/src/share/schema/ob_schema_service.h

Source code: ob_schema_service_sql_impl.cpp – https://github.com/oceanbase/oceanbase/blob/develop/src/share/schema/ob_schema_service_sql_impl.cpp