Row locks, SKIP LOCKED and scheduler jobs

Three workers take payment jobs from one queue table of six jobs. Without row locks two of them can take the same job and send one payment twice; with a plain FOR UPDATE the second worker waits for the first; with SKIP LOCKED each takes a different job at once.

You will see why a reader in Oracle never waits for a writer, choose what a session does when it meets a locked row, and meet the tools that put row locks to work: a shared job queue, checks that stop lost updates, scheduler jobs that run work in the background, and a way to change one busy number without row locks.

Readers see committed data while a writer holds the row

Oracle’s Concepts guide states the rules in four lines. A row is locked only when a writer changes it, or when SELECT ... FOR UPDATE asks for its lock. A writer blocks another writer of the same row. Queries and writers, though, never hold each other up: while a row is being changed, the database uses undo data to give each query the version that was last committed. That is read consistencyread consistency: The guarantee that a query sees data as committed at one point in time. While a row is being changed, Oracle builds its last committed version from undo data, so readers never wait for writers., and every query gets it for a single point in time, so its rows all come from one committed state.

Follow two sessions through the queue table pay_jobs, where job 1 holds the amount 100:

Session B’s plain SELECT returned at once with the committed 100 while A’s 999 was still uncommitted. B’s SELECT ... FOR UPDATE waited, because it asked for row locks that A held. Waiting belongs to locks, never to reading.

Row locks: wait, NOWAIT, WAIT n or SKIP LOCKED

SELECT ... FOR UPDATE locks the rows it selects until the transaction ends, so no other session can change or lock them first. Oracle’s SELECT page allows it only in a top-level query, not with DISTINCT, set operators, GROUP BY or aggregate functions. What a session does when it meets row locks held by another transaction depends on one clause:

WAIT counts seconds unless MILLISECONDS or MICROSECONDS follows the number. SKIP LOCKED is built, in the SELECT page’s words, for multiconsumer queues: each consumer skips the rows other consumers hold and gets the remaining rows without waiting. The lab script runs every clause in two windows, and the model prints what session B gets while A holds jobs 1 and 2. Press Edit, set HELD = [1, 2, 3, 4, 5, 6] and run it again:

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: two SQL*Plus or SQLcl
-- windows, A and B, both connected as LAB. Run each numbered step in the window it names, in order.
-- Step 1 creates the queue table pay_jobs with six new jobs. The steps then show a reader that does not wait for a
-- writer, and what B gets from SELECT ... FOR UPDATE with each wait clause while A holds jobs 1 and 2.
-- claim_model.py prints the same outcomes. The last step drops the table.

-- 1. Window A: the queue table.
DROP TABLE IF EXISTS pay_jobs PURGE;
CREATE TABLE pay_jobs (
  job_id     NUMBER(10)   CONSTRAINT pay_jobs_pk PRIMARY KEY,
  amount     NUMBER(14, 2) NOT NULL,
  status     VARCHAR2(10) DEFAULT 'new' NOT NULL
             CONSTRAINT pay_jobs_status_ck CHECK (status IN ('new', 'running', 'done', 'failed')),
  attempts   NUMBER(3)    DEFAULT 0 NOT NULL,
  claimed_by VARCHAR2(30)
);
INSERT INTO pay_jobs (job_id, amount) SELECT LEVEL, 100 * LEVEL FROM dual CONNECT BY LEVEL <= 6;
COMMIT;

-- 2. Window A: change job 1 and do not commit.
UPDATE pay_jobs SET amount = 999 WHERE job_id = 1;

-- 3. Window B: a plain query returns at once and reads the committed amount.
SELECT job_id, amount FROM pay_jobs WHERE job_id = 1;
-- Expected: 1, 100. A writer never blocks a reader; the reader sees the last committed value.

-- 4. Window A: undo the change, then lock jobs 1 and 2 for this transaction.
ROLLBACK;
SELECT job_id FROM pay_jobs WHERE job_id IN (1, 2) FOR UPDATE;

-- 5. Window B: each statement meets the two locked rows.
SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id FOR UPDATE NOWAIT;
-- Expected: ORA-00054 at once.
SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id FOR UPDATE WAIT 2;
-- Expected: about 2 seconds pass, then the statement fails because the rows are still locked.
SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id FOR UPDATE SKIP LOCKED;
-- Expected: 3, 4, 5 and 6, now locked by B as well.
ROLLBACK;
SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id FOR UPDATE;
-- Expected: B waits. Go to window A.

