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. This looks quite similar to exclusive mode of locking.

oracle-skip-locked

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 deptno as 10 using FOR UPDATE NOWAIT.

SELECT * FROM dept  WHERE deptno = 10 FOR UPDATE NOWAIT;
Code language: SQL (Structured Query Language) (sql)

Output:

DEPTNO     DNAME          LOC ---------- -------------- ------------- 10         ACCOUNTING     NEW YORK
Code language: Markdown (markdown)

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

SELECT * FROM dept  WHERE deptno IN (10,20) FOR UPDATE NOWAIT;
Code language: SQL (Structured Query Language) (sql)

Output:

SELECT * FROM dept WHERE deptno IN (10,20) FOR UPDATE NOWAIT ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified
Code language: SQL (Structured Query Language) (sql)

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:

  1. returned the control without throwing an exception
  2. acquired lock on the row (i.e. deptno 20) which is available for locking
  3. skipped the row (i.e. deptno 10) that has been locked already by session 1
SELECT * FROM dept  WHERE deptno IN (10,20) FOR UPDATE SKIP LOCKED;
Code language: SQL (Structured Query Language) (sql)

Output:

DEPTNO     DNAME          LOC ---------- -------------- ------------- 20         RESEARCH       DALLAS
Code language: Markdown (markdown)
Get our Articles via Email. Enter your email address.

You may also like...

2 Comments

  1. jong jin says:

    thank you for your post,
    I will quote. thank you

  2. rahul says:

    I have a query here. Say i am doing a query to fetch one record by limit.
    select * from emplyee where status = ‘new’ order by empy_id asc limit 1 for update skip locked;

    this query will only fetch one emplyee and allow application to process it. now in the clustered environment, second instance of application will run the same query, as per the query both table will try to read the same row.
    it there a way i can fetch the second newest emplyee as the first one is already locked by one transaction.

Leave a Reply

Your email address will not be published. Required fields are marked *