Oracle Equivalent of Rowlock, Updlock, Readpast Query Hints

Oracle equivalent of ROWLOCK, UPDLOCK, READPAST query hints

The equivalent of ROWLOCK is the FOR UPDATE clause

select *
from emp
for update;

Since 11g Oracle has documented the SKIP LOCKED syntax which is the equivalent of READPAST:

select *
from emp
for update skip locked;

This syntax has worked for ages (it is fundamental to Advanced Queuing) but if it's not in the docs it's not supported,

There is no equivalent of UPDLOCK lock because Oracle flat out doesn't allow dirty reads. Find out more.

Select top N with for update skip locked in Oracle

Will this work?

select messageid from messages 
    where messageid in (
       select messageid from(
         select 
          messageid, 
          RANK() over (order by messageid asc) as msg_rank 
          from messages
       ) where msg_rank=1
    )
  for update skip locked;

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.

Force Oracle to return TOP N rows with SKIP LOCKED

"From what I have seen, Oracle applies the WHERE predicate before determining what rows to skip."

Yup. It is the only possible way. You can't skip a row from a resultset until you have determined the resultset.

The answer is simply not to limit the number of rows returned by the SELECT statement. You can still use the FIRST_ROWS_n hints to direct the optimizer that you won't be grabbing the full data set.

The software calling the SELECT should only select the first n rows. In PL/SQL, it would be

DECLARE
  CURSOR c_1 IS  
    SELECT /*+FIRST_ROWS_1*/ qt.ID
    FROM QueueTest qt
    WHERE Locked IS NULL
    ORDER BY PRIORITY
    FOR UPDATE SKIP LOCKED;
BEGIN
  OPEN c_1;
  FETCH c_1 into ....
  IF c_1%FOUND THEN
     ...
  END IF;
  CLOSE c_1;
END;

SELECT FOR UPDATE with SQL Server

Recently I had a deadlock problem because Sql Server locks more then necessary (page). You can't really do anything against it. Now we are catching deadlock exceptions... and I wish I had Oracle instead.

Edit:
We are using snapshot isolation meanwhile, which solves many, but not all of the problems. Unfortunately, to be able to use snapshot isolation it must be allowed by the database server, which may cause unnecessary problems at customers site. Now we are not only catching deadlock exceptions (which still can occur, of course) but also snapshot concurrency problems to repeat transactions from background processes (which cannot be repeated by the user). But this still performs much better than before.

Oracle quartz selectWithLockSQL value?

so what are you trying to do there? the syntax in Oracle to lock a row is:

SELECT * FROM <table_name> WHERE <condition> FOR UPDATE;

In SQL Server, how can I lock a single row in a way similar to Oracle's SELECT FOR UPDATE WAIT?

In SQL Server there are locking hints but they do not span their statements like the Oracle example you provided. The way to do it in SQL Server is to set an isolation level on the transaction that contains the statements that you want to execute. See this MSDN page but the general structure would look something like:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    select * from ...

    update ...

COMMIT TRANSACTION;

SERIALIZABLE is the highest isolation level. See the link for other options. From MSDN:

SERIALIZABLE Specifies the following:

Statements cannot read data that has been modified but not yet
committed by other transactions.

No other transactions can modify data that has been read by the
current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would
fall in the range of keys read by any statements in the current
transaction until the current transaction completes.

---

Related Topics