How does Oracle manage transaction commits and rollbacks using redo and undo mechanisms?

Oracle ensures transaction durability and consistency using redo for commits and undo for rollbacks. During a commit, Oracle generates a commit record in the redo log buffer, marks changes as permanent in redo logs, and updates the SCN to reflect the current database state. For rollbacks, Oracle uses undo data to revert changes by reconstructing original values and maintaining read consistency. Redo records changes for recovery, stored in online redo logs, while undo stores how to reverse changes for rollback and consistency, kept in undo tablespaces. Key practical considerations include: 1) frequent commits increase redo but reduce undo usage; 2) large transactions may trigger "snapshot too old" errors if undo retention is insufficient; 3) monitor redo via V$SYSSTAT or trace files; 4) ensure adequate undo tablespace size and retention for long queries; and 5) check wait events like log file sync or enq: TX – contention when troubleshooting performance issues.

Oracle handles transaction commits and rollbacks using two core mechanisms: redo and undo. These work together to ensure data consistency, durability, and the ability to recover from failures.

What happens during a commit

When you issue a COMMIT, Oracle doesn’t immediately write all changes to the data files. Instead, it does a few key things:

• It generates a commit record in the redo log buffer.
• All the changes made by the transaction up to that point are marked as permanent in the redo logs.
• The System Change Number (SCN) is updated to reflect the current point in time of the database.

So even if the actual data blocks haven’t been written to disk yet, the fact that the transaction committed is recorded in the redo log. That’s why Oracle can guarantee durability — once a commit returns successfully, the change is safe, even in case of a crash.

How rollback works with undo

If a transaction is rolled back (either explicitly or due to an error), Oracle uses undo data to revert the changes.

Each change made by a transaction is first logged in the undo tablespace before the actual data is modified. This allows Oracle to:

• Reconstruct the original values of changed data.
• Maintain read consistency for other sessions that might be querying the same data concurrently.

For example, if Session A updates a row and Session B queries it before the commit, Session B sees the original version of the row using the undo data.

Rollback essentially plays back this undo information in reverse, restoring the database to its state before the transaction began.

Redo vs Undo – Key differences

It's easy to mix up redo and undo, but here's how they differ in function:

• Redo:

  • Records what changes were made to the database.
  • Used for recovery — instance recovery, media recovery, etc.
  • Stored in online redo log files.
  • Every change goes through the redo log buffer before being flushed to disk.

• Undo:

  • Records how to undo a change.
  • Used for rollbacks and maintaining read consistency.
  • Stored in undo tablespaces.
  • Also used by Oracle for flashback features and consistent reads.

In short:

Redo is about recording changes for recovery; undo is about preserving old values for rollback and consistency.

Practical implications and tuning considerations

Understanding these mechanisms helps when dealing with performance or recovery issues.

Here are a few practical points:

• Frequent commits reduce the amount of undo needed but increase redo generation. So, in bulk operations, batch your commits wisely.
• Large transactions may cause "snapshot too old" errors if undo retention isn't sufficient.
• You can monitor redo generation via views like V$SYSSTAT (redo size) or trace files.
• For long-running reports or queries, make sure your undo tablespace is large enough and configured with appropriate retention.

If you're troubleshooting performance issues related to redo or undo, checking wait events like log file sync (for redo) or enq: TX – contention (for undo) can help pinpoint bottlenecks.

That’s how Oracle ensures reliable transaction handling under the hood. Not something you see every day, but good to understand when tuning or debugging database behavior.

The above is the detailed content of How does Oracle manage transaction commits and rollbacks using redo and undo mechanisms?. For more information, please follow other related articles on the PHP Chinese website!