Oracle 提交时，实际执行了哪些操作？

原创已于 2025-09-09 12:22:22 修改 · 886 阅读

24 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2025-09-09 12:20:11 首次发布

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
我将为您深入解析Oracle数据库中最为关键的操作之一——提交（COMMIT） 的内部原理。理解这个过程对于掌握Oracle的并发控制、数据一致性和性能调优至关重要。

第一部分：官方定义与核心作用

一、官方定义 (Official Definition)

提交是SQL的一个事务控制语句，它标志着当前事务的所有修改都已得到确认，并使其成为数据库的永久部分。从官方文档的角度看，COMMIT命令指示数据库使事务的所有更改永久化，并释放事务持有的所有锁和资源。

二、核心作用 (Purpose)

保证事务的持久性（Durability）：这是ACID属性中的“D”。一旦提交，即使发生系统故障，事务的结果也必须保持。
释放资源：释放事务持有的所有行锁和表锁，使得其他会话可以修改这些被锁定的数据。
使更改可见：提交后，该事务所做的更改对其他会话变得可见（在READ COMMITTED隔离级别下）。

通俗比喻：
将事务比作在网上商城下单。

DML操作（INSERT/UPDATE/DELETE）：就像你把商品加入购物车、修改数量、删除商品。这些操作只在你的本次“会话”中有效，商城（数据库）不会为这些操作预留库存（锁定了行，防止别人修改）。
COMMIT操作：就像你点击了**“确认订单并付款”**。这个动作一旦完成：
- 持久性：订单就正式生效了，商城必须承认这个订单（数据永久化）。
- 释放锁：商品库存被正式扣减，其他顾客可以查看新的库存量（释放锁）。
- 可见性：其他顾客（会话）现在能看到这个商品的库存减少了（更改可见）。

第二部分：深入底层原理与管理机制

许多人有一个误解，认为COMMIT会将缓冲区缓存（Buffer Cache）中的“脏块”写入数据文件。这是不正确的。 COMMIT的核心操作与重做日志（Redo Log）紧密相关，而非数据文件。

一、提交的详细过程

当一个用户发出COMMIT语句时，会发生以下一系列精妙的操作：

生成提交重做记录 (Generate Commit Redo Record)：
- 服务器进程会在PGA中生成一个极小的重做记录，其中包含事务ID和提交SCN。这个记录是提交操作的“身份证”。
LGWR同步写盘 (LGWR Synchronous Write)：
- 这是整个提交过程中唯一需要等待的、真正的物理I/O操作，也是提交操作的核心代价。
- 日志写入进程（LGWR）会同步地将重做日志缓冲区（Log Buffer）中与该事务相关的所有重做记录（包括刚生成的提交记录）强制写入（Force Write） 到在线重做日志文件（On-Disk Redo Log Files）中。
- “同步” 意味着服务器进程必须等待LGWR完成写盘操作后，才能继续执行。这个等待就是著名的 log file sync 事件。
更新事务状态 (Update Transaction Status)：
- 在重做日志成功写盘后，服务器进程会更新回滚段头（Undo Segment Header）中的事务表（Transaction Table），将该事务的状态标记为“已提交”（Committed）。这个操作本身也会产生重做记录，但LGWR会很快将其写出。
释放锁并清理资源 (Release Locks and Cleanup)：
- 事务持有的所有行锁（TX锁） 被立即释放。这是为什么提交后其他会话可以立即修改这些行的原因。
- 会话的PGA中与该事务相关的资源被清理。
返回成功信息 (Return Success to User)：
- 此时，控制权返回给用户进程，COMMIT操作完成，用户收到“提交完成”的确认。

二、关键原理：为什么先写重做日志？

