Transactions, COMMIT, ROLLBACK, SAVEPOINT, Implicit Commit Boundaries, and Autonomous Transactions

Learning outcomes

A ServiceHub request updates a work order, writes an audit event, and then discovers that the inventory reservation failed. Which changes should survive? A second developer runs CREATE TABLE “just for debugging” before rolling back and discovers the earlier update is already permanent. Transaction boundaries, not individual DML statements, define the atomic business unit.

1. A transaction is the business unit

After one transaction ends, the next executable SQL statement that needs a transaction starts another. COMMIT makes the transaction durable, erases savepoints, and releases transactional locks. ROLLBACK undoes the transaction. Client settings such as autocommit can cause a client to issue commits automatically, but that is client policy—not a different database transaction model.

INSERT INTO sh07_tx_work_order(work_order_id, status_code, amount)VALUES (701,'OPEN',500);UPDATE sh07_tx_work_orderSET amount = amount + 25WHERE work_order_id = 701;COMMIT;

2. Savepoints are partial rollback markers, not mini-commits

A savepoint names a position inside the current transaction. ROLLBACK TO can undo later work while preserving earlier work in that transaction. The preserved work is still uncommitted and can still be rolled back by a later full ROLLBACK.

SAVEPOINT validated_request;UPDATE sh07_tx_work_orderSET amount = amount + 100WHERE work_order_id = 701;-- Validation discovers that this price adjustment should not survive.ROLLBACK TO validated_request;-- The transaction is still active; decide explicitly.COMMIT;

A failed SQL statement also typically performs statement-level rollback of its own work. Do not interpret that as “Oracle rolled back my whole business transaction.” Application exception paths should explicitly choose commit or rollback.

3. Deliberately wrong: DDL inside a transaction commits earlier DML

Oracle issues an implicit commit before a DDL statement and, when the DDL succeeds, after it as well. The pre-DDL commit means earlier DML can become permanent even if you later issue ROLLBACK.

DROP TABLE sh07_tx_marker IF EXISTS PURGE;DROP TABLE sh07_tx_work_order IF EXISTS PURGE;CREATE TABLE sh07_tx_work_order (    work_order_id NUMBER PRIMARY KEY,    status_code   VARCHAR2(12) NOT NULL,    amount        NUMBER(10,2) NOT NULL);INSERT INTO sh07_tx_work_order VALUES (701,'OPEN',500);COMMIT;UPDATE sh07_tx_work_orderSET amount = 999WHERE work_order_id = 701;-- Not committed by us yet.CREATE TABLE sh07_tx_marker(id NUMBER);-- The preceding UPDATE was committed before this DDL executed.ROLLBACK;SELECT amount FROM sh07_tx_work_order WHERE work_order_id = 701;-- Expected: 999, not 500.

This is why schema migrations and transactional business DML need deliberate separation. A rollback after DDL does not rewind the implicit pre-DDL commit.

4. Autonomous transactions are independent—not nested commits

An autonomous transaction suspends the main transaction, starts an independent transaction context, and must commit or roll back its own work before returning normally. It does not share the main transaction's locks or commit dependency. If the main transaction later rolls back, a committed autonomous log row remains.

DROP TABLE sh07_tx_audit IF EXISTS PURGE;CREATE TABLE sh07_tx_audit (    audit_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,    message_text VARCHAR2(200) NOT NULL,    logged_at    TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL);CREATE OR REPLACE PROCEDURE sh07_log_event(p_text VARCHAR2)AUTHID DEFINERAS    PRAGMA AUTONOMOUS_TRANSACTION;BEGIN    INSERT INTO sh07_tx_audit(message_text) VALUES (p_text);    COMMIT;END;/UPDATE sh07_tx_work_orderSET status_code = 'HOLD'WHERE work_order_id = 701;BEGIN    sh07_log_event('Main transaction placed WO 701 on HOLD');END;/ROLLBACK;SELECT status_code FROM sh07_tx_work_order WHERE work_order_id = 701;SELECT message_text FROM sh07_tx_audit ORDER BY audit_id;-- Main UPDATE rolled back; autonomous audit row remains committed.

5. Autonomous visibility and deadlock hazards

The autonomous routine cannot see the caller's uncommitted changes as though it were the same transaction. Worse, if it tries to access a resource locked by the suspended main transaction, the two contexts can deadlock. For that reason, autonomous logging should normally write to separate append-only logging structures and should not “fix” the same business rows the caller is changing.

6. Read/write versus read-only transaction intent

Oracle also supports explicit transaction characteristics. A read-only transaction provides a transaction-level consistent view for its queries, while the default READ COMMITTED model provides statement-level snapshots. Set transaction characteristics before other statements in that transaction; do not sprinkle them into an already active unit of work.

COMMIT;SET TRANSACTION READ ONLY;SELECT work_order_id, status_code, amountFROM sh07_tx_work_orderORDER BY work_order_id;COMMIT;

7. Cleanup and production judgment

DROP PROCEDURE sh07_log_event;DROP TABLE sh07_tx_audit PURGE;DROP TABLE sh07_tx_marker PURGE;DROP TABLE sh07_tx_work_order PURGE;

Keep transaction scope short enough to control contention but large enough to preserve the true business invariant. Never use arbitrary periodic commits merely to “save progress” if partial commit violates correctness. Treat DDL as a hard transaction boundary. Autonomous work should be rare, isolated, and explicitly justified. No paid option or COMPATIBLE change is required.

8. Summary and next step

Savepoints undo part of a transaction without making earlier work durable, DDL introduces implicit commits, and autonomous transactions have their own commit/rollback lifecycle. Lesson 3 explains how other sessions can still read coherent data while transactions are changing it: SCNs plus undo reconstruct the required snapshot.

Authoritative references

• About DML Statements and Transactions — COMMIT, ROLLBACK, SAVEPOINT, and implicit DDL commits
• Transaction Processing and Control — PL/SQL transaction control
• Autonomous Transactions — independence, visibility, and deadlock cautions
• SET TRANSACTION — transaction isolation and read-only characteristics