潍坊网站定制模板建站,学院网站开发网站定位,上传文档网站开发,电商平台取名字大全小伙伴们#xff0c;在从前的oracle版本中#xff0c;对表进行并行 DML后#xff0c;必须COMMIT的步骤可以改变了。在Oracle Database 23ai中#xff0c;Unrestricted Parallel DML#xff08;无限制并行DML#xff09; 新特性#xff0c;改进后将解决了长期困扰开…小伙伴们在从前的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) */
SYSCDB$ROOT show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________2 PDB$SEED READ ONLY NO3 FREEPDB1 READ WRITE NO
SYSCDB$ROOT alter session set containerFREEPDB1;
Session altered.
SYSCDB$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 synonympointed to a table or view that did not exist.To find existing user tables and views, query theALL_TABLES and ALL_VIEWS data dictionary views. Certainprivileges may be required to access the table. If anapplication returned this message, then the table that theapplication tried to access did not exist in the database, orthe 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.
SYSCDB$ROOT CREATE TABLE test31 AS SELECT * FROM dba_users;Table TEST31 created.
SYSCDB$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-12838SYStest19 !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; -- 返回正确行数
SYSCDB$ROOT SELECT COUNT(*) FROM TEST31;
SYSCDB$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.
SYSCDB$ROOT INSERT /* PARALLEL(test31, 4) */ INTO test31 SELECT * FROM test31;
272 rows inserted.
SYSCDB$ROOT UPDATE /* PARALLEL(test31, 4) */ test31 SET USERNAME LOWER(USERNAME);
544 rows updated.
-- 返回最终行数无错误
SELECT COUNT(*) FROM test31;
SYSCDB$ROOT SELECT COUNT(*) FROM TEST31;COUNT(*)
___________544-- 直接路径追加再次更新将会报错
INSERT /* APPEND */ INTO test31 SELECT * FROM test31;
SYSCDB$ROOT INSERT /* APPEND */ INTO test31 SELECT * FROM test31;
544 rows inserted.
SYSCDB$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/
SYSCDB$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训练等场景提供了原生级支持。
