小伙伴们，在从前的oracle版本中，对表进行并行 DML后，必须COMMIT的步骤可以改变了。在Oracle Database 23ai中，Unrestricted Parallel DML（无限制并行DML） 新特性，改进后将解决了长期困扰开发者的“Touch-Once”限制问题。
一、为什么需要Unrestricted Parallel DML
Touch-Once机制
在Oracle 23ai之前的版本（如19c）并行DML操作（如INSERT /*+ PARALLEL */）会触发严格的隔离机制：
- 同一事务中，若表已被并行DML修改，后续任何访问该表的操作（包括查询、更新）都将抛出 ORA-12838: cannot read/modify an object after modifying it in parallel错误。
- 根本原因是为了保证事务一致性：并行DML操作可能使表处于“中间状态”，此时允许读操作可能导致脏读或数据冲突。
23ai的特性改进
Oracle 23ai移除了Touch-Once限制，其核心技术原理如下：
- 事务级版本控制增强 并行DML操作不再使表进入“不可读”状态。事务内部通过多版本控制（MVCC）的增强机制，确保即使并行操作未提交，也能提供一致性视图。
- 无锁保留（Lock-Free Reservations） 引入延迟锁冲突检测机制：
- 更新操作不再立即锁定数据行，而是记录“保留标记”（Reservation Markers）
- 实际锁检查推迟到事务提交时，避免阻塞并发操作
二、老版本 vs 23ai：对比与验证脚本
环境准备
-- 1. 创建测试表
DROP TABLE test31 PURGE;
CREATE TABLE test31 AS SELECT * FROM all_objects;
-- 2. 启用并行DML
ALTER SESSION ENABLE PARALLEL DML;
--disabled
DISABLE_PARALLEL_DML SQL;
--parallel
INSERT /*+ PARALLEL(test31, 4) */
SYS@CDB$ROOT> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
SYS@CDB$ROOT> alter session set container=FREEPDB1;
Session altered.
SYS@CDB$ROOT> DROP TABLE test31 PURGE;
Error starting at line : 1 in command -
DROP TABLE test31 PURGE
Error report -
ORA-00942: table or view "SYS"."TEST31" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
00942. 00000 - "table or view%s does not exist"
*Cause: The specified table or view did not exist, or a synonym
pointed to a table or view that did not exist.
To find existing user tables and views, query the
ALL_TABLES and ALL_VIEWS data dictionary views. Certain
privileges may be required to access the table. If an
application returned this message, then the table that the
application tried to access did not exist in the database, or
the application did not have access to it.
*Action: Check each of the following
- The spelling of the table or view name is correct.
- The referenced table or view name does exist.
- The synonym points to an existing table or view.
SYS@CDB$ROOT> CREATE TABLE test31 AS SELECT * FROM dba_users;
Table TEST31 created.
SYS@CDB$ROOT> ALTER SESSION ENABLE PARALLEL DML;
Session altered.老版本（19c）的Touch-Once限制 验证脚本
drop table t1 purge;
create table t1 as select * from all_objects;
-- 并行插入数据
insert /*+ parallel(t1 4) */ into t1 select /*+ parallel(t1 4)*/ * from t1;
-- 立即查询表 → 触发ORA-12838
SELECT COUNT(*) FROM test31;
第 1 行出现错误:
ORA-12838
SYS@test19> !oerr ora 12838
12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.23 ai的Unrestricted Parallel DML 验证脚本
drop table test31 purge;
create table test31 as select * from all_objects;
alter session enable parallel dml;
-- 1. 并行插入后立即查询 → 成功
insert /*+ parallel(t1 4) */ into test31 select /*+ parallel(t1 4)*/ * from test31;
SELECT COUNT(*) FROM test31; -- 返回正确行数
SYS@CDB$ROOT> SELECT COUNT(*) FROM TEST31;
SYS@CDB$ROOT> SELECT COUNT(*) FROM test31;
COUNT(*)
___________
70201
-- 2. 同一事务内多次并行DML操作
INSERT /*+ PARALLEL(test31, 4) */ INTO test31 SELECT * FROM test31; -- 二次插入
UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME); -- 并行更新
272 rows updated.
SYS@CDB$ROOT> INSERT /*+ PARALLEL(test31, 4) */ INTO test31 SELECT * FROM test31;
272 rows inserted.
SYS@CDB$ROOT> UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME);
544 rows updated.
-- 返回最终行数（无错误）
SELECT COUNT(*) FROM test31;
SYS@CDB$ROOT> SELECT COUNT(*) FROM TEST31;
COUNT(*)
___________
544
-- 直接路径追加，再次更新将会报错
INSERT /*+ APPEND */ INTO test31 SELECT * FROM test31;
SYS@CDB$ROOT> INSERT /*+ APPEND */ INTO test31 SELECT * FROM test31;
544 rows inserted.
SYS@CDB$ROOT> UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME);
Error starting at line : 1 in command -
UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME)
Error report -
ORA-12838: cannot read/modify an object after modifying it in parallel
Help: https://docs.oracle.com/error-help/db/ora-12838/
More Details :
https://docs.oracle.com/error-help/db/ora-12838/
SYS@CDB$ROOT>
-- 返回最终行数发生错误
--SQL Error: ORA-12838: cannot read/modify an object after modifying it in parallel
COMMIT
-- APPEND 需要COMMIT提交后，才能返回最终行数（无错误）
SELECT COUNT(*) FROM test31;三、输出对比
操作 |
19c及更早版本 |
23ai |
并行DML后查询表 |
ORA-12838 错误 |
成功返回结果 |
同一事务多次并行DML |
仅首次成功 |
全部成功 |
混合操作（INSERT/UPDATE） |
事务必须拆分 |
单事务内自由组合 |
四、23ai Unrestricted Parallel DML的应用场景
- ETL流水线优化 ：单事务内完成“清洗→转换→分析”全流程，无需拆分提交：
INSERT /*+ PARALLEL */ INTO target_table SELECT /*+ PARALLEL */ * FROM raw_data;
UPDATE /*+ PARALLEL */ target_table SET status = 'PROCESSED';
SELECT /*+ PARALLEL */ * FROM target_table; -- 即时验证- 实时大事务处理 并行更新的场景中，批量更新账户后立即生成审计报告：
UPDATE /*+ PARALLEL(acc, 8) */ accounts acc SET balance = balance * 1.05;
SELECT SUM(balance) FROM accounts; -- 实时汇总五、验证后体会
- 触发器与约束限制 并行DML仍不支持表上的触发器或级联约束（如DELETE CASCADE）。
- 资源管理建议 并行度设置需留出系统余量（推荐：CPU线程总数 - 2）：
sql
/*+ PARALLEL(test31, 6) */ -- 在8核服务器上设置6线程- 回滚段优化：频繁并行DML需增大UNDO_RETENTION，避免快照过旧（Snapshot Too Old）。
TIPS：
Oracle 23ai的Unrestricted Parallel DML通过事务版本控制增强与无锁保留机制，解决了并行DML的隔离性枷锁，使开发者能在同一事务内自由组合批量操作与即时查询。这一特性大幅简化了大数据处理架构，为实时分析、AI训练等场景提供了原生级支持。