• 31 Jan, 2026
• 0 Comments
• 4 Mins Read

How Oracle Handles Commit & Rollback

How Oracle Handles Commit & Rollback – Complete Guide for DBAs

In Oracle Database, transaction control is one of the most critical concepts that ensures data integrity and consistency. Every change you make using DML operations (INSERT, UPDATE, DELETE, MERGE) is treated as part of a transaction. These changes are not permanently saved until you issue a COMMIT, and they can be undone using ROLLBACK.

Understanding how Oracle internally handles Commit and Rollback is essential for DBAs, developers, and anyone preparing for Oracle interviews.

Let’s break this down step by step.

What is a Transaction in Oracle?

A transaction is a logical unit of work that consists of one or more SQL statements.

A transaction:

• Starts with the first DML statement

• Ends with COMMIT or ROLLBACK

• Ensures ACID properties:

  • Atomicity

  • Consistency

  • Isolation

  • Durability

Example:

UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 101;
UPDATE accounts SET balance = balance + 1000 WHERE acc_id = 202;

Both statements belong to the same transaction until committed or rolled back.

What Happens When You Modify Data?

When a DML statement runs in Oracle:

Step 1 – Data Change in Buffer Cache

• Oracle modifies the data block in Buffer Cache (memory), not directly on disk.

• The block becomes a dirty buffer.

Step 2 – Undo Data Generated

• Oracle writes the before image of the data to Undo segments.

• This is used for rollback and read consistency.

Step 3 – Redo Generated

• Oracle records the change in Redo Log Buffer.

• This ensures recovery in case of crash.

So every DML creates:

• Undo records

• Redo records

• Dirty buffers in memory

How Oracle Handles COMMIT

A COMMIT makes all changes permanent and visible to other users.

Internal Steps During Commit

1 Commit Request Issued

User executes:

COMMIT;

2 SCN Assigned

• Oracle assigns a System Change Number (SCN) to the transaction.

• SCN acts as a transaction timestamp.

3 Redo Log uffer FluBshed

• LGWR (Log Writer process) writes redo entries from Redo Log Buffer to Online Redo Log files on disk.

• This is the most important step for durability.

4 Commit Record Written

• A commit record with SCN is written to redo logs.

5 Lock Release

• Row locks are released.

• Other sessions can now see the changes.

6 User Gets Commit Success

• Oracle confirms commit only after redo is safely written to disk.

Note: Data blocks themselves may still be in memory. DBWR writes them later – commit does not wait for DBWR.

Key Point: Commit Does NOT Write Data Blocks to Disk Immediately

Many think commit writes table data to disk — this is incorrect.

Commit guarantees:

• Redo is written to disk

• Changes are recoverable

Actual data blocks are written later by DBWR.

How Oracle Handles ROLLBACK

A ROLLBACK undoes all uncommitted changes in a transaction.

Internal Steps During Rollback

1 Rollback Command Issued

2 Undo Records Used

• Oracle reads undo records from Undo segments

• Restores old values in data blocks

3 Dirty Buffers Updated

• Buffer cache blocks are restored to their original state

4 Locks Released

• All row locks are released

5 Changes Disappear

• Other users never see rolled-back data

Partial Rollback Using Savepoints

Oracle allows partial rollback using SAVEPOINT.

Example:

SAVEPOINT sp1;

UPDATE emp SET salary = salary + 1000;

SAVEPOINT sp2;

DELETE FROM emp WHERE empno = 200;

ROLLBACK TO sp2;

Result:

• DELETE is undone

• UPDATE remains

This is useful in complex transactions.

Commit vs Rollback – Internal Behavior Comparison

Role of Undo in Read Consistency

Oracle uses Undo data not only for rollback but also for:

Read Consistency (MVCC)

If one session updates a row and another session queries it:

• Query sees old version

• Oracle reconstructs old data using undo

• No blocking occurs

This is Oracle’s Multi-Version Concurrency Control (MVCC) mechanism.

Background Processes Involved

LGWR (Log Writer)

• Writes redo to redo logs at commit time

• Critical for durability

DBWR (Database Writer)

• Writes dirty buffers to datafiles

• Works independently of commit

SMON

• Uses undo for transaction recovery after crash

What Happens If Database Crashes Before Commit?

If crash happens:

Bitefore Comm

• Changes are not committed

• SMON uses undo to rollback

• Data returns to original state

After Commit

• Redo logs contain commit record

• Oracle replays redo during recovery

• Changes are restored

This ensures Durability.

Best Practices for DBAs & Developers

• Commit only when logical transaction is complete
•  Avoid frequent commits inside loops
• Avoid very long uncommitted transactions
• Monitor undo tablespace usage
• Size redo logs properly
• Use savepoints in complex operations

Common Interview Questions

Q: Does commit write data blocks to disk?
No – it writes redo to disk. DBWR writes data blocks later.

Q: Can rollback happen after commit?
No – once committed, changes are permanent.

Q: Why does rollback generate redo?
Because rollback itself changes data blocks and must be recoverable.

Q: Which process writes redo at commit?
LGWR

Final Thoughts

Commit and Rollback are not just SQL commands – they are backed by a powerful internal mechanism involving Undo segments, Redo logs, SCNs, and background processes. Understanding this flow helps DBAs troubleshoot performance issues, recovery scenarios, and locking problems more effectively.

At Learnomate Technologies, we break down critical Oracle concepts like Commit, Rollback, Undo, and Redo so learners can build strong real-world DBA skills.