Oracle Transaction Basics: COMMIT, ROLLBACK, and Savepoints

When operating an Oracle Database, the most critical concept for maintaining data integrity is the “Oracle Transaction.” To prevent unintended data changes or to finalize multiple processes as a single unit, it is necessary to correctly understand transaction control (COMMIT and ROLLBACK).

This article explains the rules for starting and ending transactions, how to utilize savepoints, and operational precautions for developers and beginner DBAs, accompanied by practical examples.

Conclusion: Key Points of Transaction Control (To-Do List)
1. Transaction Basics and Mechanism
2. Oracle SQL Transaction Implementation Steps
3. Hands-on Execution Example (Using Savepoints)
1. Preparation and Execution Code
2. Code Explanation
4. Transaction Management Troubleshooting
5. Operation, Monitoring, and Security Cautions
6. Frequently Asked Questions (FAQ)
Summary

Conclusion: Key Points of Transaction Control (To-Do List)

First, let’s grasp the overall picture of transaction operations.

Start: No special command is required. It starts automatically with the first DML (INSERT/UPDATE/DELETE).
Confirm: Use COMMIT to permanently save changes to the database.
Cancel: Use ROLLBACK to discard changes and return to the state before the transaction started.
Partial Cancel: Set a SAVEPOINT and use ROLLBACK TO to revert to that specific point.
Caution: DDL statements such as CREATE or TRUNCATE automatically commit when executed (Implicit Commit).

1. Transaction Basics and Mechanism

What is a Transaction?

A transaction is “an indivisible unit of a series of processing steps.” A typical example is a bank transfer process (withdrawing from Account A and depositing into Account B). Since it would be problematic if only one side succeeded and the other failed, it guarantees that “either everything succeeds, or everything fails (All or Nothing).”

ACID Properties

These are the four properties that support the reliability of a database.

Atomicity: Everything is executed, or nothing is executed.
Consistency: Maintains a state with no data contradictions (such as integrity constraint violations).
Isolation: Running transactions are not interfered with by others (controlled by isolation levels).
Durability: Once committed, data is not lost even if a failure occurs.

Differences from Other RDBMS (e.g., SQL Server)

In systems like SQL Server, transactions are often explicitly started with BEGIN TRANSACTION. However, in Oracle Database, a transaction starts automatically the moment a DML statement is executed. This difference is important.

2. Oracle SQL Transaction Implementation Steps

Let’s check the flow of operations with a flowchart.

[ Idle State ]
      ↓
[ Execute DML (INSERT/UPDATE, etc.) ]  ← Transaction Auto-start here
      ↓
      ├─→ [ Set SAVEPOINT ] (Optional: Create a return point)
      │        ↓
      │   [ Execute DML (Additional changes) ]
      │        ↓
      ├─→ [ ROLLBACK TO <SP> ] (Optional: Revert to Savepoint)
      ↓
[ End Decision ]
      ├─→ COMMIT   (Confirm: Save all changes) ──┐
      └─→ ROLLBACK (Discard: Cancel all changes) ──┴→ [ Transaction End ]

3. Hands-on Execution Example (Using Savepoints)

Here is a code example that can be executed in SQL*Plus or SQL Developer.

We will assume the following scenario:

Create a test table.
Insert one record (Transaction Start).
Create a Savepoint.
Update data by mistake.
Revert to the Savepoint to correct it.
Finally, Commit to finalize.

Preparation and Execution Code

[!WARNING]

The SQL does not contain multi-byte characters (Japanese) and uses comments only for supplementary explanation. If testing in a real environment, please use a test schema.

-- 1. Setup: Create a test table (DDL auto-commits)
CREATE TABLE trans_test (
    id NUMBER PRIMARY KEY,
    val VARCHAR2(20)
);

-- 2. Start Transaction implicitly by INSERT
INSERT INTO trans_test (id, val) VALUES (1, 'Initial Data');

-- Verify: Data is visible to this session, but not others yet.
SELECT * FROM trans_test;

-- 3. Set a Savepoint
SAVEPOINT sp_before_update;

-- 4. Execute an UPDATE (Assume this is a mistake or tentative change)
UPDATE trans_test SET val = 'Wrong Data' WHERE id = 1;

-- Verify the change
SELECT * FROM trans_test;
-- Result: id=1, val='Wrong Data'

-- 5. Partial Rollback: Go back to the Savepoint
ROLLBACK TO SAVEPOINT sp_before_update;

-- Verify: The UPDATE is undone, but INSERT is still alive
SELECT * FROM trans_test;
-- Result: id=1, val='Initial Data'

-- 6. Commit: Make the INSERT permanent
COMMIT;

Execution result :

SQL> -- 1. Setup: Create a test table (DDL auto-commits)
SQL> CREATE TABLE trans_test (
  2      id NUMBER PRIMARY KEY,
  3      val VARCHAR2(20)
  4  );