-- 6. Window A: end the transaction. B's waiting statement returns 1 to 6 at once.
COMMIT;

-- 7. Window B: release the rows, then drop the table.
ROLLBACK;
DROP TABLE pay_jobs PURGE;

"""for_update_choices.sql in the lab, in two SQL*Plus clients of LAB, A and B, step by step as the script
numbers them. Step 5 ends with a statement that waits for A's locks; its rows arrive after A's COMMIT in
step 6. The lock error names a transaction by numbers that change on every run, shown as <n>."""

from oracle_session import Lab

STEP = r"^-- \d+\. (?P<who>Window A|Window B|Both windows|Clean-up)\b"
WHO = {"Window A": ["A"], "Window B": ["B"], "Both windows": ["A", "B"], "Clean-up": ["A"]}
MASKS = [(r'by \d+ \("usn<<16 \| slot"\)', 'by <n> ("usn<<16 | slot")'), (r'and \d+ \("sequence"\)', 'and <n> ("sequence")'),
         (r"transaction ID(\s+)\d+\.\d+\.\d+", r"transaction ID\1<n>.<n>.<n>")]

with Lab(masks=MASKS) as lab:
    lab.steps("locks/for_update_choices.sql", STEP, WHO, users={"A": "lab", "B": "lab"}, waiting={5}, errors=2)

A> 1. Window A: the queue table.
Table dropped.


Table created.


6 rows created.


Commit complete.

A> 2. Window A: change job 1 and do not commit.
1 row updated.

B> 3. Window B: a plain query returns at once and reads the committed amount.
    JOB_ID     AMOUNT
---------- ----------
         1        100

A> 4. Window A: undo the change, then lock jobs 1 and 2 for this transaction.
Rollback complete.


    JOB_ID
----------
         1
         2

B> 5. Window B: each statement meets the two locked rows.
SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id FOR UPDATE NOWAIT
                   *
ERROR at line 1:
ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction",
Description: "Lock held by a transaction to allow other transactions to wait
for it") because it is currently held by another session. The resource being
locked can be identified by <n> ("usn<<16 | slot") and <n> ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID
<n>.<n>.<n>
Help: https://docs.oracle.com/error-help/db/ora-00054/


SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id FOR UPDATE WAIT 2
                   *
ERROR at line 1:
ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction",
Description: "Lock held by a transaction to allow other transactions to wait
for it") because it is currently held by another session. The resource being
locked can be identified by <n> ("usn<<16 | slot") and <n> ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID
<n>.<n>.<n>
Help: https://docs.oracle.com/error-help/db/ora-00054/



    JOB_ID
----------
         3
         4
         5
         6


Rollback complete.
(B waits)

A> 6. Window A: end the transaction. B's waiting statement returns 1 to 6 at once.
Commit complete.

B> (the statement that waited returns)
    JOB_ID
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

B> 7. Window B: release the rows, then drop the table.
Rollback complete.


Table dropped.

"""What a second session gets from SELECT ... FOR UPDATE while a first session holds row locks, modelled in Python.

A model, not Oracle: it applies the rules of Oracle's SELECT page and its Concepts guide. A row is locked only by a
writer or by SELECT ... FOR UPDATE, and the lock lasts until that transaction commits or rolls back. Without a wait
clause the second session waits; NOWAIT fails at once with ORA-00054; WAIT n waits up to n seconds, then fails; SKIP
LOCKED returns and locks the rows nobody else holds. A plain query, without FOR UPDATE, never waits.

Press Edit, change HELD (the jobs session A holds), and run it again.
"""

JOBS = [1, 2, 3, 4, 5, 6]          # payment jobs with status 'new'
HELD = [1, 2]                      # session A: SELECT ... WHERE job_id IN (1, 2) FOR UPDATE, not committed

