oracle concurrent select for update and insert

Question

I have a table with two columns: k (primary key) and value. I'd like to:

1. select for update by k, if k is not found, insert a new row with a default value.
2. with the returned value ( existent or new inserted row value) make some processing.
3. update the row and commit.

Is it possible to make this "select for update and insert default value if not found"?

If implement (1) as a select/check if found/insert if not found, we have concurrency problems, since two sessions could make the select concurrently on non existent key, both will try to insert and one of the instances will fail.

In this case the desired behavior is to perform atomically the select/insert and one of the instance perform it and the second one keep locked until the first one commits, and then use the value inserted by the first one.

We implement it always doing an "insert ... if not exist.../commit" before the "select for update" but this implies always trying to insert when it is a unlikely needed.

Is there any way to implement it on one sql sentence?

Thanks!!

Answer 1

select ... for update is the first step you should make; without it, you can't "reserve" that row for further processing (unless you're willing to lock the whole table in exclusive mode; if that "processing" takes no time, that could also be an option, especially if there are not many users who will be doing it).

If row exists, the rest is simple - process it, update it, commit.

But, if it doesn't exist, you'll have to insert a new row (just as you said), and here's a problem of two (or more) users inserting the same value.

To avoid it, create a function which

• will return unique ID value for a new row
• is an autonomous transaction
  • why? Because you're performing DML in it (update or insert), and you can't do that in a function unless it is an autonomous transaction

Users will have to use that function to get the next ID value. Here's an example: you'll need a table (my_id) which holds the last used ID (and every user who accesses it via the function will create a new value).

Table:

SQL> create table my_id (id number);

Table created.

Function:

SQL> create or replace function f_id
  2    return number
  3  is
  4    pragma autonomous_transaction;
  5    l_nextval number;
  6  begin
  7    select id + 1
  8      into l_nextval
  9      from my_id
 10      for update of id;
 11
 12      update my_id set
 13        id = l_nextval;
 14
 15    commit;
 16    return (l_nextval);
 17
 18  exception
 19    when no_data_found then
 20      lock table my_id in exclusive mode;
 21
 22      insert into my_id (id)
 23      values (1);
 24
 25      commit;
 26      return(1);
 27  end;
 28  /

Function created.

Use it as

SQL> select f_id from dual;

      F_ID
----------
         1

SQL>

That's it ... code you'll use will then be something like this:

SQL> create table test
  2    (id      number constraint pk_test primary key,
  3     name    varchar2(10),
  4     datum   date
  5    );

Table created.

SQL> create or replace procedure p_test (par_id in number)
  2  is
  3    l_id test.id%type;
  4  begin
  5    select id
  6      into l_id
  7      from test
  8      where id = par_id
  9      for update;
 10
 11    update test set datum = sysdate where id = par_id;
 12  exception
 13    when no_data_found then
 14      insert into test (id, name, datum)
 15        values (f_id, 'Little', sysdate);         --> function call is here
 16  end;
 17  /

Procedure created.

SQL> exec p_test (1);

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME       DATUM
---------- ---------- -------------------
         1 Little     04.09.2021 20:49:21

SQL> exec p_test (1);

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME       DATUM
---------- ---------- -------------------
         1 Little     04.09.2021 20:49:21   --> row was inserted

SQL> exec p_test (1);

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME       DATUM
---------- ---------- -------------------
         1 Little     04.09.2021 20:49:30   --> row was updated

SQL>