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?

Solution 1

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

Solution 2

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

Solution 3

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.

Comments

[Codo]

The blog you mention says something different: Some rows are not returned not because they are locked but because the ITL slots on the block where the row resides are exhausted. Increasing the number of ITL slots fixed the problem in their case. But I don't want to rule out that your proposed code could help as well. My experience is that "ROWNUM <= xxx" is the tricky part.

[Vincent Malgrat]

@codo: I agree the case is different, however the main behaviour is the same: Oracle returns less rows than asked even if some rows are available. My understanding is that this is because the SKIP clause is evaluated after the WHERE clause. Adding ITL slots doesn't help in the case I tested.

[mateusz.fiolka]

Thank you, Vincent. This method seems to work in the case that I have described. However, it turned out that we need to lock multiple rows in a single select for update. When using your method and loading few rows in two separate sessions id numbers start to overlap.

[mateusz.fiolka]

Vincent's answer is a good hint for solving this specific problem, so I'll choose it as correct. Hovever my vote goes also to you, as we also decided to do it this way. Additionally we have move our transaction login from Java to a PL/SQL procedure, to make it as close do db as possible. This way transactions should be very short.

[Vincent Malgrat]

@mateusz: There should be no overlap: you can't lock a row twice at the same time. Maybe you have found a bug, or more likely the row has been locked and then released (end of transaction) before being locked again. Perhaps you can build a test case that reproduces your finding?

[Lukas Eder]

+1 for the "unintended-consequences" blog post. That really helped me!

[Andrei LED]

+1: Tom proposes to use cursor as well for this case: asktom.oracle.com/pls/asktom/… Though they say that QA may be better for this

[svlada]

What to do if we can't use Oracle AQ?

[Lukas Eder]

@svlada: Well, that question really cannot be answered in a simply way :-). Why don't you create a new Stack Overflow question with all the details?