free = [job for job in JOBS if job not in HELD]
print(f"Session A holds jobs {', '.join(map(str, HELD)) or 'none'} with FOR UPDATE and has not committed.")
print("Session B runs SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id ...")
print()
rows = [
    ("without FOR UPDATE", f"returns {', '.join(map(str, JOBS))} at once, and locks nothing"),
    ("FOR UPDATE", "waits until session A commits or rolls back" if HELD else f"returns and locks {', '.join(map(str, JOBS))}"),
    ("FOR UPDATE NOWAIT", "fails at once with ORA-00054" if HELD else f"returns and locks {', '.join(map(str, JOBS))}"),
    ("FOR UPDATE WAIT 2", "waits 2 seconds, then fails: the rows are still locked" if HELD else f"returns and locks {', '.join(map(str, JOBS))}"),
    ("FOR UPDATE SKIP LOCKED", f"returns and locks {', '.join(map(str, free))}" if free else "returns no row at once"),
]
for clause, outcome in rows:
    print(f"  {clause:<24} {outcome}")
done = True

Session A holds jobs 1, 2 with FOR UPDATE and has not committed.
Session B runs SELECT job_id FROM pay_jobs WHERE status = 'new' ORDER BY job_id ...

  without FOR UPDATE       returns 1, 2, 3, 4, 5, 6 at once, and locks nothing
  FOR UPDATE               waits until session A commits or rolls back
  FOR UPDATE NOWAIT        fails at once with ORA-00054
  FOR UPDATE WAIT 2        waits 2 seconds, then fails: the rows are still locked
  FOR UPDATE SKIP LOCKED   returns and locks 3, 4, 5, 6

When every job is held, SKIP LOCKED returns no row at once, which a worker reads as “nothing to do now”. In 26ai the error pages also describe ORA-40097, which names the transaction holding a row lock by its undo segment, slot and wrap numbers.

Four larger topics follow from these row locks. A SKIP LOCKEDSKIP LOCKED: A clause of SELECT ... FOR UPDATE that leaves out the rows another transaction has locked and locks and returns the others, so several consumers of a queue can work without waiting for each other. queue lets several workers share one table of jobs without taking any job twice. A lost updatelost update: A change that disappears because another session, starting from an older read of the same row, writes over it. Both statements succeed, and no error is raised. is a change that another session overwrites from an older read, and an optimistic checkoptimistic locking: A way to prevent lost updates that holds no lock between reading a row and writing it: the write itself checks that the row has not changed since it was read. in the UPDATE stops it. Scheduler jobsscheduler job: A job of Oracle Scheduler, created with DBMS_SCHEDULER.CREATE_JOB, that runs PL/SQL or a program in the background, once or on a calendar, and logs each run. run PL/SQL in the background on a calendar, and scheduler jobs log each run in USER_SCHEDULER_JOB_RUN_DETAILS. A lock-free reservation lets many sessions change one busy number, such as the stock of a best seller, without waiting for each other’s row locks.

A job queue that several workers share with SKIP LOCKED Pro: opens with a Pro, Premium or Ultimate pass

Lost updates reproduced, and the checks that prevent them Pro: opens with a Pro, Premium or Ultimate pass

Scheduler jobs that keep the queue running, and lock-free reservations Pro: opens with a Pro, Premium or Ultimate pass

Practise: reads, waits and skips

Each question describes two sessions and one row. Say who waits, who fails and what each one reads:

Interview questions

Warm-up (fresher to 2–5 years): what is the practical difference between NOWAIT and WAIT n on a locked row?

Both stop a SELECT … FOR UPDATE from waiting without limit for another transaction’s row lock. NOWAIT fails at once with ORA-00054, which suits a screen that should say “someone else is editing this” straight away. WAIT n waits up to n seconds, then fails if the row is still locked, which suits a short job that can afford a pause. Without either clause the session waits until the holder commits or rolls back.

Three longer questions follow: a payment sent twice from a queue, seats sold beyond capacity, and a nightly job that stopped without anyone noticing:

Key takeaways

• In Oracle a reader never waits for a writer: a query reads the last committed version of each row, from undo data.
• A row is locked only by a writer or by SELECT … FOR UPDATE, and the lock lasts until the transaction ends.
• With no clause FOR UPDATE waits, NOWAIT fails at once, WAIT n waits n seconds, and SKIP LOCKED leaves locked rows out.
• SKIP LOCKED queues and optimistic checks put row locks to work for shared queues and concurrent edits, and scheduler jobs run such work in the background.