CODEWITHSUNDEEP
sqloracle-databasetransactionsisolation-level
Nicolas Labrot
Nicolas Labrot

Reputation: 4106

Does Oracle SKIP LOCKED prevent non-repeatable reads?

A transaction executes these 2 statements with the READ COMMITTED isolation

SELECT * FROM CATS WHERE ID=1 FOR UPDATE SKIP LOCKED
SELECT * FROM CATS WHERE ID=1

The first query returns the ID 1 row. I wonder if the second query will always return values equals to the result of the first query?

My concerns are the following

I have read the following question and the associated articles: Force Oracle to return TOP N rows with SKIP LOCKED.

If I understand correctly, Oracle first computes the result set, it opens the cursor and then for each row, skip the row if the row is already locked. Without SKIP LOCKED the result set is locked when the cursor is opened.

Is this correct?

If yes, given a READ COMMITTED isolation:

I wonder given the below scenario if T2 can update a row before the row is locked by T1:

  1. T1: Oracle computes the result set
  2. T2: Oracle updates the same result set and COMMIT
  3. T1: Oracle opens the cursor
  4. T1: Oracle for each row, skips the row if the row is already locked

Is it possible?

Upvotes: 2

Views: 480

Answers (1)

Konstantin Sorokin
Konstantin Sorokin

Reputation: 405

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;

Upvotes: 1

Related Questions