[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 f...