title: Oracle select for update behaviour
body: <p>The problem we try to solve looks like this. </p>
  
  <ul>
  <li>We have a table full of rows which represent cards. The purpose of reservation transaction is to assign a card to a client</li>
  <li>A card can not belong to many clients</li>
  <li>After some time (if it is not bought) a card has to be returned to the pool of available resurces</li>
  <li>Reservation can be done by many clients at the same time</li>
  <li>We use Oracle database for storing the data, so solution has to work at least on Oracle 11
  </ul>
  
  <p>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.</p>
  
  <p>However our query doesn't work as expected.</p>
  
  <p>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.</p>
  
  <pre><code>-- first, in session 1
  set autocommit off;
  
  select id from card_numbers  
  where id is not null  
  and rownum &lt;= 1  
  for update skip locked;
  </code></pre>
  
  <p>We don't commit the transaction here, the row has to be locked.  </p>
  
  <pre><code>-- later, in session 2
  set autocommit off;
  
  select id from card_numbers  
  where id is not null  
  and rownum &lt;= 1  
  for update skip locked;
  </code></pre>
  
  <p>The expected behaviour is that in both sessions we get a single, different row which satisfies query conditions. </p>
  
  <p>However it doesn't work that way. Depending on whether we use the "skip locked" part of the query or not - the behavious changes:</p>
  
  <ul>
  <li>without "skip locked" - second session is blocked - waiting for transaction commit or rollback in session one</li>
  <li>with "skip locked" - second query returns immediately empty result set
  </ul>
  
  <p>So, after this long introduction comes the question.  </p>
  
  <p>Is the kind of desired locking behaviour possible in Oracle? If yes, then what are we doing wrong? What would be the correct solution?</p>