在回答之前,先了解一下 OceanBase 的 Offline DDL 实现原理。
OceanBase Offline DDL 操作是采用 “重建表” 的方式实现的。
具体而言,Offline DDL 操作会新建一张临时的隐藏表(对用户不可见),然后将原表的数据迁移到新建的表中。完成数据迁移后,临时表会被重命名为原表的名称,并且旧表将被删除。因此,Offline DDL 操作完成后,table_id
会发生变化。
注意,在 Offline DDL 执行期间不允许 DML 操作。
gv$ob_locks
视图解读表锁和对应步骤。gv$session_longops
视图解读 DDL 操作的执行状态和进度。初始表结构。
obclient [evan_db]> show create table t10;
| Table | Create Table
| t10 | CREATE TABLE `t10` (
`a` varchar(12) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
UNIQUE KEY `c_idx` (`c`) BLOCK_SIZE 16384 LOCAL,
KEY `idx_b` (`b`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
1 row in set (0.014 sec)
查看表的 table_id
。
obclient [evan_db]> select distinct(table_id) from oceanbase.dba_ob_table_locations where table_name = 't10';
+----------+
| table_id |
+----------+
| 500037 |
+----------+
1 row in set (0.018 sec)
执行 DDL 操作(更改列类型),耗时 9.707s。
obclient [evan_db]> select count(*) from t10; selectnow();ALTER TABLE t10 MODIFY COLUMN a int(11);select now();
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.030 sec)
+---------------------+
| now() |
+---------------------+
| 2025-08-0517:24:16 |
+---------------------+
1 row in set (0.006 sec)
Query OK, 0 rows affected (9.707 sec)
+---------------------+
| now() |
+---------------------+
| 2025-08-0517:24:26 |
+---------------------+
1 row in set (0.001 sec)
下面,我将解读 DDL 执行过程中产生的表锁及对应步骤的含义。
执行的 sql 内容(脚本执行,间隔 10ms 执行一次)。
SELECT
l.TYPE,
l.ID1,
l.ID2,
l.LMODE,
l.REQUEST,
l.CTIME,
l.BLOCK,
t.trace_id,
t.query_sql
FROM
oceanbase.gv$ob_sql_audit t,
oceanbase.gv$ob_locks l
WHERE
t.tx_id = l.trans_id
AND l.tenant_id = (
SELECT tenant_id
FROM dba_ob_tenants
WHERE tenant_name = 'mysql_tenant'
)
AND l.type = 'TM';
步骤概览
tablet_id=200014
和 table_id=500037
,开启事务。table_id 500040
),复制 t10 结构(含索引)。table_id
切换到 500040,锁释放。备注:
gv$ob_locks
视图,锁类型(type)含义:TM 表示表锁,TX 表示事务锁,TR 表示行锁。tablet_id=200014
和 table_id=500037
施加表锁,保护元数据和数据分片。[2025-08-05 17:24:16.378] TYPE ID1 ID2 LMODE REQUEST CTIME BlOCK trace_id query_sql
TM 200014 NULL X NONE 26502 0 YB420ABA403E-000638C4C465F278-0-0 START TRANSACTION
TM 500037 NULL X NONE 28878 0 YB420ABA403E-000638C4C465F278-0-0 START TRANSACTION
SELECT dep_obj_id, dep_obj_type FROM __all_tenant_dependency WHERE tenant_id = 0 AND ref_obj_id = 500037
table_id=500040
,table_name=__hidden_500040_500037table_schema
),复制 t10(table_id=500037
)结构,准备应用列 a 类型变更(varchar(12) → int(11))。association_table_id=500037
关联原表,tablet_id=200015
为新 tablet。INSERT INTO __all_table (
tenant_id: 0
table_id: 500040
table_name: X'5F5F68696464656E5F3530303034305F3530303033377461626C655F736368656D61' (解码后含义: __hidden_500040_500037table_schema)
schema_version: 1754385856456352
association_table_id: 500037
tablet_id: 200015
)
INSERT /*+use_plan_cache(none)*/ INTO __all_column (
-- 列 a
table_id: 500040
column_id: 16
column_name: X'61' (解码后含义: a)
schema_version: 1754385856456352
)
INSERT INTO __all_ddl_operation (
SCHEMA_VERSION: 1754385856456352
DATABASE_ID: 500002
TABLE_ID: 500040
OPERATION_TYPE: 4 (创建表)
)
INSERT INTO __all_ddl_operation (
SCHEMA_VERSION: 1754385856538896
DATABASE_ID: 500002
TABLE_ID: 500037
OPERATION_TYPE: 3 (修改表)
)
UPDATE __all_table SET (
schema_version: 1754385856538896
association_table_id: 500040
define_user_id: 200001
table_name: X'743130' (解码后含义: t10)
tablet_id: 200014
object_status: 1
) WHERE tenant_id = 0 AND table_id = 500037
__all_table_history
和 __all_table
中 table_id=500037
的元数据,验证 schema_version=1754385856538896。SELECT tenant_id, table_id, schema_version, table_name, database_id, table_type, ...
FROM __all_table_history
WHERE tenant_id = 0 AND table_id = 500037 AND schema_version = 1754385856538896
EXCEPT
SELECT tenant_id, table_id, schema_version, table_name, database_id, table_type, ...
FROM __all_table
WHERE tenant_id = 0 AND table_id = 500037;
SELECT column_value FROM __all_core_table WHERE TABLE_NAME = '__all_global_stat' AND COLUMN_NAME = 'ddl_epoch' FOR UPDATE
table_id
从 500037 切换到 500040(新 schema,列 a 为 int(11))。INSERT INTO __all_ddl_operation (
SCHEMA_VERSION: 1754385865777640
TENANT_ID: 0
EXEC_TENANT_ID: 1006
USER_ID: 0
DATABASE_ID: 0
TABLE_ID: 0
TABLE_NAME: X''
OPERATION_TYPE: 1503
DDL_STMT_STR: X''
gmt_modified: now(6)
)
[2025-08-05 17:24:26.632] Result is empty
执行的 sql 内容(脚本执行,间隔 10ms 执行一次,重复输出省略)。
select * from oceanbase.gv$session_longops\G
[2025-08-05 17:24:16.706] Query Results:
----------------------------------------
----------------------------------------
*************************** 1. row ***************************
SID: 2025-08-05 17:24:16
输出为空,省略...
*************************** 2. row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modify column
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-05 17:24:17
ELAPSED_SECONDS: 0
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-05 17:24:17
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: PREPARE
----------------------------------------
[2025-08-0517:24:16.863] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:16
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 0
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:17
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: WAIT TRANS END, PENDING_TX_ID: 0
----------------------------------------
[2025-08-0517:24:17.032] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:17
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 0
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:17
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: OBTAIN SNAPSHOT
----------------------------------------
[2025-08-0517:24:17.203] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:17
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 1
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:17
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: REPLICA BUILD, ROW_SCANNED: 0, ROW_SORTED: 0, ROW_INSERTED: 0
----------------------------------------
中间输出省略...
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:19
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 3
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:19
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: REPLICA BUILD, ROW_SCANNED: 300000, ROW_SORTED: 600000, ROW_INSERTED: 300000
----------------------------------------
[2025-08-0517:24:19.508] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:19
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 3
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:20
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ ]
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:20
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: createindex
TARGET: 500041
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:20
ELAPSED_SECONDS: 0
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:20
MESSAGE: TENANT_ID: 1006, TASK_ID: 33632, STATUS: WAIT TRANS END, PENDING_TX_ID: 0
*************************** 3.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 3
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:20
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 33632 ]
----------------------------------------
[2025-08-0517:24:20.146] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:20
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: createindex
TARGET: 500041
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:20
ELAPSED_SECONDS: 0
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:20
MESSAGE: TENANT_ID: 1006, TASK_ID: 33632, STATUS: REPLICA BUILD, ROW_SCANNED: 0, ROW_SORTED: 0, ROW_INSERTED: 0
*************************** 3.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 4
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:20
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 33632 ]
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:22
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 6
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:23
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 3363233861 ]
*************************** 3.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: createindex
TARGET: 500042
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:22
ELAPSED_SECONDS: 0
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:23
MESSAGE: TENANT_ID: 1006, TASK_ID: 33861, STATUS: REPLICA BUILD, ROW_SCANNED: 0, ROW_SORTED: 0, ROW_INSERTED: 0
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:24
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 8
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:24
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 3363233861 ]
*************************** 3.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: createindex
TARGET: 500042
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:22
ELAPSED_SECONDS: 2
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:24
MESSAGE: TENANT_ID: 1006, TASK_ID: 33861, STATUS: REPLICA BUILD, ROW_SCANNED: 300000, ROW_SORTED: 300000, ROW_INSERTED: 300000
----------------------------------------
[2025-08-0517:24:24.407] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:24
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 8
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:24
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 3363233861 ]
*************************** 3.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: createindex
TARGET: 500042
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:22
ELAPSED_SECONDS: 2
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:24
MESSAGE: TENANT_ID: 1006, TASK_ID: 33861, STATUS: ENABLEINDEX
----------------------------------------
[2025-08-0517:24:24.748] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:24
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 8
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:25
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 3363233861 ]
*************************** 3.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: createindex
TARGET: 500042
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:22
ELAPSED_SECONDS: 2
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:25
MESSAGE: TENANT_ID: 1006, TASK_ID: 33861, STATUS: CLEAN ONSUCCESS
----------------------------------------
[2025-08-0517:24:24.936] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:24
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 8
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:25
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: TAKE EFFECT
----------------------------------------
[2025-08-0517:24:25.482] Query Results:
----------------------------------------
----------------------------------------
*************************** 1.row ***************************
SID: 2025-08-0517:24:25
输出为空,省略...
*************************** 2.row ***************************
SID: -1
TRACE_ID: YB420ABA403E-000638C4C465F278-0-0
OPNAME: modifycolumn
TARGET: 500040
SVR_IP: 10.186.64.61
SVR_PORT: 2882
START_TIME: 2025-08-0517:24:17
ELAPSED_SECONDS: 9
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2025-08-0517:24:26
MESSAGE: TENANT_ID: 1006, TASK_ID: 33491, STATUS: CLEAN ONSUCCESS
----------------------------------------
不同时间点对应的操作:
obclient [evan_db]> select b.table_name,a.tenant_id,a.task_id,a.object_id,a.target_object_id,a.ret_code,a.affected_rows,a.user_message,a.dba_message,a.ddl_type,a.gmt_create,a.gmt_modified from oceanbase.__all_ddl_error_message a join oceanbase.__all_table b on a.object_id = b.table_id order by a.gmt_modified desc limit 1;
+------------+-----------+---------+-----------+------------------+----------+---------------+----------------+----------------+----------+----------------------------+----------------------------+
| table_name | tenant_id | task_id | object_id | target_object_id | ret_code | affected_rows | user_message | dba_message | ddl_type | gmt_create | gmt_modified |
+------------+-----------+---------+-----------+------------------+----------+---------------+----------------+----------------+----------+----------------------------+----------------------------+
| t10 | 0 | 33491 | 500040 | -1 | 0 | 0 | Successful ddl | Successful ddl | 1001 | 2025-08-05 17:24:25.966025 | 2025-08-05 17:24:25.966025 |
+------------+-----------+---------+-----------+------------------+----------+---------------+----------------+----------------+----------+----------------------------+----------------------------+
1 row in set (0.057 sec)
此时,ret_code = 0
和 user_message = Successful ddl
且 dba_message = Successful ddl
,可以认为 DDL 执行成功。
查看表结构。
obclient [evan_db]> show create table t10;
| Table | Create Table
| t10 | CREATE TABLE `t10` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
UNIQUE KEY `c_idx` (`c`) BLOCK_SIZE 16384 LOCAL,
KEY `idx_b` (`b`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
1 row in set (0.035 sec)
可以看到 a 列 由 varchar(12) 转为 int(11)。
查看表的 table_id
。
obclient [evan_db]> select distinct(table_id) from oceanbase.dba_ob_table_locations where table_name = 't10';
+----------+
| table_id |
+----------+
| 500040 |
+----------+
1 row in set (0.014 sec)
可以看到,由于是 Offline DDL , table_id
已经由 500037 变成了 500040。
若未及时查询视图或想查看更为详细的步骤,可以通过日志打印信息观察 DDL 过程。
基于 gv$session_longops
查询结果的 trace_id
将涉及的信息保存到文本文件。
grep YB420ABA403E-000638C4C465F278-0-0 observer.log* > /tmp/YB420ABA403E-000638C4C465F278-0-0_DDL.txt
observer.log:[2025-08-05 17:24:16.323510] INFO [SQL.RESV] calc_ddl_parallelism (ob_ddl_resolver.cpp:11093) [21619][T1006_L0_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=9] calc ddl parallelism(parallelism=1)
可见 parallelism=1
,表明 DDL 操作的并行度为 1,即串行执行,没有并行。
observer.log:[2025-08-05 17:24:16.417567] INFO [STORAGE.TABLELOCK] send_rpc_task_ (ob_table_lock_service.cpp:2125) [22578][T1006_L0_G20][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=98] send table lock rpc(ret=0, ret="OB_SUCCESS", retry_ctx.send_rpc_count_=1, addr="10.186.64.62:2882", request={task_type_:0, lsid_:{id:1}, params_:[{lock_id:{obj_type:1, obj_id:500037}, lock_mode:1, owner_id:{id:33491}, op_type:2, is_deadlock_avoid_enabled:false, is_try_lock:true, expired_time:1754387356415961, schema_version:0}], tx_desc_:{this:0x7f0fec5cb8a0, tx_id:{txid:33490}, state:2, addr:"10.186.64.62:2882", tenant_id:1006, session_id:1, assoc_session_id:1, xid:NULL, xa_mode:"", xa_start_addr:"0.0.0.0:0", access_mode:0, tx_consistency_type:0, isolation:1, snapshot_version:{val:18446744073709551615, v:3}, snapshot_scn:0, active_scn:1754385856406340, op_sn:6, alloc_ts:1754385856406340, active_ts:1754385856406340, commit_ts:-1, finish_ts:-1, timeout_us:999917948, lock_timeout_us:-1, expire_ts:1754386856324288, coord_id:{id:-1}, parts:[{id:{id:1}, addr:"10.186.64.62:2882", epoch:515276525793764, first_scn:1754385856409625, last_scn:1754385856409625, last_touch_ts:1}], exec_info_reap_ts:0, commit_version:{val:18446744073709551615, v:3}, commit_times:0, commit_cb:null, cluster_id:1730815150, cluster_version:17180000522, flags_.SHADOW:false, flags_.INTERRUPTED:false, flags_.BLOCK:false, flags_.REPLICA:false, can_elr:false, cflict_txs:[], abort_cause:0, commit_expire_ts:0, commit_task_.is_registered():false, modified_tables:[], ref:2}})
打印日志时存在活跃事务才会输出结果。
[root@10-186-64-62 tmp]# grep "WAIT_TRANS_END" YB420ABA403E-000638C4C465F278-0-0_DDL.txt
[root@10-186-64-62 tmp]#
__hidden_500040_500037table_schema
(table_id=500040)迁移数据。table_id
会变成 500040,相当于将隐藏表更名为 t10 ,而原表会进行删除。__pk_increment
)排序,确保插入的数据按顺序排列。[root@10-186-64-62 tmp]# grep 'INTO `evan_db`.`__hidden_500040_500037table_schema' YB420ABA403E-000638C4C465F278-0-0_DDL.txt
observer.log:[2025-08-05 17:24:19.380073] INFO [SERVER] process_final (ob_inner_sql_connection.cpp:662) [21600][T1006_L0_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=40] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"INSERT /*+ monitor enable_parallel_dml parallel(1) opt_param('ddl_execution_id', 0) opt_param('ddl_task_id', 33491) opt_param('enable_newsort', 'false') use_px */INTO `evan_db`.`__hidden_500040_500037table_schema`(`__pk_increment`, `a`, `b`, `c`, `d`) SELECT /*+ index(`t10` primary) ob_ddl_schema_version(`t10`, 1754385856538896) */ `__pk_increment` AS `__pk_increment`, `a` AS `a`, `b` AS `b`, `c` AS `c`, `d` AS `d` from `evan_db`.`t10` as of snapshot 1754385856939933337 order by 1"}, process_time=2048956)
observer.log:[2025-08-05 17:24:17.626133] INFO [STORAGE.BLKMGR] alloc_block (ob_block_manager.cpp:318) [603][T1006_PX_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=27] successfully alloc block(macro_id=[9078](ver=0,mode=0,seq=43366))
observer.log:[2025-08-05 17:24:17.630278] INFO [STORAGE.BLKMGR] async_write (ob_macro_block_handle.cpp:178) [603][T1006_PX_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=40] Async write macro block(macro_id_=[9078](ver=0,mode=0,seq=43366))
observer.log:[2025-08-05 17:24:17.835883] INFO [SQL.ENG] preprocess_dump (ob_sort_op_impl.cpp:968) [603][T1006_PX_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=195] trace sort need dump(dumped=true, mem_context_->used()=30742088, get_memory_limit()=1473665, profile_.get_cache_size()=30242949, profile_.get_expect_size()=1473665, sql_mem_processor_.get_data_size()=26673152)
...
observer.log:[2025-08-05 17:24:18.004129] INFO [STORAGE] open (ob_tmp_file.cpp:1393) [603][T1006_PX_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=49] succeed to open a tmp file(fd=381, dir=17185094, common::lbt()="0x13978d65 0xfb1005f 0xfb0f383 0xcf552eb 0xcf4bd7c 0xcf4c675 0xcf4cd45 0x57ae0d5 0xcf4e46f 0xd2ae0d6 0x578b997 0xd2a766e 0xd2a66fe 0x571fd54 0x56f1652 0x55b805c 0x5884afc 0xd4780da 0xd476b57 0x5884397 0xd210a3d 0x55b8c6c 0xb95484d 0x55b80ae 0xb9a6d7f 0xb953514 0xb995a91 0x5658889 0xddb6b44 0xdded6b4 0xa945ce2 0xa9458eb 0x13c371cf 0x7f105c714ea5 0x7f105c43db0d")
observer.log:[2025-08-05 17:24:18.004175] INFO [SQL.ENG] write_file (ob_chunk_datum_store.cpp:2036) [603][T1006_PX_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=41] open file success(io_.fd=381, io_.dir_id=17185094)
__idx_500040_c_idx
为例,索引表 __idx_500123_idx_b
同理__hidden_500040_500037table_schema
)插入到索引表( __idx_500040_c_idx
),该 SQL 使用快照版本 ( 1754385859935671612 )读取隐藏表数据,并按列 c
和 __pk_increment
排序后插入索引表。observer.log:[2025-08-05 17:24:21.634195] INFO [SERVER] process_final (ob_inner_sql_connection.cpp:662) [21591][T1006_L5_G0][T1006][YB420ABA403E-000638C4C465F278-0-0] [lt=36] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"INSERT /*+ monitor enable_parallel_dml parallel(1) opt_param('ddl_execution_id', 0) opt_param('ddl_task_id', 33632) opt_param('enable_newsort', 'false') use_px */INTO`evan_db`.`__idx_500040_c_idx`(`c`, `__pk_increment`) SELECT/*+ index(`__hidden_500040_500037table_schema` primary) */`c`AS`c`, `__pk_increment`AS`__pk_increment`from`evan_db`.`__hidden_500040_500037table_schema`asofsnapshot1754385859935671612orderby1, 2"}, process_time=1459268)
oceanbase.__all_ddl_error_message
可确认 DDL 执行成功。[root@10-186-64-62 tmp]# grep "report_ddl_error_message" YB420ABA403E-000638C4C465F278-0-0_DDL.txt
[root@10-186-64-62 tmp]#
我们经过对 DDL 操作的详细分析,可以对开篇的两个提问进行回答。
通常会施加表锁,尤其是涉及表结构变更或数据重写的操作,具体可根据前方步骤观察 gv$ob_locks
视图进行确认。
“与数据量有关,需要重整数据” 或 “是否有活跃事务有关”,可查看官网关于 DDL 操作的耗时评估依据表格。
较为准确的耗时评估方法:
table_id
是否发生变化,如果没有变化,说明是 Online DDL,如果发生变化,则是 Offline DDL。本文关键字:#OceanBase #离线DDL