Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 38 ยท Exceptions and Transactions

COMMIT, ROLLBACK and SAVEPOINT in Oracle

Learn how Oracle transactions work: implicit starts, COMMIT, ROLLBACK, SAVEPOINT, statement-level atomicity, read consistency, isolation levels and what causes an implicit commit.

A transaction is a group of changes that succeed or fail together. Moving money between two accounts takes two updates; if only one lands, money appears or disappears. Oracle gives you full ACID transactions (see ACID properties for the theory), and PL/SQL gives you three statements to steer them: COMMIT, ROLLBACK and SAVEPOINT.

Oracle cannot run on this site. The Oracle snippets are read-only. The runnable examples use SQLite, which also has transactions and savepoints with the same names, as stand-ins; the differences are noted where they matter.

When does a transaction start and end?

Oracle has no BEGIN TRANSACTION. A transaction starts automatically with the first DML statement (INSERT, UPDATE, DELETE, MERGE) or SELECT ... FOR UPDATE. It ends when one of these happens:

  • COMMIT: the changes become permanent and visible to others.
  • ROLLBACK: all changes since the transaction began are undone.
  • DDL (CREATE, ALTER, DROP, TRUNCATE, GRANT): Oracle commits before and after the statement automatically. This is the implicit commit, and it surprises many people.
  • A clean exit from SQL*Plus or a normal disconnect usually commits; an abnormal termination rolls back.
SQL
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Until COMMIT runs, other sessions still see the old balances (see read consistency below). If the session crashes first, Oracle rolls the changes back using the undo data.

One transaction

First DML starts it No BEGIN needed
COMMIT Makes it permanent
ROLLBACK Undoes everything
DDL Commits automatically

A transaction is a unit of work, not a single statement.

1 / 2

ROLLBACK and SAVEPOINT

ROLLBACK undoes the whole transaction. A savepoint lets you undo just a part:

SQL
INSERT INTO orders (order_id, customer_id) VALUES (1, 10);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 99, 5);
-- wrong product! undo only the item
ROLLBACK TO SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 42, 5);
COMMIT;

ROLLBACK TO after_order undoes everything after the savepoint but keeps the savepoint itself and the transaction open, so the order insert is still pending. Rolling back to an earlier savepoint erases the later ones. Savepoint names are plain identifiers; reusing a name moves the marker.

SQLite supports the same pattern, so you can watch it happen:

SQL SAVEPOINT and ROLLBACK TO (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Statement-level atomicity

Even without a savepoint, Oracle makes each statement atomic. If an UPDATE touching 1,000 rows fails at row 700 (a constraint violation, say), Oracle undoes the 699 rows already changed by that statement, but earlier statements in the transaction stay in place and the transaction remains open. You can then decide to retry, fix the data, or roll back. Oracle does this with an implicit savepoint taken before every statement.

When a PL/SQL block raises an unhandled exception, the same principle applies to the whole call from the client: its changes are undone. If you catch the exception inside the block, nothing is rolled back for you; handle it explicitly with ROLLBACK or ROLLBACK TO savepoint (see exception handling).

Transfer pattern in PL/SQL

SQL
CREATE OR REPLACE PROCEDURE transfer (p_from NUMBER, p_to NUMBER, p_amount NUMBER) IS
BEGIN
  UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20201, 'Source account not found');
  END IF;

  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20202, 'Target account not found');
  END IF;
  -- no COMMIT: the caller owns the transaction
END;
/

If the second update finds no row, the error propagates and the caller rolls back, undoing the debit as well. A CHECK (balance >= 0) constraint on the table would stop an overdraft at the first update.

The Python stand-in below models "all or nothing" with a snapshot copy, so you can see a failed transfer leave no trace:

Python All or nothing (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Isolation and read consistency

Oracle uses multiversion concurrency control. When you query, Oracle shows the data as of a single point in time, rebuilding old versions from undo data when rows changed meanwhile. The key consequences:

  • Readers do not block writers, and writers do not block readers. A report running for ten minutes sees a consistent picture and holds no locks.
  • Writers block only other writers of the same row.
  • Dirty reads (seeing uncommitted data) never happen.

The default isolation level is READ COMMITTED: each statement sees data committed before the statement started. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE makes the whole transaction see one snapshot and fails with ORA-08177: can't serialize access if another session changed rows you try to change. SET TRANSACTION READ ONLY gives a snapshot of the whole transaction for consistent multi-query reports. A lost update is still possible under read committed when two sessions read, compute and write: use SELECT ... FOR UPDATE or a version column to prevent it (see locking).

Good habits

  • Commit at the end of a logical unit of work, in the code that owns it, rarely inside helper procedures.
  • Do not commit in the middle of a FOR UPDATE cursor loop (it invalidates the cursor) or after every row of a large load: it is slower and breaks atomicity.
  • Remember DDL commits implicitly. Never run DDL in the middle of a transaction you may want to roll back.
  • Keep transactions short. Long ones hold locks and strain undo space (ORA-01555: snapshot too old for long readers).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What begins a transaction in Oracle?

    Oracle starts a transaction implicitly with the first statement that changes or locks data.
  2. 2. You run UPDATE, then CREATE INDEX, then ROLLBACK. What happens to the UPDATE?

    DDL issues an implicit commit before it runs, so the earlier changes are permanent.
  3. 3. What does ROLLBACK TO SAVEPOINT do?

    Earlier work remains pending; you can continue and still commit or roll back later.
  4. 4. In Oracle's default isolation level, a long-running SELECT...

    Multiversion read consistency rebuilds old row versions from undo, so readers and writers do not block each other.

Summary

  • A transaction starts with the first DML and ends with COMMIT, ROLLBACK or DDL (an implicit commit).
  • SAVEPOINT and ROLLBACK TO undo part of a transaction; each statement is also atomic on its own.
  • Oracle gives read-consistent snapshots: readers do not block writers. The default level is READ COMMITTED; SERIALIZABLE and READ ONLY are available.
  • Let the code that owns the business operation commit, and keep transactions short.

Next: Autonomous Transactions and Locking