Force Oracle to Return Top N Rows with Skip Locked

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 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;

Does Oracle SKIP LOCKED prevent non-repeatable reads?

If skip locked successfully returns the ID = 1 row subsequent queries will always return values equal to the result of the first query.
In the case of select for update skip locked Oracle doesn't compute result set first but instead checks blocks and rows while fetching. I'll try to explain it by writing pseudocode

start select for update skip locked
open cursor, skip_locked_SCN := next SCN;
start fetching
for block in table_data_blocks loop
  if block.SCN < skip_locked_SCN then  -- unchanged block
    for row in block.rows(where id = :id) loop  -- filter rows
      if row is locked then
        -- skip that row
      else 
        add_to_resultset(row);
      end if;
    end loop;
  else  -- block has been changed
    -- go to undo segment and get previous version
    undo_block := get_from_UNDO(block);  -- (ORA-01555: snapshot too old may be raised)
    for undo_row in undo_block.rows(where id = :id) loop
       actual_row = block.rows(where rowid = undo_row.rowid);  -- get actual version of appropriate row by rowid
       if actual_row is locked then
         -- skip that row
       else
         -- check if data in the row remains unchanged
         if actual_row.data = undo_row.data then
           add_to_resultset(actual_row);
         else
           -- data changed, skip that row
         end if; 
       end if; 
    end loop; 
  end if; 
end loop;

Select for update skip locked with row limit

If you want each thread to only select about one fifth of the records, you might do something like:

select *
from   queue_requests
where  request_status = 0 and
       date_requested <= sysdate and
       mod(DBMS_RowID.RowID_Row_Number(queue_requests.rowid),5) = thread_number
       rownum <= ?
for update
skip locked;

... where thread_number is an integer between 0 and 4.

The success of this depends on the likely number of rows per block for the queue_requests table. If the rows are small then you'll be OK, but if they're large and you have many rows then you might do better with:

mod(DBMS_RowID.RowID_Block_Number(queue_requests.rowid),5)

Oracle delete skip locked

Following up on Jeffrey's answer, I believe this clause was intended for use in PL/SQL blocks. So you could try this, for example:

begin
  for r in (select rowid as rid from t for update skip locked)
  loop
    delete from t where rowid = r.rid;
  end loop;
end;
/ 

---

Related Topics