Table created.

SQL> -- 2. Start Transaction implicitly by INSERT
SQL> INSERT INTO trans_test (id, val) VALUES (1, 'Initial Data');

1 row created.

SQL> -- Verify: Data is visible to this session, but not others yet.
SQL> SELECT * FROM trans_test;

        ID VAL
---------- --------------------
         1 Initial Data

SQL> -- 3. Set a Savepoint
SQL> SAVEPOINT sp_before_update;

Savepoint created.

SQL> -- 4. Execute an UPDATE (Assume this is a mistake or tentative change)
SQL> UPDATE trans_test SET val = 'Wrong Data' WHERE id = 1;

1 row updated.

SQL> -- Verify the change
SQL> SELECT * FROM trans_test;

        ID VAL
---------- --------------------
         1 Wrong Data

SQL> -- 5. Partial Rollback: Go back to the Savepoint
SQL> ROLLBACK TO SAVEPOINT sp_before_update;

Rollback complete.

SQL> -- Verify: The UPDATE is undone, but INSERT is still alive
SQL> SELECT * FROM trans_test;

        ID VAL
---------- --------------------
         1 Initial Data

SQL> -- 6. Commit: Make the INSERT permanent
SQL> COMMIT;

Commit complete.

Code Explanation

SAVEPOINT sp_before_update;:Assigns a name (label) to the state at this point. Imagine placing a pointer in memory.
ROLLBACK TO SAVEPOINT …;:Cancels only the operations performed after the specified label. The INSERT executed before the label is maintained, so there is no need to start over from scratch.
COMMIT;:Writes to the REDO log and finalizes the data. At this point, the data becomes visible to other users.

4. Transaction Management Troubleshooting

Here are common errors and solutions encountered during transaction operations.

Error Code	Error Content	Cause and Solution
ORA-00054	resource busy and acquire with NOWAIT specified…	Cause: The target row is locked by another transaction, and access was attempted with settings that do not wait. Solution: Wait for the lock to be released or check operational rules.
ORA-02049	time-out: distributed transaction waiting for lock…	Cause: Timed out waiting for a lock at a DB link destination, etc. Solution: Identify the lock holder and release it.
ORA-30036	unable to extend segment by … in undo tablespace	Cause: The transaction is too large, and the change history (UNDO) overflowed. Solution: Split huge updates and commit them separately, or extend the UNDO tablespace.

5. Operation, Monitoring, and Security Cautions

The Trap of Auto Commit

SQL*Plus and some GUI tools have an “Auto Commit” feature. If this is turned ON, a COMMIT happens automatically immediately after an UPDATE, making it irreversible.

Check Method (SQL*Plus): SHOW AUTOCOMMIT
Countermeasure: Always ensure it is OFF when connecting to a production environment.

DDL Implicit Commit

When DDL statements such as CREATE, ALTER, DROP, or TRUNCATE are executed, a COMMIT is implicitly issued immediately before them.

Be careful: if you “INSERT data and then use TRUNCATE to clean up another table,” the inserted data will also be finalized.

Long-term Lock Holding (Blocking)

If you leave a transaction without executing COMMIT or ROLLBACK, the lock on that row (or table) continues to be held. This causes other users’ processes to wait (hang state), which can look like a system failure.

Best Practice: Design transactions to be as short as possible and avoid including user interaction (such as waiting for screen input).

6. Frequently Asked Questions (FAQ)

Q1. I committed by mistake. Can I revert it?

A. You cannot revert it with a standard ROLLBACK.

However, by using Oracle’s Flashback Query feature, you may be able to reference and restore the state “as of 15 minutes ago” (depending on the retention period of UNDO data).

Example: SELECT * FROM trans_test AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE);

Q2. Can I create nested transactions?

A. Oracle Database does not strictly support nested transactions (starting another transaction inside a transaction) in the standard way.

Instead, use SAVEPOINT, or if using PL/SQL, use PRAGMA AUTONOMOUS_TRANSACTION (Autonomous Transaction) to perform an independent commit separate from the main process.

Q3. What is the Transaction Isolation Level?

A. This is a setting that determines how much influence is received from other transactions. Oracle’s default is READ COMMITTED (reads only confirmed data). If strict consistency is required, SERIALIZABLE is used, but be aware that concurrency will decrease.

Summary

Appropriately handling Oracle transactions is a fundamental skill for database engineers.

Start is Automatic: Begins the moment DML is thrown.
End is Mandatory: Always COMMIT or ROLLBACK when processing is finished.
Partial Correction: Inserting a SAVEPOINT in long processes makes recovery easier.
Watch out for DDL: DDL is accompanied by a forced commit before execution.

First, try to experience the behavior in a development environment by intentionally causing errors or using savepoints.

This article explains concepts based on Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Oracle Database SQL Language Reference, 19c