CODEWITHSUNDEEP
sqloracle-databaselockingblocking
mateusz.fiolka
mateusz.fiolka

Reputation: 3100

Oracle select for update behaviour

The problem we try to solve looks like this.

Our solution is to assign a status to the card, and store it's reservation date. When reserving a card we do it using "select for update" statement. The query looks for available cards and for cards which were reserved long time ago.

However our query doesn't work as expected.

I have prepared a simplified situation to explain the problem. We have a card_numbers table, full of data - all of the rows have non-null id numbers. Now, let's try to lock some of them.

-- first, in session 1
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

We don't commit the transaction here, the row has to be locked. 

-- later, in session 2
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

The expected behaviour is that in both sessions we get a single, different row which satisfies query conditions.

However it doesn't work that way. Depending on whether we use the "skip locked" part of the query or not - the behavious changes:

So, after this long introduction comes the question.

Is the kind of desired locking behaviour possible in Oracle? If yes, then what are we doing wrong? What would be the correct solution?

Upvotes: 21

Views: 52462

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220877

While the other answers already sufficiently explained what's going on in your database with the various SELECT .. FOR UPDATE variants, I think it's worth mentioning that Oracle discourages using FOR UPDATE SKIP LOCKED directly and encourages using Oracle AQ instead:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346

We use Oracle AQ in our application and I can confirm that, after a somewhat steep learning curve, it can be a quite convenient way to handle producers/consumers directly in the database

Upvotes: 6

user734922
user734922

Reputation: 41

Not that Vincent's answer is wrong but I would have designed it differently.

My first instinct is to select for update the first available record and updated the record with a "reserved_date". After XXX time has passed and the transaction is not finalized, update the record's reserved_date back to null freeing up the record again.

I try to keep things as simple as possible. For me, this is simpler.

Upvotes: 4

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

The behaviour you've encountered for FOR UPDATE SKIP LOCKED has been described in this blog note. My understanding is that the FOR UPDATE clause is evaluated AFTER the WHERE clause. The SKIP LOCKED is like an additional filter that guarantees that among the rows that would have been returned, none are locked.

Your statement is logically equivalent to: find the first row from card_numbers and return it if it is not locked. Obviously this is not what you want.

Here is a little test case that reproduces the behaviour you describe:

SQL> CREATE TABLE t (ID PRIMARY KEY)
  2  AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;

Table created

SESSION1> select id from t where rownum <= 1 for update skip locked;

        ID
----------
         1

SESSION2> select id from t where rownum <= 1 for update skip locked;

        ID
----------

No row is returned from the second select. You can use a cursor to work around this issue:

SQL> CREATE FUNCTION get_and_lock RETURN NUMBER IS
  2     CURSOR c IS SELECT ID FROM t FOR UPDATE SKIP LOCKED;
  3     l_id NUMBER;
  4  BEGIN
  5     OPEN c;
  6     FETCH c INTO l_id;
  7     CLOSE c;
  8     RETURN l_id;
  9  END;
 10  /

Function created

SESSION1> variable x number;
SESSION1> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
1

SESSION2> variable x number;
SESSION2> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
2

Since I've explicitely fetched the cursor, only one row will be returned (and only one row will be locked).

Upvotes: 21

Related Questions