Select unlocked rows oracle

Oracle does have this feature, specifically the SKIP LOCKED portion of the SELECT statement. To quote:

SKIP LOCKED is an alternative way to handle a contending transaction that is locking some rows of interest. Specify SKIP LOCKED to instruct the database to attempt to lock the rows specified by the WHERE clause and to skip any rows that are found to be already locked by another transaction.

The documentation goes on to say it's designed for use in multi-consumer queues but this does not mean that you have to use it in this environment. Though the documentation says this there is a large caveat. You can't ask for the next N unlocked rows - only the next N rows, of which the unlocked ones will be returned.

SELECT * 
  FROM TableXY 
 WHERE ROWNUM = 1
   FOR UPDATE SKIP LOCKED

Note that if the table you're selecting from is locked in exclusive mode, i.e. you've already instructed the database not to let any other session lock the table you will not get any rows returned until the exclusive lock is released.


I faced with the same problem recently, and after solved it, I wrote this blog entry:

http://nhisawesome.blogspot.com/2013/01/how-to-lock-first-unlocked-row-in-table.html

Feel free to leave a comment! Your comments are appreciated.

Short summary: instead of select first unlocked one and lock it, I select a bunch of records, then loop through the bunch and try to acquire a lock on it using SKIP LOCKED hint. If the selected one is not lockable, move on to the next one, until a lock acquired or none remain.


select for update nowait will error out if you select a row that is locked. Is that what you want? I am curious as to what problem you are trying to solve. Unless you have long-running transactions, the lock on a row would be transient from one moment to the next.

Example:

CREATE TABLE TEST ( COL1 NUMBER(10) NOT NULL, COL2 VARCHAR2(20 BYTE) NOT NULL );

CREATE UNIQUE INDEX TEST_PK ON TEST (COL1);

ALTER TABLE TEST ADD ( CONSTRAINT TEST_PK PRIMARY KEY (COL1) USING INDEX TEST_PK );

SQL Session #1: SQL> insert into test values(1,'1111'); 1 row created. SQL> insert into test values(2,'2222'); 1 row created. SQL> commit; Commit complete. SQL> update test set col2='AAAA' where col1=1; 1 row updated.

SQL Session #2: Attempt to read locked row, get error:

SQL> select * from test where col1=1 for update nowait; select * from test where col1=1 for update nowait * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Comments

  1. Nguyen

    • 2020/6/12

    SKIP LOCKED is an alternative way to handle a contending transaction that is locking some rows of interest. Specify SKIP LOCKED to instruct the 

  2. Wes

    • 2015/12/26

    Oracle does have this feature, specifically the SKIP LOCKED portion of the SELECT statement.To quote: SKIP LOCKED is an alternative way to handle a contending transaction that is locking some rows of interest.

  3. Diaz

    • 2020/5/23

    SELECT FOR UPDATE SKIP LOCKED Hi TeamHave a scenario to select a particular set of rows from a table for further processing.

  4. Colson

    • 2016/5/2

    Suppose I have Oracle table books store n books info with columns id and title. Some of the tuples are locked by SELECT FOR UPDATE clause. Suppose these rows

  5. Holden

    • 2020/8/1

    Suppose I have Oracle table books store n books info with columns id and title Some of the tuples are locked by SELECT FOR UPDATE claus

  6. Abdiel

    • 2018/3/16

    1. There are, say, 3 rows in my table 2. I do SELECT * FROM table FOR UPDATE which locks the 3 rows 3. Another process inserts 2 new rows 4. I would like to do a SELECT on all unlocked rows (in this case find the 2 unlocked rows) FOR UPDATE. Step 2, 3 and 4 goes in separate transactions. Step 2 may take long time. Is step 4 possible??

  7. Kaden

    • 2020/8/4

    In Oracle, FOR UPDATE SKIP LOCKED clause is usually used to select and process tasks from a queue by multiple concurrent sessions. It allows a session to 

  8. Quincy

    • 2017/12/16

    Suppose I have Oracle table books store n books info with columns id and title.Some of the tuples are locked by SELECT FOR UPDATE clause.. Suppose these rows whose

  9. Robert

    • 2020/4/2

    SELECT… · You can instruct Oracle to not wait for the locks to be released, by adding a NOWAIT to SELECT… · To release the locked rows, a COMMIT 

  10. Kevin

    • 2017/12/5

    So here is a function which will exclude the rowids of those rows which are locked when used within a select query. The basic idea is to first locate all the rows that are locked and find their ROWIDs. To locate all the locked rows a cursor is built which will try to lock each row in the table. (Select for update nowait).

  11. Tyler

    • 2020/8/6

    To locate all the locked rows a cursor is built which will try to lock each row in the table. (Select for update nowait).

  12. Kayden

    • 2021/6/20

    Oracle SQL: select first n rows / rows between n and m (top n/limit queries) At times, it's necessary to select the first n rows or the rows between n and m (paging) from a table or query. Here are a few wrong and correct ways to do it.

  13. Elias

    • 2015/3/16

    SELECT TOP 1 * FROM TableXY WITH(UPDLOCK, READPAST); I'm a little bit disappointed with Oracle lacking such a feature. They wanna make me use AQ 

  14. Malakhi

    • 2020/7/22

    select event,p1,p2,p3 from v$session_wait where wait_time=0 and event='enqueue'; · select * from v$lock where type='TX' and request>0;. Show sessions holding a 

  15. Kasen

    • 2016/9/13

    FOR UPDATE or SELECT FOR SHARE transaction that requests the same locked row must wait until the blocking transaction releases the row lock. This behavior 

  16. Omari

    • 2016/7/4

    This means that locking a record in Oracle means updating the record's metadata and issuing a logical page write. For instance, you cannot do SELECT FOR UPDATE 

Comments are closed.

Recent Posts