Deep Dive — SAVEPOINT

1. What It Is and When

SAVEPOINT name marks a point in the current transaction. ROLLBACK TO name undoes everything after that point — DML, locks acquired by it — and keeps the transaction open with everything before it intact.

Use it when a unit of work inside a bigger transaction may fail and you want to discard that unit only: one batch of a bulk job, one order in a multi-order booking, one step of a multi-step update.

What Oracle rolls back automatically — and what it does not

So: "the procedure failed, so its changes are gone" is false when the procedure was called from PL/SQL and the caller handles the exception. That is exactly the case SAVEPOINT + ROLLBACK TO in the handler is for.

2. Syntax

SAVEPOINT <name>;
ROLLBACK TO [SAVEPOINT] <name>;

• Reusing a name moves the savepoint to the new position; the old one is gone.
• All savepoints are erased by COMMIT, ROLLBACK (without TO), and any implicit commit (DDL).
• Savepoints are per transaction; an autonomous transaction has its own.
• ORA-01086: savepoint never established — rolling back to a name that does not exist in this transaction.

3. Examples on the Course Schema

3a. Discard one batch, keep the job going

DECLARE
  CURSOR c IS SELECT order_id, amount FROM orders WHERE status = 'NEW';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;
    BEGIN
      SAVEPOINT batch_start;
      FORALL i IN 1 .. l_rows.COUNT
        UPDATE orders SET amount = pricing_pkg.reprice(order_id, amount)
        WHERE  order_id = l_rows(i).order_id;
      post_batch_checks(l_rows);              -- may raise: business validation
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO batch_start;              -- this batch only
        log_error('reprice batch starting ' || l_rows(1).order_id, SQLCODE, SQLERRM);
        -- continue with the next batch
    END;
  END LOOP;
  CLOSE c;
END;

Note the COMMIT inside the BEGIN: after it the savepoint no longer exists, which is fine — the next iteration creates a new one before doing work.

3b. Why the savepoint is needed — the procedure that "failed" but left changes

CREATE OR REPLACE PROCEDURE book_order (p_order_id NUMBER) IS
BEGIN
  UPDATE orders SET status = 'BOOKED' WHERE order_id = p_order_id;          -- (1) succeeds
  INSERT INTO shipments (order_id, ship_date) VALUES (p_order_id, SYSDATE); -- (2) fails: FK violation
END;
/
BEGIN
  book_order(1001);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('booking failed: ' || SQLERRM);
    -- (1) is STILL in the transaction. The order is BOOKED with no shipment.
    COMMIT;    -- and now it is permanent
END;

The fix is on either side: the procedure protects itself…

CREATE OR REPLACE PROCEDURE book_order (p_order_id NUMBER) IS
BEGIN
  SAVEPOINT book_order_sp;
  UPDATE orders SET status = 'BOOKED' WHERE order_id = p_order_id;
  INSERT INTO shipments (order_id, ship_date) VALUES (p_order_id, SYSDATE);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO book_order_sp;
    RAISE;
END;
/

…or the caller does (SAVEPOINT before_book; book_order(1001); EXCEPTION WHEN OTHERS THEN ROLLBACK TO before_book;). Doing it in the procedure makes the procedure atomic for every caller.

3c. Statement-level rollback is already atomic — no savepoint needed

BEGIN
  UPDATE orders SET status = 'A' WHERE order_id = 1;          -- stays
  FORALL i IN 1 .. l_rows.COUNT                                -- element 7,000 violates a check
    UPDATE orders SET amount = l_rows(i).amount WHERE order_id = l_rows(i).order_id;
EXCEPTION
  WHEN OTHERS THEN
    -- the FORALL's 6,999 successful updates are ALREADY rolled back (one statement);
    -- the first UPDATE is still there.
    RAISE;
END;

Add a SAVEPOINT before the first UPDATE if the two must succeed or fail together.

3d. Nested savepoints and name reuse

BEGIN
  SAVEPOINT a;
  UPDATE orders SET status = 'X' WHERE order_id = 1;
  SAVEPOINT b;
  UPDATE orders SET status = 'Y' WHERE order_id = 2;
  SAVEPOINT a;                    -- moves 'a' here; the original 'a' is gone
  UPDATE orders SET status = 'Z' WHERE order_id = 3;
  ROLLBACK TO a;                  -- undoes only order 3
  ROLLBACK TO b;                  -- undoes order 2 (b still exists: it is before the new a)
END;

Unique names per logical unit (batch_start, book_order_sp) avoid this class of confusion.

3e. Locks are released with the rollback

SAVEPOINT s1;
UPDATE orders SET status = 'LOCKED' WHERE order_id = 42;      -- row lock taken
ROLLBACK TO s1;                                                 -- row lock released

A savepoint rollback releases the locks acquired after the savepoint — other sessions waiting on those rows proceed. Useful in retry logic after ORA-00060 (deadlock): roll back to the savepoint, wait, try again, without losing the rest of the transaction.

4. What Breaks at Scale

• ROLLBACK TO costs time proportional to the undo generated since the savepoint. Rolling back a 5M-row batch is as slow as doing it. Keep savepoint scopes at batch size.
• A savepoint does not shorten the transaction: undo and locks from before it are held until COMMIT. A job that savepoints every batch but commits only at the end still holds everything.
• Many savepoints in one transaction are cheap (a marker in the undo chain); there is no practical limit that matters.

5. Pitfalls

1. Believing a failed procedure's DML was rolled back — it was not (unless the exception escaped the top level).
2. ROLLBACK TO after a COMMIT — ORA-01086; the commit erased it.
3. Reusing a savepoint name unintentionally — moves it forward, and a later ROLLBACK TO undoes less than expected.
4. DDL between SAVEPOINT and ROLLBACK TO — the DDL committed; ORA-01086.
5. Expecting ROLLBACK TO to undo an autonomous transaction's work — it committed independently.
6. Using ROLLBACK (full) in a library procedure — discards the caller's work too. Library code uses ROLLBACK TO its own savepoint, then RAISE.

6. Related

• COMMIT — the other end of the transaction.
• EXCEPTION — handler structure where the ROLLBACK TO lives.
• FORALL — statement-level atomicity of array DML.
• PRAGMA AUTONOMOUS_TRANSACTION — a separate transaction with its own savepoints.