# Deep Dive — SAVEPOINT

> Undo part of a transaction without ending it — and know exactly what Oracle rolls back on its own, because it is less than most people think.

Used in: [Chapter 5 — COMMIT Strategy](../course-plan.md) · Appendix entry: [SAVEPOINT](../appendix.md#savepoint) · Related: [COMMIT](commit.md), [EXCEPTION](exception.md), [FORALL](forall.md), [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md)

---

## 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

| Situation | What is rolled back |
| --- | --- |
| A single SQL statement fails (constraint, conversion, `ORA-01555`…) | **That statement only** — statement-level rollback. Earlier statements in the transaction stay. |
| A `FORALL` (without `SAVE EXCEPTIONS`) fails at element 7,000 | The whole `FORALL` — it is one statement. |
| A stored procedure raises an unhandled exception to its PL/SQL caller | **Nothing beyond the failing statement.** DML the procedure completed before the error stays in the transaction. |
| An unhandled exception escapes the top-level call (SQL*Plus, JDBC, a scheduler job) | The client performs a rollback of the whole call (statement-level rollback of the outermost statement) — in SQL*Plus and most drivers that is the entire anonymous block / procedure call. |

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

```sql
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

```sql
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

```sql
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…

```sql
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

```sql
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

```sql
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

```sql
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](commit.md) — the other end of the transaction.
- [EXCEPTION](exception.md) — handler structure where the `ROLLBACK TO` lives.
- [FORALL](forall.md) — statement-level atomicity of array DML.
- [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md) — a separate transaction with its own savepoints.