写入量最小化：重做日志是顺序写入的，而数据块是随机写入的。写入一个小的重做日志记录远比将所有修改过的脏块（可能分散在磁盘各处）写入数据文件要快几个数量级。
保证持久性：只要重做日志写盘成功，即使随后发生实例崩溃，数据库在恢复时也能通过重做日志重演（Redo） 这个事务的所有修改，从而保证数据的持久性。这就是著名的 “Write-Ahead Logging (WAL)” 协议。

三、延迟块清除 (Delayed Block Cleanout) 的联动

这是一个与提交密切相关的优化机制。

现象：提交操作并不会去遍历和更新所有被该事务修改过的数据块。这些数据块的头部ITL事务槽中，仍然标记着该事务为“活动”状态。
过程：当后续的SELECT语句首次读取这些块时，会发现ITL中的事务已提交（通过回滚段头的事务表确认），然后由这个SELECT语句在内存中完成块的清理工作（更新ITL状态为已提交，记录提交SCN）。这个操作被称为“延迟块清除”。
优点：将提交操作的成本降至最低（只需一次小的同步I/O），将清理工作分摊到后续的低峰操作中。

第三部分：原理串联与示例

场景： 用户执行UPDATE employees SET salary = 10000 WHERE employee_id = 123; 然后 COMMIT;。

UPDATE执行：
- 服务器进程找到employee_id=123的数据块，将其读入Buffer Cache（若不在其中）。
- 在回滚段中生成该行修改前的镜像（salary旧值）。
- 在Buffer Cache中修改数据，将salary改为10000。该块变为“脏块”。
- 在重做日志缓冲区中记录如何重做这个修改的重做记录。
- 在数据块的ITL中获得一个行锁，锁定该行。
COMMIT执行：
- 服务器进程生成一个提交重做记录，放入重做日志缓冲区。
- 服务器进程等待LGWR将重做日志缓冲区中从该事务的第一条重做记录到提交记录的所有内容，同步写入磁盘上的重做日志文件。（log file sync等待）。
- LGWR写盘完成后，服务器进程更新回滚段头的事务表，标记事务为已提交。
- 服务器进程释放该事务持有的行锁。
- 控制返回用户：“Commit complete”。
- 注意：那个被修改的、存放salary的数据块仍然在Buffer Cache中，仍然是脏的，并且ITL中可能还显示事务未提交。它将在之后由DBWn进程写入数据文件，并由延迟块清除机制清理。
后续SELECT访问：
- 另一个会话查询employee_id=123。
- 服务器进程读取数据块，发现ITL中有一个“活动”事务。
- 它去回滚段头查看，发现事务已提交。
- 它在内存中对这个数据块执行“延迟块清除”：更新ITL状态，填入提交SCN。
- 然后返回salary=10000这个已提交的数据。

第四部分：争用、等待事件与排查解决

提交操作的主要瓶颈和争用都围绕着重做日志的写入。

1. 日志文件同步等待 (Log File Sync)

等待事件：log file sync
场景：这是提交操作最直接、最常见的等待事件。当大量会话频繁提交小事务时，每个会话都需要等待LGWR将其重做日志写盘。
根本原因：LGWR写入速度跟不上提交的频率。可能的原因：
- 存储I/O性能差：重做日志所在的磁盘慢、I/O带宽不足、I/O延迟高。
- LGWR写入瓶颈：LGWR是单进程，即使存储很快，它处理大量并发写入请求时也可能成为瓶颈。
- 提交过于频繁：应用程序设计问题，例如循环内提交。

排查SQL：

-- 查看log file sync的平均等待时间，单位是毫秒(ms)
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO / TOTAL_WAITS / 1000, 2) AS AVERAGE_MS
FROM V$SYSTEM_EVENT
WHERE EVENT = 'log file sync';

-- 查看重做日志相关的I/O统计
SELECT * FROM V$SYSTEM_STAT WHERE NAME LIKE '%redo%';
-- 重点关注 'redo writes', 'redo write time', 'redo wastage'

