Deep Dive — FOR UPDATE

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

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

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

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

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

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

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

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

-- 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 — lock lifetime = transaction lifetime.
• PRAGMA EXCEPTION_INIT — naming -54, -30006, -60.
• FETCH FIRST — why it cannot be used here.
• DBMS_PARALLEL_EXECUTE — the other way to split a table across workers (by rowid range, no locks needed between workers).
• CURSOR FOR LOOP — WHERE CURRENT OF.