A transaction is a logical unit of work that consists of one or more SQL statements. A transaction: - Starts with the first DML statement Starts with the first DML statement - Ends with COMMIT or ROLLBACK Ends with COMMIT or ROLLBACK - Ensures ACID properties: Atomicity Consistency Isolation Durability Ensures ACID properties: - Atomicity Atomicity - Consistency Consistency - Isolation Isolation - Durability Durability Example: Both statements belong to the same transaction until committed or rolled back.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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

When a DML statement runs in Oracle:

- Oracle modifies the data block in Buffer Cache (memory), not directly on disk. Oracle modifies the data block in Buffer Cache (memory), not directly on disk. - The block becomes a dirty buffer . The block becomes a dirty buffer .

- Oracle writes the before image of the data to Undo segments . Oracle writes the before image of the data to Undo segments . - This is used for rollback and read consistency. This is used for rollback and read consistency.

- Oracle records the change in Redo Log Buffer . Oracle records the change in Redo Log Buffer . - This ensures recovery in case of crash. This ensures recovery in case of crash. So every DML creates: - Undo records Undo records - Redo records Redo records - Dirty buffers in memory Dirty buffers in memory

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

1 Commit Request Issued User executes: 2 SCN Assigned - Oracle assigns a System Change Number (SCN) to the transaction. Oracle assigns a System Change Number (SCN) to the transaction. - SCN acts as a transaction timestamp. 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. 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. This is the most important step for durability. 4 Commit Record Written - A commit record with SCN is written to redo logs. A commit record with SCN is written to redo logs. 5 Lock Release - Row locks are released. Row locks are released. - Other sessions can now see the changes. Other sessions can now see the changes. 6 User Gets Commit Success - Oracle confirms commit only after redo is safely written to disk. 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.

Many think commit writes table data to disk — this is incorrect. Commit guarantees: - Redo is written to disk Redo is written to disk - Changes are recoverable Changes are recoverable Actual data blocks are written later by DBWR .

A ROLLBACK undoes all uncommitted changes in a transaction.

1 Rollback Command Issued 2 Undo Records Used - Oracle reads undo records from Undo segments Oracle reads undo records from Undo segments - Restores old values in data blocks Restores old values in data blocks 3 Dirty Buffers Updated - Buffer cache blocks are restored to their original state Buffer cache blocks are restored to their original state 4 Locks Released - All row locks are released All row locks are released 5 Changes Disappear - Other users never see rolled-back data Other users never see rolled-back data

Oracle allows partial rollback using SAVEPOINT . Example: Result: - DELETE is undone DELETE is undone - UPDATE remains UPDATE remains This is useful in complex transactions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SAVEPOINT
sp1;
UPDATE
emp
SET
salary
=
salary
+
1000
;
SAVEPOINT
sp2;
DELETE
FROM
emp
WHERE
empno
=
200
;
ROLLBACK
TO
sp2;

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

If one session updates a row and another session queries it: - Query sees old version Query sees old version - Oracle reconstructs old data using undo Oracle reconstructs old data using undo - No blocking occurs No blocking occurs This is Oracle’s Multi-Version Concurrency Control (MVCC) mechanism.

- Writes redo to redo logs at commit time Writes redo to redo logs at commit time - Critical for durability Critical for durability

- Writes dirty buffers to datafiles Writes dirty buffers to datafiles - Works independently of commit Works independently of commit

- Uses undo for transaction recovery after crash Uses undo for transaction recovery after crash

If crash happens: Bitefore Comm - Changes are not committed Changes are not committed - SMON uses undo to rollback SMON uses undo to rollback - Data returns to original state Data returns to original state After Commit - Redo logs contain commit record Redo logs contain commit record - Oracle replays redo during recovery Oracle replays redo during recovery - Changes are restored Changes are restored This ensures Durability .

- 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

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

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.