解决方案：
- 优化存储I/O：这是最有效的方案。将重做日志文件放在最快、最低延迟、专有的存储设备上（通常是与数据文件隔离的高速SSD）。启用异步I/O（如果OS和存储支持）。
- 优化提交频率：避免在循环内提交，改用批量处理，每处理N条记录提交一次。这将一次log file sync的代价分摊到多条记录上，极大提升吞吐量。
- 考虑使用COMMIT_WRITE（需谨慎）：可以配置提交的持久性级别（如COMMIT WRITE BATCH NOWAIT），但这会牺牲一定的持久性来换取性能，一般不推荐用于核心业务系统。

2. 日志文件并行写入等待 (Log File Parallel Write)

等待事件：log file parallel write
场景：这是LGWR进程的等待事件。当LGWR向多个重做日志成员（Multiplexed Members）执行并行写操作时发生。
关系：如果log file parallel write等待时间很长，它直接会导致前台的log file sync等待时间变长。
解决方案：与log file sync类似，优化存储I/O。检查是否有多路复用的日志成员放在了同一个慢速磁盘上，应该将它们分散到不同的物理磁盘。

3. 提交后查询性能突然下降

场景：一个长时间运行的查询在访问一个刚刚被大批量提交操作修改过的表。
影响：该查询需要承担大量的“延迟块清除”工作，包括访问回滚段和更新数据块ITL。这会导致查询执行时间远长于预期。
解决方案：
- 在批量作业后，对表执行一次全表扫描（如SELECT COUNT(*) FROM table;），让这个无害的查询提前完成延迟块清除工作。

第五部分：常用监控SQL

监控提交效率与负载：

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('user commits', 'user rollbacks', 'transaction rollbacks');

-- 计算平均每秒事务数(TPS)
SELECT (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'user commits') /
       (SELECT MAX(STARTUP_TIME) FROM V$INSTANCE) TPS
FROM DUAL;

监控重做日志生成量：

SELECT * FROM V$SYSSTAT WHERE NAME = 'redo size';
-- 高的redo size通常伴随着频繁的提交

查看当前正在等待log file sync的会话：

SELECT SID, SERIAL#, SQL_ID, EVENT, SECONDS_IN_WAIT, STATE
FROM V$SESSION
WHERE EVENT = 'log file sync';

总结

官方总结：提交（COMMIT）操作是Oracle实现事务持久性（Durability）的核心机制。其本质是一个同步的日志写操作（Write-Ahead Logging），由LGWR进程将事务的所有重做记录强制写入在线重做日志文件。提交过程会释放行锁并更新回滚段头中的事务状态，但数据块的清理工作会延迟到后续的读操作中完成（延迟块清除）。其性能主要受重做日志I/O性能的影响，表现为log file sync等待事件。

通俗总结：提交的核心是“敲章认证”，而不是“搬货入库”。

真正做的事：领导（服务器进程）对秘书（LGWR）说：“把这些操作记录（重做日志）立刻拿去归档室（磁盘）存好，存好了告诉我一声。” 领导必须停下手中的活，等待秘书回来报告“已存好”（log file sync等待）。秘书回来后，领导就在会议记录本（回滚段头）上标记“此事务已生效”，并宣布散会（释放锁）。
没做的事：领导不会自己跑去仓库（数据文件）把所有的货物（数据块）按照新记录摆放好。这个“摆货”的工作（写数据文件）会交给仓库管理员（DBWn）之后闲时批量处理。
性能关键：提交快不快，全看秘书跑去归档室的速度快不快（重做日志I/O性能）。如果秘书跑得慢，所有领导都得排队等着她回来（log file sync争用）。

因此，优化提交性能的根本在于：1) 优化归档室的道路（存储I/O）；2) 让领导们减少跑腿次数（批量提交）。深刻理解这一点，是进行Oracle数据库高性能应用设计和高并发调优的基础。

欢迎关注我的公众号《IT小Chen》