# Deep Dive — FOR UPDATE

> A row lock lasts until COMMIT. Everything about locking well is about making that interval short, and about not waiting for someone else's.

Used in: [Chapter 8 — Locking](../course-plan.md) · Appendix entry: [FOR UPDATE](../appendix.md#for-update) · Related: [COMMIT](commit.md), [PRAGMA EXCEPTION_INIT](pragma-exception_init.md), [FETCH FIRST](fetch-first.md), [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md), [CURSOR FOR LOOP](cursor-for-loop.md)

---

## 1. What It Is and When

`SELECT ... FOR UPDATE` locks the selected rows (a TX row lock on each, in the block's ITL) so that no other transaction can update, delete or `FOR UPDATE` them until you commit or roll back. Oracle never escalates row locks to block or table locks — you can hold 50M row locks — but you *hold* them until the transaction ends.

Three questions decide the clause:
1. **What if a row is already locked?** Wait forever (default), wait *n* seconds (`WAIT n`), fail now (`NOWAIT`), or pretend it is not there (`SKIP LOCKED`).
2. **Which rows?** All rows of the result, or only those of one table in a join (`OF col`).
3. **For how long?** Until your `COMMIT` — so lock a batch, not a table.

`SKIP LOCKED` + `ROWNUM` + a short transaction is the pattern for **several workers sharing one table** without coordination. It is what Advanced Queuing does internally.

## 2. Syntax

```sql
SELECT ... FROM <t1> [, <t2> ...] WHERE ...
FOR UPDATE [OF <t>.<col> [, ...]] [NOWAIT | WAIT <seconds> | SKIP LOCKED];

-- in a cursor
CURSOR c IS SELECT ... FOR UPDATE;
UPDATE <t> SET ... WHERE CURRENT OF c;
DELETE FROM <t> WHERE CURRENT OF c;
```

- Locks are taken when rows are **fetched** (explicit cursor) or when the statement executes (`SELECT INTO` / `BULK COLLECT`).
- Not allowed with `DISTINCT`, `GROUP BY`, aggregates, `UNION`, `FETCH FIRST` (`ORA-02014`), or in a function called from SQL.
- `OF t.col` — in a join, lock only rows of table `t` (the column is just a way to name the table).
- Errors: `ORA-00054` (`NOWAIT`, row busy), `ORA-30006` (`WAIT n` expired), `ORA-00060` (deadlock), `ORA-01002` (fetch after commit closed the cursor).

## 3. Examples on the Course Schema

### 3a. Work queue — many workers, no collisions

```sql
CREATE OR REPLACE PROCEDURE worker (p_batch PLS_INTEGER DEFAULT 1000) IS
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  LOOP
    SELECT order_id BULK COLLECT INTO l_ids
    FROM   orders
    WHERE  status = 'NEW' AND ROWNUM <= p_batch
    FOR UPDATE SKIP LOCKED;                    -- take up to 1000 rows nobody else holds
    EXIT WHEN l_ids.COUNT = 0;                 -- nothing free: done (or all taken by others)

    FOR i IN 1 .. l_ids.COUNT LOOP
      process_order(l_ids(i));
    END LOOP;
    FORALL i IN 1 .. l_ids.COUNT
      UPDATE orders SET status = 'DONE' WHERE order_id = l_ids(i);
    COMMIT;                                    -- locks released; next batch
  END LOOP;
END;
/
-- start 8 of these from 8 sessions (or 8 scheduler jobs): the table is divided automatically
```

Each worker locks only its batch; `SKIP LOCKED` makes workers ignore each other's rows; `COMMIT` per batch keeps every lock alive for seconds. A worker that dies releases its locks (PMON), and its unfinished rows are still `'NEW'` for the others.

### 3b. NOWAIT — fail fast on a busy row

```sql
DECLARE
  e_busy EXCEPTION;  PRAGMA EXCEPTION_INIT(e_busy, -54);
  v_status orders.status%TYPE;
BEGIN
  SELECT status INTO v_status FROM orders WHERE order_id = :id FOR UPDATE NOWAIT;
  -- ... change it ...
  COMMIT;
EXCEPTION
  WHEN e_busy THEN
    RAISE_APPLICATION_ERROR(-20030, 'order ' || :id || ' is being edited by another user');
END;
```

Interactive code should never wait forever on a lock — the user sees a frozen screen. `NOWAIT` turns it into a message.

### 3c. WAIT n — bounded patience

```sql
DECLARE
  e_timeout EXCEPTION;  PRAGMA EXCEPTION_INIT(e_timeout, -30006);
BEGIN
  SELECT order_id INTO v_id FROM orders WHERE order_id = :id FOR UPDATE WAIT 5;
EXCEPTION
  WHEN e_timeout THEN
    log_error('lock wait on order ' || :id, SQLCODE, SQLERRM);
    RAISE;
END;
```

### 3d. OF — lock one table in a join

```sql
SELECT o.order_id, o.amount, c.tier
FROM   orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE  o.status = 'NEW' AND ROWNUM <= 1000
FOR UPDATE OF o.status SKIP LOCKED;            -- locks orders rows only; customers untouched
```

Without `OF`, the matching `customers` rows are locked too — and every other worker's `SKIP LOCKED` would skip *orders* whose customer row is locked. Lock what you will change.

### 3e. WHERE CURRENT OF — and why batch jobs avoid it

```sql
DECLARE
  CURSOR c IS SELECT order_id FROM orders WHERE status = 'NEW' FOR UPDATE;
BEGIN
  FOR r IN c LOOP
    UPDATE orders SET status = 'DONE' WHERE CURRENT OF c;
    -- COMMIT;   → ORA-01002 on the next fetch
  END LOOP;
  COMMIT;            -- one commit at the end: all 50M locks held throughout
END;
```

`WHERE CURRENT OF` ties the update to a `FOR UPDATE` cursor, and a `FOR UPDATE` cursor cannot survive a commit. For batches: fresh `SELECT ... FOR UPDATE SKIP LOCKED` per batch (3a), `UPDATE ... WHERE order_id = ...` (or by `ROWID`), commit, repeat.

### 3f. Deadlock avoidance — lock in a consistent order

```sql
-- session A locks 1001 then 1002; session B locks 1002 then 1001 → ORA-00060 for one of them
-- fix: everyone locks in the same order
SELECT order_id BULK COLLECT INTO l_ids
FROM   orders
WHERE  order_id IN (SELECT COLUMN_VALUE FROM TABLE(:ids))
ORDER  BY order_id                                    -- deterministic lock order
FOR UPDATE;
```

`ORDER BY` with `FOR UPDATE` is allowed (unlike `GROUP BY`); rows are locked in fetch order.

### 3g. The optimistic alternative — no lock at all

```sql
-- read
SELECT status, ORA_ROWSCN INTO v_status, v_scn FROM orders WHERE order_id = :id;
-- ... user thinks for two minutes ...
-- write only if unchanged
UPDATE orders SET status = 'APPROVED'
WHERE  order_id = :id AND ORA_ROWSCN = v_scn;         -- table created with ROWDEPENDENCIES for row-level SCN
IF SQL%ROWCOUNT = 0 THEN
  RAISE_APPLICATION_ERROR(-20031, 'order was changed by someone else; reload');
END IF;
```

A version column works the same way. No lock is held while the human is thinking — which is the only correct answer for interactive edits.

## 4. What Breaks at Scale

- **Lock duration, not lock count.** 50M row locks cost nothing to hold; holding them for 6 hours blocks every other writer for 6 hours, and every one of those writers is a session waiting on `enq: TX - row lock contention` — the DBA's phone rings.
- **Waiting.** A batch job with plain `FOR UPDATE` that hits one row held by a forgotten interactive session waits forever. `NOWAIT`/`WAIT n`/`SKIP LOCKED` — always one of them in batch code.
- **`SKIP LOCKED` + `ROWNUM`** can return fewer rows than `ROWNUM` asks for (locked rows are skipped after `ROWNUM` counted them in some plans). Loop until `COUNT = 0` (3a); do not assume a full batch.
- **Hot rows.** Many workers wanting the *same* few rows (a counter table, a "last processed" row) serialize on them no matter what. Design them away: sequences, per-worker rows, `SKIP LOCKED` queues.
- **ORA-01555 for others.** A long transaction with many locked rows also keeps undo busy; long-running queries elsewhere start failing with snapshot-too-old.
- **Undo for `FOR UPDATE` itself is small** (a lock byte per row), but the `UPDATE` that follows is not — batch both.

## 5. Pitfalls

1. `FOR UPDATE` with no `NOWAIT`/`WAIT`/`SKIP LOCKED` in a batch job — an unbounded wait.
2. `COMMIT` inside a `FOR UPDATE` cursor loop — `ORA-01002`.
3. `FETCH FIRST n ROWS ONLY ... FOR UPDATE` — `ORA-02014`; use `ROWNUM`.
4. `ORDER BY` + `ROWNUM` + `SKIP LOCKED` in one query — `ROWNUM` is applied *before* `ORDER BY`; you get "some 1000 rows, sorted", not "the first 1000 by order_date". Sort in an inline view is not allowed with `FOR UPDATE` either — accept unordered batches, or use a number range instead.
5. Locking through a join without `OF` — locks in the parent table stop other workers.
6. `SELECT ... FOR UPDATE` in a function invoked from SQL — `ORA-14551`.
7. Expecting `SKIP LOCKED` to skip rows locked by *your own* transaction — it does not; they are yours and are returned.
8. Locks held by a client that disconnected uncleanly stay until PMON notices (seconds to minutes); `NOWAIT` errors in that window are normal.

## 6. Related

- [COMMIT](commit.md) — lock lifetime = transaction lifetime.
- [PRAGMA EXCEPTION_INIT](pragma-exception_init.md) — naming `-54`, `-30006`, `-60`.
- [FETCH FIRST](fetch-first.md) — why it cannot be used here.
- [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md) — the other way to split a table across workers (by rowid range, no locks needed between workers).
- [CURSOR FOR LOOP](cursor-for-loop.md) — `WHERE CURRENT OF`.