Complete Guide to Oracle Transaction Control

Understanding COMMIT, ROLLBACK, and SAVEPOINT for Beginners

In Oracle Database, transaction control is a critical concept for maintaining data consistency. This article explains the mechanism of transactions—from the basics to real-world considerations—in a clear and visual manner.

✅ What is a Transaction?
🔄 Transaction Lifecycle Overview
🟩 COMMIT: Finalize Changes
🟥 ROLLBACK: Undo Changes
🟨 SAVEPOINT: Mark Transaction Points
🔄 ROLLBACK TO SAVEPOINT: Partial Undo
🧪 Example: Step-by-Step Execution
1. 🔎 Visual Flow of Operations
🧠 Important Notes on Transaction Control
✅ Summary

✅ What is a Transaction?

A transaction is a logical unit of work that ensures multiple operations are processed either completely or not at all.
Consider the following:

cssコピーする編集する① Withdraw 10,000 yen from Account A  
② Deposit 10,000 yen into Account B

If only one of these operations is executed, data integrity is compromised. Transactions group these operations to ensure all-or-nothing execution.

🔄 Transaction Lifecycle Overview

textコピーする編集する【Transaction Lifecycle】

    ▼ Start SQL execution
    ↓
【Transaction Begins】
    ↓
INSERT / UPDATE / DELETE
    ↓
SAVEPOINT (Optional)
    ↓
COMMIT or ROLLBACK
    ↓
【Transaction Ends】

In Oracle, a transaction begins automatically when a DML (Data Manipulation Language) operation like INSERT, UPDATE, or DELETE is executed.

🟩 COMMIT: Finalize Changes

sqlコピーする編集するCOMMIT;

Permanently saves changes made by DML statements.
Makes the changes visible to other users.
Ends the transaction and releases all associated locks.

textコピーする編集するExample:
INSERT INTO emp VALUES (9999, 'TARO', 'CLERK', 7902, SYSDATE, 3000, NULL, 20);
COMMIT;

🟥 ROLLBACK: Undo Changes

sqlコピーする編集するROLLBACK;

Undoes all changes made since the transaction began.
Useful for canceling unintended or incorrect operations.
Also ends the transaction and releases locks.

textコピーする編集するExample:
DELETE FROM emp WHERE empno = 7788;
ROLLBACK;  -- Undo the deletion

🟨 SAVEPOINT: Mark Transaction Points

sqlコピーする編集するSAVEPOINT sp1;

Sets a marker within the transaction to which you can roll back later.
Allows partial undoing of changes without reverting the entire transaction.

🔄 ROLLBACK TO SAVEPOINT: Partial Undo

sqlコピーする編集するROLLBACK TO sp1;

Reverts the transaction to the previously defined SAVEPOINT.
Keeps earlier changes intact.
Does not end the transaction.

🧪 Example: Step-by-Step Execution

sqlコピーする編集する-- Transaction begins implicitly
UPDATE emp SET sal = sal + 100 WHERE empno = 7839;
SAVEPOINT sp_update;

DELETE FROM emp WHERE empno = 7566;
SAVEPOINT sp_delete;

-- Undo only the DELETE
ROLLBACK TO sp_delete;

-- Finalize the salary update
COMMIT;

🔎 Visual Flow of Operations

textコピーする編集するOperation Flow & State
----------------------------
UPDATE (Increase salary)  → OK
SAVEPOINT sp_update       → Set
DELETE (Remove employee)  → Executed
SAVEPOINT sp_delete       → Set
ROLLBACK TO sp_delete     → DELETE undone only
COMMIT                    → UPDATE committed

🧠 Important Notes on Transaction Control

Caution	Description
Forgetting COMMIT	The transaction may be rolled back if the session ends unexpectedly
Long-running transactions	Can exhaust UNDO space and cause lock contention
Excessive use of SAVEPOINT	May reduce readability and complicate management
Auto-COMMIT with DDL	DDL operations (e.g. CREATE, ALTER) automatically commit and can’t be rolled back

✅ Summary

Oracle’s transaction control ensures the reliability and consistency of applications:

COMMIT: Finalize changes
ROLLBACK: Cancel changes
SAVEPOINT: Allow partial rollbacks

By understanding and applying these controls correctly, you can perform safe and efficient data manipulation in Oracle.

[reference]
11 Transactions