Oracle select for update behaviour

Question

The problem we try to solve looks like this.

• We have a table full of rows which represent cards. The purpose of reservation transaction is to assign a card to a client
• A card can not belong to many clients
• After some time (if it is not bought) a card has to be returned to the pool of available resurces
• Reservation can be done by many clients at the same time
• We use Oracle database for storing the data, so solution has to work at least on Oracle 11

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:

• without "skip locked" - second session is blocked - waiting for transaction commit or rollback in session one
• with "skip locked" - second query returns immediately empty result set

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?

Answer 1

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.

Answer 2

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).

Answer 3

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