Oracle DDL与特殊事务：当CREATE TABLE按下回车后，内核到底在忙什么？

前言干了这么多年 DBA我发现一个挺有意思的现象绝大多数人对 Oracle 事务的理解都停留在 insert / update / delete 加 commit / rollback 这个层面。顶多再知道个 savepoint能说出DDL 会自动提交就已经算不错的了。但如果你真的去 dump 过块、看过 undo 链、追过递归 SQL你会发现用户层面那条简简单单的 CREATE TABLE在内核眼里根本不是一条语句而是一场涉及数据字典、空间管理、段头格式化的多兵种联合作战。而所谓的自动提交背后藏着一整套递归事务、控制文件事务、自治事务的机制。这篇文章我就从一个最平常的动作聊起——你在 SQL*Plus 里敲下 CREATE TABLE t (id NUMBER);按下回车。光标闪了那么零点几秒返回了 Table created.。这零点几秒里内核到底在忙什么一、CREATE TABLE 不是一条语句是一堆 DML先从最重要的认知颠覆开始DDL 在用户层面是单条语句在内核层面是一堆递归 DML。你以为你执行的是一条 CREATE TABLE实际上 Oracle 替你悄悄地往数据字典基表里插了一大堆行。具体都干了啥我给你列个清单insert/update OBJ$对象登记表你的表在这里有了个户口obj#delete from FET$ / insert into UET$字典管理表空间下空闲区Free Extent转成已用区Used Extentinsert into SEG$段信息登记update TSQ$表空间配额扣减insert into TAB$表定义insert into COL$列定义一列一行维护I_OBJ1、I_OBJ2等字典索引在目标数据文件里格式化段头块segment header注意最后一条——这不只是写字典还涉及物理块的格式化。这也是为什么 CREATE TABLE 会产生 redo所有这些变化都要记 redo log段头块格式化的 redo opcode 是13.1Layer 13 是块格式化层。11.2 之后有了 deferred segment creation延迟段创建空表不建段这个动作会推迟到第一行数据插入时才发生——但那是后话。这些对字典基表的 insert/update/deleteOracle 内部叫递归 SQLrecursive SQL。名字挺形象你的 SQL 触发了 Oracle 自己的 SQL一层套一层。二、隐式提交DDL 最坑人的特性接着说一个坑很多老手都踩过。DDL 有一个特性执行成功会自动提交。但这句话只说了一半。完整的真相是——DDL 开始执行之前会先把你当前会话里所有未提交的 DML 隐式提交掉。也就是说UPDATE emp SET sal sal * 1.1 WHERE deptno 10; -- 没提交CREATE TABLE tmp_t (id NUMBER); -- 这一敲UPDATE 被提交了你本来以为那个 UPDATE 还能 ROLLBACK 反悔结果 CREATE TABLE 一执行反悔的机会没了。DDL 前后的两个隐式提交执行前提交一次成功后再提交一次是 Oracle 的经典行为目的之一是让字典修改和你的事务彻底解耦。所以我的习惯是DDL 永远单独开一个会话执行别和业务 DML 混在一起。这不是洁癖是血泪教训。三、DDL 失败了会全部回滚吗不会这里有个反直觉的点值得好好说说。既然 DDL 内部是一堆递归 DML那它中途失败了比如磁盘满、配额不够是不是把所有递归操作全回滚不是。Oracle 只回滚让数据库恢复一致所必需的部分刻意不回滚空间管理操作。最经典的例子你执行一个大 insert过程中表空间给你分配了新的 extent然后语句失败了。回滚这个语句时extent 的分配不会被回滚——新分配的 extent 就留在那里了。为什么因为失败的语句经常会被重试。如果每次失败都把 extent 还回去下次重试又得重新分配空间管理事务来来回回折腾纯属浪费。Oracle 的设计哲学是空间管理事务次数降到最少一致性恢复只做到够用就行。这个设计思路其实贯穿了 Oracle 内核很多地方不是所有东西都要干净利落性能优先的前提下够用就好。四、递归事务与 SYSTEM undo 段的特权下面聊一个大部分人不知道的机制递归事务Recursive-Level Transaction。递归 SQL 执行时也是要改数据的改字典基表改数据就要有事务保护就可能失败、可能崩溃。所以 Oracle 为递归操作和空间管理事务生成 undo 和 redo——即使它们最终会被隐式提交undo 也不能少这是崩溃恢复的底线。递归事务有几个特点我觉得挺优雅第一递归事务总是和它的顶层事务使用同一个 undo 段。不另起炉灶undo 都记在一个地方恢复的时候顺着一条链走就行。第二递归事务可以使用 SYSTEM undo 段——这是特权。大家知道数据字典基表OBJ$、TAB$、COL$ 这些都放在 SYSTEM 表空间里。Oracle 有一条铁律SYSTEM 回滚段只给 SYSTEM 表空间上的事务用。普通用户事务永远轮不到 SYSTEM undo 段——为什么因为 Oracle 要保证 SYSTEM undo 段里始终有槽位留给递归事务。DDL 随时可能来字典随时可能要改SYSTEM undo 段的槽位是给它们预留的不能让用户事务挤占了。这个设计在 RBU手工回滚段和 AUM自动 undo 管理模式下都成立。你可以理解为SYSTEM undo 段是急诊通道平时空着也不许私家车走因为救护车随时要来。第三递归事务的提交是立即生效的。经典例子是 sequence。对一个没 cache 的 sequence 取 nextvalSELECT seq_t.nextval FROM dual;这个取值动作是一个递归事务它立即提交——不管你外层事务最后是 commit 还是 rollbacksequence 的值已经涨上去了。所以 sequence 有缺口gap是正常的第二个会话永远能拿到递增后的值。想让 sequence 连续无缺口别想了那是拿并发性能换来的Oracle 不干这事。顺带一提Resource Manager 的 UNDO_POOL 指令下递归事务消耗的 undo 是计入顶层事务的顶层事务结束前额度不还回消费组。细节虽小做资源管控排障时可能会遇到。五、ORA-604递归事务的报错哲学递归事务失败时Oracle 报的错误是ORA-00604: error occurred at recursive SQL level 1这句话翻译过来就是你那条 SQL 本身没问题但它触发的内部 SQL 挂了。ORA-604 最让人头疼的地方是它常常把真正的错误藏起来。你只能看到一个递归层级的数字不知道底下到底发生了什么。多数时候 ORA-604 后面会跟着真正的错误比如 ORA-01653 表空间无法扩展但偶尔就是没有只给你一行干巴巴的 604。这时候别慌抓 errorstackALTER SESSION SET events 604 trace name errorstack;然后再执行一遍出问题的操作到 trace 文件里看完整的错误栈真错误就藏在里面。我排障这些年ORA-604 见得多了。经验就一条永远别只盯着 604 本身它只是个报信的真凶在栈里。六、控制文件事务双缓冲的艺术说完了字典再看看另一个特殊的事务——控制文件事务。加数据文件、改文件名、检查点更新、表空间进热备模式、介质恢复……这些操作都要修改控制文件。但控制文件不是普通数据块它不在 buffer cache 里走常规的 redo/undo 流程它有自己的玩法双镜像翻转。控制文件内部维护着两份镜像image。进程要修改控制文件时流程是在 CF Enqueue 的保护下修改非活跃的那份镜像shadow block改完后翻转一个 bit切换活跃/非活跃的角色释放 enqueue熟不熟悉这就是**双缓冲double buffering**的思路——图形学里防画面撕裂用的就是这招。改的是影子翻的是开关读者永远看到的是完整的一致版本。redo 层面也有对应的设计redo 第六层Layer 6的 opcode 保留给控制文件相关的 undo 操作。注意措辞控制文件事务本身不产生 redo但表空间/数据文件这类操作需要 undo 记录防止中途失败这些 undo 记录修改的是回滚段的块而对 undo 块的修改会以 opcode5.1记入 redo。举几个具体的 opcode体会一下这套设计的对称美操作undo opcode含义失败时怎么补救创建表空间6.7remove tablespace把刚建的表空间抹掉创建表空间6.1remove data file把刚加的文件抹掉offline 表空间6.4online失败了就改回 onlinedrop 表空间6.2add data file失败就把文件加回来drop 表空间6.8add tablespace失败就把表空间加回来看出来了吗每条 undo 记录都是正操作的逆操作。创建表空间要记两条 undo6.7 6.1drop 也要记两条6.2 6.8——因为这些操作涉及表空间和数据文件两个实体回滚得分别撤销。这种一一对应的设计让崩溃恢复的逻辑变得极其清晰。七、Savepoint不是存盘是一个地址接下来聊几个大家或多或少听过、但未必理解本质的特殊事务家族。先说 savepoint。SAVEPOINT s1; 这条命令干了什么很多人望文生义以为 Oracle 把当前状态存了一份。不是的——savepoint 没有任何物理落盘动作它只是内存里的一个关联记录savepoint 序列号 UBAUndo Block Address。UBA 是什么undo 块地址。说白了savepoint 就是在 undo 链上插了一面小旗子记住这个位置。内存里有个 ktcsp 结构维护 savepoint 表和对应的 UBA。当你执行 ROLLBACK TO s1 时Oracle 做的事是从事务表里的 undo 链尾端开始逐条应用 undo 记录直到目标 UBA 为止。回滚到那面小旗子的位置停。由此带出 savepoint 的几条行为规则其实都是这个实现的必然结果只回滚 savepoint 之后的语句undo 链回滚到 UBA 为止指定的 savepoint 保留它之后建立的 savepoint 全部丢失旗子在链上链回滚了后面的旗子自然没了释放 savepoint 之后获得的表锁/行锁之前的保留事务保持活跃可以继续最后一条值得展开——这里有个著名的savepoint 不公平性现象做个实验就能看到-- 会话 T1UPDATE t SET val 1 WHERE id 1; -- 改 row1SAVEPOINT s1;UPDATE t SET val 2 WHERE id 2; -- 改 row2-- 会话 T2UPDATE t SET val 99 WHERE id 2; -- 等待row2 被 T1 锁着-- 回到 T1ROLLBACK TO s1; -- 回滚 row2 的修改按上面的规则rollback to s1 会释放 savepoint 之后获得的锁——row2 的行锁应该释放了。但你会发现T2 还在等锁没释放干净更诡异的还在后面这时开个会话 T3 来 update row2T3 能成功而先来的 T2 反而继续等着。这就是 savepoint 锁释放机制的历史遗留行为——rollback to savepoint 在锁释放的排队处理上并不严格公平后来的事务可能插队。知道这个现象的存在将来遇到明明回滚了锁怎么还不放的场景你就不会怀疑人生了。八、自治事务栈里的独立王国savepoint 有个天然缺陷没有 COMMIT TO SAVEPOINT——你不能只提交事务的一部分。但现实中确实有这种需求最典型的就是错误日志表无论主事务成功还是失败错误日志都必须留下来。Oracle 给出的答案是自治事务Autonomous Transaction。思路很直白临时把当前事务挂起开一个完全独立的子事务子事务 commit/rollback 后父事务接着走两边互不影响。CREATE OR REPLACE PROCEDURE log_error (p_msg VARCHAR2) ASPRAGMA AUTONOMOUS_TRANSACTION;BEGININSERT INTO error_log (msg, ts) VALUES (p_msg, SYSDATE);COMMIT; -- 必须显式提交END;/几个关键规则块结束返回前必须显式COMMIT或ROLLBACK否则报 ORA-06519自治事务提交后立即对所有会话可见自治事务看不到父事务未提交的修改它是个独立事务读一致基准不同父事务回滚不影响已提交的自治事务Oracle 内部把事务组织成一个栈结构任何时刻只有栈顶事务可访问挂起的事务压在下面。挂起事务的数量上限由 TRANSACTIONS 参数控制。前面说的递归事务用的也是类似机制——你看内核的设计语言是统一的。ATM 取钱是个经典的教学例子无论你取款成功还是失败余额不足用卡记录usage 表都必须提交——银行可不能因为交易失败就丢掉你用过这张卡这个事实。这种无论成败都要留痕的需求就是自治事务的主场。最后提醒一个坑自治事务和父事务可能互相死锁——父事务持有某行的锁自治事务去改同一行就会等自己的爹而爹在等儿子返回。Oracle 不预防这种死锁只会报个专门的错误。所以用自治事务的函数里千万别碰父事务可能修改的表这是开发者自己的责任。九、串行化事务ORA-8177 与 ITL 槽的故事再往上一个隔离级别Serializable串行化。​​​​​​​SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- 或者ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;效果等同于所有事务一个接一个串行执行你只能看到自己事务的修改 事务开始前已提交的数据。不可重复读、幻读统统防住。初始化参数 SERIALIZABLE 早已废弃别去翻旧文档了。代价呢除了并发度下降最出名的就是ORA-08177: cant serialize access for this transaction。8177 的根源要挖到块结构里。串行化事务构建读一致视图时依赖块里可用的 ITLInterested Transaction List槽。ITL 数量由 INITRANS/MAXTRANS/PCTFREE 决定块内空间够PCTFREE 预留的那部分时 ITL 可以扩展也会复用。索引块还要额外考虑串行化期间可能发生的块分裂。实战经验就三条如果串行化事务生命周期内可能有 N 个独立事务更新同一个块就给它分配 N1 个 ITL建表时把 INITRANS 调大多个事务可能改同一行先SELECT ... FOR UPDATE把行锁住ORA-8177 要在应用设计阶段就考虑怎么处理重试逻辑别等上线了被它打个措手不及十、PDML并行事务的两阶段提交最后一个家族成员并行 DMLPDML。UPDATE /* parallel(sales,4) */ sales SET amount amount * 1.1;一条 PDML 实际是一个协调者 一堆 slave 事务。内部用**两阶段提交2PC**协议保证原子性——但注意失败的 PDML不会在 dba_2pc_pending 里留记录也不需要 distributed option这个 2PC 是实例内部用的跟分布式数据库没关系。PDML 的行为规则有点特殊容易踩坑PDML 之后必须显式 commit/rollback才能发后续 DML如果 PDML 后面紧跟 DDLDDL 会隐式强制提交这个 PDML。比如UPDATE /* parallel(sales,4) */ sales SET amount amount * 1.1;ALTER TABLE sales DROP PARTITION p2023; -- 这个 drop 会强制提交上面的 updatePDML 里不允许SET TRANSACTION还有个性能细节并行 slave 事务如果挤在同一个 undo 段会争用 undo 段头。为减少争用slave 事务应该分散到尽可能多的回滚段上——AUM 模式下 Oracle 通常自己就分好了手工 RBU 时代这是 DBA 要操心的事。想观察并行事务的父子关系查 v$transaction里面有 PTX 标识列ptx_xidusn / ptx_xidslt / ptx_xidsqn 三列指向父事务的 XID一目了然。十一、动手环节亲眼看看内核在干什么纸上得来终觉浅。上面说的这些东西全都可以亲手验证。我给你三个实验。实验 1SQL_TRACE 看 CREATE TABLE 的递归 SQL​​​​​​​ALTER SESSION SET sql_trace TRUE;CREATE TABLE trace_t (id NUMBER, name VARCHAR2(20));ALTER SESSION SET sql_trace FALSE;找到 trace 文件select value from v$diag_info where nameDefault Trace File跑 tkproftkprof orcl_ora_12345.trc out.prf sysno打开 out.prf你能清清楚楚看到对 OBJ$、TAB$、COL$、SEG$ 的递归 insert/update——一条 CREATE TABLE 背后站着多少条 SQL数字会震撼你。实验 2dump 块看段头和 ITL​​​​​​​-- 查表在哪个文件哪个块段头SELECT header_file, header_block FROM dba_segmentsWHERE segment_name TRACE_T AND owner 你的用户名;-- dump 段头块ALTER SYSTEM DUMP DATAFILE BLOCK ;到 trace 文件里找你能看到 ITL 条目、Xid格式是 undo段号.槽位.序列号、Uba 字段——这些就是前面讲的理论的肉身。实验 3追踪 undo 链拿到 Xid 的第一段undo 段号后​​​​​​​SELECT file#, block# FROM undo$ WHERE us# ;定位到 undo 段头块再 dump 段头和 UBA 指向的 undo 块顺着链条一路看下去——一条 DDL 的 undo 足迹就完整地呈现在你眼前了。这三个实验做下来DDL 是一堆递归 DML 空间管理事务 块格式化这句话对你来说就不再是概念而是你亲眼见过的东西。总结​​​​​​​回到开头那个问题CREATE TABLE 按下回车后内核在忙什么现在你可以回答了它在开隐式提交在往 OBJ$/TAB$/COL$/SEG$ 里插行在做 FET$/UET$ 的空间流转在用 SYSTEM undo 段的专属槽位记录递归事务的 undo在以 opcode 13.1 把段头格式化写进 redo在控制文件里翻转双镜像。而这一切的设计语言是统一的undo 记录逆操作、递归事务复用顶层 undo 段、SYSTEM undo 段预留特权槽位、空间管理事务能省则省。Oracle 内核没有魔法只有无数个这样精心设计、互相咬合的小机制。DBA 这行干得越久我越觉得所谓功力就是当别人只看到 Table created. 的时候你脑子里能自动放映出这一整串画面。下次敲 CREATE TABLE 的时候不妨停一秒——你知道内核正在为你忙什么了。今天话题就聊到这欢迎留言交流。觉得内容有用别忘了点赞转发给有需要的朋友回见