sql - Oracle select for update behaviour -

the problem try solve looks this.

• we have table full of rows represent cards. purpose of reservation transaction assign card client
• a card can not belong many clients
• after time (if not bought) card has returned pool of available resurces
• reservation can done many clients @ same time
• we use oracle database storing data, solution has work @ least on oracle 11

our solution assign status card, , store it's reservation date. when reserving card using "select update" statement. query looks available cards , cards reserved long time ago.

however our query doesn't work expected.

i have prepared simplified situation explain problem. have card_numbers table, full of data - of rows have non-null id numbers. now, let's try lock of them.

-- first, in session 1 set autocommit off;  select id card_numbers   id not null   , rownum <= 1   update skip locked; 

we don't commit transaction here, row has locked.

-- later, in session 2 set autocommit off;  select id card_numbers   id not null   , rownum <= 1   update skip locked; 

the expected behaviour in both sessions single, different row satisfies query conditions.

however doesn't work way. depending on whether use "skip locked" part of query or not - behavious changes:

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

so, after long introduction comes question.

is kind of desired locking behaviour possible in oracle? if yes, doing wrong? correct solution?

the behaviour you've encountered for update skip locked has been described in this blog note. understanding update clause evaluated after clause. skip locked additional filter guarantees among rows have been returned, none locked.

your statement logically equivalent to: find first row card_numbers , return if not locked. not want.

here little test case reproduces behaviour describe:

sql> create table t (id primary key)   2  select rownum dual connect level <= 1000;  table created  session1> select id t rownum <= 1 update skip locked;          id ----------          1  session2> select id t rownum <= 1 update skip locked;          id ---------- 

no row returned second select. can use cursor work around issue:

sql> create function get_and_lock return number   2     cursor c select id t update skip locked;   3     l_id number;   4  begin   5     open c;   6     fetch c 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 completed x --------- 1  session2> variable x number; session2> exec :x := get_and_lock;  pl/sql procedure completed x --------- 2 

since i've explicitely fetched cursor, 1 row returned (and 1 row locked).