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
Appendix entry
COMMIT
Related
SAVEPOINT LIMIT FOR UPDATE PRAGMA AUTONOMOUS_TRANSACTION TRUNCATE

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 frequencyCommits for 500M rowsTime in log file sync alone (at 1 ms)
every row500,000,000~5.8 days
every 1,000 rows500,000~8 minutes
every 50,000 rows10,000~10 seconds
once1~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 batch of 10K–100K rows.

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.

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;

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.

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;

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

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.

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.

  • 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), 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.

  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.