Reading a row and then updating it leaves a gap in which another transaction can change the row first. SELECT ... FOR UPDATE closes the gap: it locks the rows it returns, as if they had been updated, until the transaction ends. NOWAIT and WAIT control what happens when a row is already locked, and SKIP LOCKED lets several workers share a queue of rows without blocking each other.
Code for This Guide
The main examples are in the examples/transactions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
select ... for update [of column [, ...]] [nowait | wait n | skip locked]
| Option | If a row is locked by another transaction |
|---|---|
| (none) | Wait until it is released |
| NOWAIT | Fail at once with ORA-00054 |
| WAIT n | Wait at most n seconds, then fail |
| SKIP LOCKED | Skip the locked rows and return the others |
NOWAIT
The main transaction locks an aircraft row. A second transaction, autonomous so it can run in the same script, asks for the same row with NOWAIT.
Example:
-- this transaction locks a row ... select tail_number, status from aircraft where tail_number = 'A6-NAC' for update; -- ... and another transaction (an autonomous one) tries to lock it too declare pragma autonomous_transaction; v_status aircraft.status%type; begin select status into v_status from aircraft where tail_number = 'A6-NAC' for update nowait; end; / rollback;
Output:
TAIL_NUMBER STATUS
______________ _________
A6-NAC ACTIVE
declare
*
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 196633 ("usn<<16 | slot") and 1345 ("sequence")
ORA-40097: Failure to acquire row lock, currently held by transaction ID 3.25.1345
ORA-06512: at line 5
Rollback complete.It fails at once with ORA-00054. Oracle AI Database 26ai explains the error in detail: the kind of lock and the transaction that holds it (the long line is cut here). The example rolls back afterward.
SKIP LOCKED for Queues
Several workers can process the same table of tasks: each locks the rows it takes, and SKIP LOCKED makes the others pass over them. Here the main transaction holds two flights, and a second worker takes the next three free ones.
Example:
-- this transaction takes the first two scheduled flights of route 5 ...
select flight_id from flights
where flight_id in (2328, 2360)
for update;
-- ... and a second worker takes the next free ones, skipping the locked rows
declare
pragma autonomous_transaction;
cursor c_free is
select flight_id from flights
where route_id = 5 and status = 'SCHEDULED'
order by flight_id
for update skip locked;
v_flight_id flights.flight_id%type;
begin
open c_free;
for i in 1 .. 3 loop
fetch c_free into v_flight_id;
dbms_output.put_line('worker 2 took flight ' || v_flight_id);
end loop;
close c_free;
rollback;
end;
/
rollback;Output:
FLIGHT_ID
____________
2328
2360
worker 2 took flight 2392
worker 2 took flight 2424
worker 2 took flight 2456
PL/SQL procedure successfully completed.
Rollback complete.The second worker gets flights 2392, 2424, and 2456, skipping the two that are locked, without waiting. FOR UPDATE cannot be combined with FETCH FIRST, so the worker opens a cursor and fetches the rows it needs.
Things to Know
- FOR UPDATE OF column locks only the rows of the tables that column belongs to, in a join.
- Locks are released by COMMIT or ROLLBACK, never by the end of the query, so keep such transactions short.
- Oracle never escalates row locks to table locks, so locking many rows does not block the rest of the table.
Related Guides
Conclusion
SELECT ... FOR UPDATE locks the rows it reads until the transaction ends, so they cannot change before you update them. Use NOWAIT or WAIT n to avoid waiting indefinitely, and SKIP LOCKED to let several workers take rows from a shared queue.
