[Simple Note] Select … For Update Options in Oracle (WAIT, NOWAIT, SKIP LOCKED)

In Oracle, the SELECT ... FOR UPDATE statement allows you to acquire an exclusive lock on selected rows. 

1. FOR UPDATE with no option : In this case, Oracle waits indefinitely until it acquires the lock. If another session already holds the lock, it will wait until the lock is released. 

2. FOR UPDATE WAIT integer : With this option, Oracle retries acquiring the lock for the specified duration (in seconds). If the lock cannot be obtained during this time, the query fails with an ORA-30006 error. 

SELECT ename FROM scott.emp WHERE empno = 7900 FOR UPDATE WAIT 5;

 

3. FOR UPDATE NOWAIT (= WAIT 0) : If the lock cannot be acquired immediately, Oracle raises an ORA-00054 error. This means that if another session has already locked the rows, the query fails immediately. 

SELECT ename FROM scott.emp WHERE empno = 7900 FOR UPDATE NOWAIT;

 

4. FOR UPDATE OF ... column : This syntax locks the select rows only for a particular table or view in a join. The columns in the OF clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. 

SELECT empno, sal, comm
FROM emp, dept
WHERE emp.deptno = dept.deptno AND job = 'CLERK' AND loc = 'NEW YORK'
FOR UPDATE OF emp.sal;

5. FOR UPDATE SKIP LOCKED : This allows you to skip locked rows and proceed with available ones. It’s useful for handling concurrency without waiting for locks. 

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html