Oracle Skip Locked in 11g

Oracle Skip Locked

Oracle 11g introduced SKIP LOCKED clause to query the records from the table which are not locked
in any other active session of the database.

The select for update statement has always been problematic for large updates because it the default
is to wait for locks and using select for update other tasks can abort waiting on access with the ORA-300036 error:

ORA-30006: resource busy; acquire with WAIT timeout expired

In other cases using select for update with the nowait clause you your own update may abort with the ORA-00054 error:

ORA-00054 resource busy and NOWAIT specified

Even worse, if a select for update task aborts, a zombie process may hold the row locks long term,
requiring DBA intervention.

To illustrate, we open two sessions. In the first session, we lock the row with Empno as 10 using FOR UPDATE NOWAIT.

SELECT * FROM Employee  WHERE Empno = 10  FOR UPDATE NOWAIT;

In the second session, we try to lock two rows (Empno10 and 20) from the table emp using FOR UPDATE NOWAIT. An exception is thrown after executing the following statement because one of the row (i.e. Empno10) out of the selected list is already locked by session 1.

SELECT * FROM Employee WHERE Empno IN (10,20) FOR UPDATE NOWAIT;

we get an error ORA-00054: resource busy and acquire with NOWAIT specified

Now we again try to lock two rows (deptno(s) 10 and 20) from the table dept but using the clause FOR UPDATE SKIP LOCKED instead of FOR UPDATE NOWAIT. As you can see the following statement has:

returned the control without throwing an exception  acquired lock on the row (i.e. deptno 20)
which is available for locking
skipped the row (i.e. deptno 10) that has been locked already by session 1
SELECT * FROM Employee WHERE Empno IN (10,20) FOR UPDATE SKIP LOCKED;