oracle concurrent select for update and insert

See if k is available

SELECT * FROM table WHERE k = value FOR UPDATE

If no rows returned, then it doesn't exist. Insert it:

INSERT INTO table(k, col1, col2) VALUES (value, val1, default))

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>

Use a sequence to generate a surrogate primary key instead of using a natural key. If you had a real natural key, then it would be extremely unlikely for two users to submit the same value at the same time.

There are several ways to automatically generate primary keys. I prefer to use sequence defaults, like this:

create sequence test_seq;

create table test1
(
    k number default test_seq.nextval,
    value varchar2(4000),
    constraint test1_pk primary key(k)
);

If you can't switch to a surrogate key or a real natural key:

Change the "insert ... if not exist.../commit" to a simpler "insert ... if not exist", and perform all operations in a single transaction. Inserting the same primary key in different sessions, even uncommitted, is impossible in Oracle. Although the SELECT won't cause a block, the INSERT will. This behavior is an exception to Oracle's implementation of isolation, the "I" in "ACID", and in this case that behavior can work to your advantage.

If two sessions attempt to insert the same primary key at the same time, the second session will hang, and when the first session finally commits, the second session will fail with the exception "ORA-00001: unique constraint (X.Y) violated". Let that exception become your flag for knowing when a user has submitted a duplicate value. You can catch the exception in the application and have the user try again.