# Deep Dive — COMMIT

> A COMMIT is a wait for the disk. Its frequency decides how long the job runs, how much undo it holds, and where it can restart from.

Used in: [Chapter 5 — COMMIT Strategy](../course-plan.md) · Appendix entry: [COMMIT](../appendix.md#commit) · Related: [SAVEPOINT](savepoint.md), [LIMIT](limit.md), [FOR UPDATE](for-update.md), [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md), [TRUNCATE](truncate.md)

---

## 1. What It Is and When

`COMMIT` makes a transaction permanent: the log writer (LGWR) flushes the redo for it to disk, the session waits for that write (`log file sync`), locks are released, and the changes become visible to other sessions. The wait is the point: it is typically 0.5–3 ms on decent storage, and it is **serial per session**.

That gives the arithmetic of Chapter 5:

| Commit frequency | Commits for 500M rows | Time in `log file sync` alone (at 1 ms) |
| --- | --- | --- |
| every row | 500,000,000 | ~5.8 days |
| every 1,000 rows | 500,000 | ~8 minutes |
| every 50,000 rows | 10,000 | ~10 seconds |
| once | 1 | ~1 ms (and 500M rows of undo held) |

The right frequency is the largest batch whose undo the system can hold comfortably and whose loss on failure you can afford to redo — in practice, one commit per [LIMIT](limit.md) batch of 10K–100K rows.

## 2. Syntax

```sql
COMMIT [WORK] [COMMENT '<text>'] [WRITE [WAIT | NOWAIT] [IMMEDIATE | BATCH]];
COMMIT [WORK] FORCE '<xid>' [, <scn>];      -- in-doubt distributed transactions (DBA)
```

- `WRITE WAIT` (default) — return after the redo is on disk. `WRITE NOWAIT` — return immediately; the redo is written later. A crash in between loses the transaction, although the session was told it committed.
- `WRITE IMMEDIATE` (default) — LGWR writes now. `WRITE BATCH` — LGWR may buffer and write with the next batch.
- `COMMIT_WAIT` / `COMMIT_LOGGING` parameters set the same at session or system level.
- Implicit commits: any DDL (including `TRUNCATE`, `ALTER`, `CREATE`), `EXECUTE IMMEDIATE` of DDL, normal session exit, and the end of an autonomous transaction block that committed.

## 3. Examples on the Course Schema

### 3a. Batch commit — the pattern

```sql
DECLARE
  CURSOR c IS SELECT order_id FROM orders WHERE status = 'NEW';
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_ids LIMIT 50000;
    EXIT WHEN l_ids.COUNT = 0;
    FORALL i IN 1 .. l_ids.COUNT
      UPDATE orders SET status = 'PROCESSED' WHERE order_id = l_ids(i);
    COMMIT;
  END LOOP;
  CLOSE c;
END;
```

### 3b. Seeing the cost — commits and log file sync

```sql
-- before and after the job, same session:
SELECT n.name, s.value
FROM   v$mystat s JOIN v$statname n ON n.statistic# = s.statistic#
WHERE  n.name IN ('user commits', 'redo size', 'redo synch time');

SELECT event, total_waits, time_waited_micro / 1e6 AS sec
FROM   v$session_event
WHERE  sid = SYS_CONTEXT('USERENV', 'SID') AND event = 'log file sync';
```

Run the per-row version and the batch version of the same job and compare `user commits` and `log file sync` time. The batch version's redo size is about the same; the wait time is what collapses.

### 3c. Restartable by design

The cursor in 3a selects `status = 'NEW'` and each batch sets `'PROCESSED'`. Kill the job after 3 hours, rerun it, and it picks up exactly the rows that are left — no bookkeeping table needed. Two conditions make a job restartable:

1. Each committed batch changes the data so the batch is excluded from the driving query.
2. The work in a batch is idempotent or is entirely inside that batch's transaction.

When the data itself has no "done" marker, keep one:

```sql
CREATE TABLE job_progress (job_name VARCHAR2(30) PRIMARY KEY, last_order_id NUMBER);

-- driving query
SELECT order_id FROM orders WHERE order_id > :last_order_id ORDER BY order_id;
-- in each batch, same transaction as the work:
UPDATE job_progress SET last_order_id = l_ids(l_ids.COUNT) WHERE job_name = 'reprice';
COMMIT;
```

### 3d. Fetch across commit and ORA-01555

The cursor in 3a stays open while batches commit — legal in Oracle ("fetch across commit"). The risk: the cursor's read-consistent view is as of `OPEN`; every block it still has to read must be reconstructed from undo if it changed since. The job's own updates change those blocks. On a 6-hour run, undo for the early blocks gets overwritten → `ORA-01555: snapshot too old`.

Defenses, in order of preference:
1. Make the job shorter (bulk, parallel, chunks — [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md) gives each chunk its own short cursor).
2. Drive by a **new query per batch** instead of one long-open cursor (`FETCH FIRST` / `ROWNUM` with a restart key, as in 3c) — each batch has a fresh snapshot.
3. Ask the DBA for larger undo / longer `UNDO_RETENTION`.

### 3e. NOWAIT for what may be lost

```sql
CREATE OR REPLACE PROCEDURE log_event (p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO event_log (logged_at, msg) VALUES (SYSTIMESTAMP, p_msg);
  COMMIT WRITE NOWAIT BATCH;      -- an event lost in a crash is acceptable; a 1 ms wait per event is not
END;
/
```

Only for data whose loss in a crash is acceptable: debug logs, metrics, progress hints. Never for business data.

### 3f. The DDL surprise

```sql
BEGIN
  UPDATE orders SET status = 'X' WHERE order_id = 1;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE orders_stage';   -- DDL: commits the UPDATE above, silently
  RAISE_APPLICATION_ERROR(-20001, 'oops');            -- nothing left to roll back
END;
```

Put DDL before or after the transactional work, never inside it.

## 4. What Breaks at Scale

- **Per-row commits** turn a CPU-bound job into an I/O-latency-bound one. Nothing else you do to the code matters until this is fixed.
- **One giant transaction** holds undo for hours (blocking undo reuse for everyone), holds every row lock until the end ([FOR UPDATE](for-update.md)), and rolls back for as long as it ran if it fails. Rollback of 500M rows can take longer than the forward work.
- **Commit inside a loop over a `FOR UPDATE` cursor** raises `ORA-01002` on the next fetch — the lock-holding cursor is closed by the commit.
- **Commit inside a called procedure** decides the transaction for a caller who did not ask. A library procedure should not commit; the job that owns the transaction should.
- **Triggers** cannot commit (`ORA-04092`) — except through an autonomous transaction, which is almost always the wrong design for anything but logging.

## 5. Pitfalls

1. `COMMIT` per row.
2. `COMMIT` in a `FOR UPDATE` cursor loop — `ORA-01002`.
3. A "commit every N rows" job that is not restartable — after a crash, rerunning it processes rows twice.
4. DDL (including `TRUNCATE` via `EXECUTE IMMEDIATE`) in the middle of a transaction — implicit commit.
5. `COMMIT WRITE NOWAIT` on business data — durable until it is not.
6. Counting on `SQL%ROWCOUNT` after `COMMIT` — it is reset to NULL by the commit. Read it before.
7. Long-running cursor + frequent commits + small undo — `ORA-01555` in hour three. Design for it (3d), do not discover it.

## 6. Related

- [SAVEPOINT](savepoint.md) — partial rollback inside a transaction.
- [LIMIT](limit.md) — the batch that becomes the commit unit.
- [FOR UPDATE](for-update.md) — lock lifetime = transaction lifetime.
- [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md) — a commit that does not touch the main transaction.
- [TRUNCATE](truncate.md) — DDL and its implicit commit.