Forum Stats

  • 3,757,480 Users
  • 2,251,236 Discussions
  • 7,869,840 Comments

Discussions

select for update and insert if not exist atomically

Hello,

I have concurrent connections to an oracle db.

On each connection, concurrently, the connected process need to :

1- select for update a register based on a key ( is the primary table key). If there are not rows with this key, insert a register with default values on this key.

2- make some operations

3 - after that, update the row and commit.

We implement this doing a 'select for update...' and if not rows were returned, perform the 'insert...'

We have the problem that if two applications concurrently made de 'select for update...' on an non existent key, both will try to perform the insert. One will continue and the other will fail on the insert when the first one commits, and this has to be catched and reprocessed.

We are looking if there is a way to do this by the db in one sentence, that look for the key, return the data if found, and if not, insert it with the given values and return it. All in a single sentence executed atomically.

We'd like to on a single sentence that perform 'select for update, and if not exist insert with default values and return it', and that if a second connection execute on the same key, just wait for the lock to be freed and do not generate an error ( this second one should return the inserted and updated row by the first execution)

One possibility we found is before starting the transaction, perform an insert.. ..where not exist../commit, but this implies to always try to insert when the need of insertion is unlikely.

Thanks

Tagged:

Best Answer

  • Ahmed AbdelFattah
    Ahmed AbdelFattah Member Posts: 8 Red Ribbon
    Accepted Answer

    Hi,

    As indicated above, the "Merge" statement should cover the first part of your requirements (for session # 1).

    For the second part related to the second session, the code should capture the ORA-00001 error and handle it, so as it returns the newly inserted data by the first session.

    In my opinion, your requirements may be implemented with a small PL/SQL program, not with one single SQL statement.

    Thanks

    Ahmed

«1

Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @User_26SND ,

    this can be done with the MERGE statement which combines INSERT, UPDATE and DELETE functions.

    Best regards

    Jan

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,724 Black Diamond

    We have the problem that if two applications concurrently made de 'select for update...' on an non existent key, both will try to perform the insert. One will continue and the other will fail on the insert when the first one commits, and this has to be catched and reprocessed.

    You need to clarify the above.

    SQL> create table test_tbl(id number primary key,val varchar2(10));
    
    Table created.
    
    SQL> 
    

    Session 1:

    SQL> select * from test_tbl where id = 1 for update;
    
    no rows selected
    
    SQL>
    

    Session 2:

    SQL> select * from test_tbl where id = 2 for update;
    
    no rows selected
    
    SQL>
    

    Now Session 1:

    SQL> insert into test_tbl values(1,'X'));
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    

    And Session 2:

    SQL> insert into test_tbl values(2,'Y'));
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    

    Most likely you are talking two sessions using same ID. If so, it makes no difference what you use SELECT FOR UPDATE or MERGE:

    Session 1:

    SQL> merge into test_tbl t using (select 3 id,'Z' val from dual) s on (t.id = s.id) when matched then update set t.val = s.val when not matched then insert values(s.id,s.val);
    
    1 row merged.
    
    SQL>
    

    Session 2:

    SQL> merge into test_tbl t using (select 3 id,'Z' val from dual) s on (t.id = s.id) when matched then update set t.val = s.val when not matched then insert values(s.id,s.val);
    

    At this point Session 2 waits since it is blocked by Session 1. After Sesion 1 commits, Session 2 is released and obviously fails:

    merge into test_tbl t using (select 3 id,'Z' val from dual) s on (t.id = s.id) when matched then update set t.val = s.val when not matched then insert values(s.id,s.val)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.SYS_C0016860) violated
    
    SQL>
    

    SY.

  • Ahmed AbdelFattah
    Ahmed AbdelFattah Member Posts: 8 Red Ribbon
    Accepted Answer

    Hi,

    As indicated above, the "Merge" statement should cover the first part of your requirements (for session # 1).

    For the second part related to the second session, the code should capture the ORA-00001 error and handle it, so as it returns the newly inserted data by the first session.

    In my opinion, your requirements may be implemented with a small PL/SQL program, not with one single SQL statement.

    Thanks

    Ahmed

  • User_26SND
    User_26SND Member Posts: 4 Green Ribbon

    For what I understand, merge combines update and insert, where one can update a row and insert it if it not exist.

    In this case I'm looking for something similar that combines select and insert, where if no row was found on select, one new row is inserted.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,753 Gold Crown

    You need to give a more precise definition of requirements.

    Will a particular value of the "key" always correspond to the same value for the "register".

    Are you doing the "select for update" because you simply want to lock the row, or do you also need to read the current value of the "register".

    If the row doesn't exist does this mean the session knows what value for the "register" it should be inserting - if the row doesn't exists will two concurrent sessions think they should be inserting the same value for the "register" or would they be expecting to insert different values.

    When you update the row at step 3 is that changing the value of the "register" or is it changing the value some other attribute that you haven't mentioned.

    You say at the start of the thread: "On each connection ..." does this mean that the code needs to run as you create the connection - in a logon trigger, for example - or does this mean that this is something the first piece of client code should do after the session has connected.


    Regards

    Jonathan Lewis

    Ahmed AbdelFattah
  • User_26SND
    User_26SND Member Posts: 4 Green Ribbon

    Hi @JohathanLewis , thanks for your response. Following is the response for your questions:

    The table has two columns: "key" that is the primary key, and "value" that contains data associated with the key.

    >Will a particular value of the "key" always correspond to the same value for the "register".

    "key" column is primary key, so is unique.

    >Are you doing the "select for update" because you simply want to lock the row, or do you also need to read the >current value of the "register".

    I'd like to read an lock the value. As indicated, first step is to select the registry for update (so locking it), after that the process will made some operations and at the end will update the registry with new data on "value" column and commit.

    >If the row doesn't exist does this mean the session knows what value for the "register" it should be inserting - if the >row doesn't exists will two concurrent sessions think they should be inserting the same value for the "register" or >would they be expecting to insert different values.

    If the row does not exist on "select for update", we need to insert a row for the "key" with a default value in "value" column. The default value is always the same (for example a empty string). We need the row to be inserted and locked.

    >When you update the row at step 3 is that changing the value of the "register" or is it changing the value some >other attribute that you haven't mentioned.

    Just change the data at "value" column.

    >You say at the start of the thread: "On each connection ..." does this mean that the code needs to run as you create >the connection - in a logon trigger, for example - or does this mean that this is something the first piece of client >code should do after the session has connected.

    The process will in loop receive some info from a socket. From this info get a key, then go to the DB selecting for update with this key on "key" column and get the associated "value". Then it make other stuff, and finally update de "value" column with new data and commit.

    More than one process will be run concurrently.

    What is presented is how to resolve the issue when a key is used for the first time and is not found in the db and it is need to be inserted and locked, and how to solve concurrency on this case.

    Thanks!!!

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,753 Gold Crown

    >Are you doing the "select for update" because you simply want to lock the row, or do you also need to read the >current value of the "register".


    I'd like to read an lock the value.

    In Oracle you do not lock "values" you lock rows. So let me clarify my question - are you reading the value because you want to make some use of the actual value, or are you simply trying to lock the row so that no-one else can lock it and change the value.

    The process will in loop receive some info from a socket.

    This gives you far more scope than your intial "on connection" statement. Whatever your front end it could call a little pl/sql procedure (with the type of merge command shown by @Solomon Yakobson ) to do the select/insert. Merge doesn't allow the RETURNING clause, but the package could select the value (if you need it) after you've locked the row; PL/SQL can also trap the exception (duplicate key which, as SY points out, will happen if two sessions find that the row doesn't exist intially) and go round a loop to try again.

    Regards

    Jonathan Lewis

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,724 Black Diamond

    You could take advantage of ignore_row_on_dupkey_index. Something like:

    create or replace
     procedure insert_or_update(
                   p_key number,
                   p_value varchar2
                  )
      is
      begin
        insert /*+ ignore_row_on_dupkey_index(your_table_name,your_table_pk_index_name) */
         into your_table_name
         values(
             p_key,
             p_value
            );
        if sql%rowcount = 0
         then
          update your_table_name
            set value = p_value
           where key = p_key;
    end;
    /
    

    Or handle exception:

    create or replace
      procedure insert_or_update(
                                 p_key number,
                                 p_value varchar2
                                )
        is
        begin
            insert
              into  your_table_name
              values(
                     p_key,
                     p_value
                    );
          exception
            when dup_val_on_index
              then
                update your_table_name
                   set value = p_value
                 where key = p_key;
    end;
    /
    

    Now you can call SP and it will either insert or update. But no matter what there will be serializing when multiple sessions will call SP passing same key and sessions will wait till preceding sessions commit. And make sure INITRANS is set to sufficient value.

    SY.

  • User_26SND
    User_26SND Member Posts: 4 Green Ribbon

    Hi there, thanks for your comments. Please excuse my difficult to explain the situation, English is not my natural language.

    @JonathanLewis:

    >In Oracle you do not lock "values" you lock rows. So let me clarify my question - are you reading the value because 

    >you want to make some use of the actual value, or are you simply trying to lock the row so that no-one else can 

    >lock it and change the value.

    I'm reading the value (at step 1) because want to make some use of the actual value, an update at the end (step 3). I also want no-one change it's value during this processing ( that's why the "select...for update", to lock the row ). If more than one want to access the same key, I want to wait for each other to finish. Than is solved with the "select for update". What I need to solve is the case when "key" does not exists and have to be inserted. If I have two concurrent process that make the "select for update" with an inexistent key and need to insert. 

    I'd like to know if there is a way to do de "select...for update or insert". Some similar of what merge does for "update or insert"

    >This gives you far more scope than your intial "on connection" statement. Whatever your front end it could call a l?

    >little pl/sql procedure (with the type of merge command shown by @Solomon Yakobson ) to do the select/insert. 

    >Merge doesn't allow the RETURNING clause, but the package could select the value (if you need it) after you've 

    >locked the row; PL/SQL can also trap the exception (duplicate key which, as SY points out, will happen if two 

    >sessions find that the row doesn't exist initially) and go round a loop to try again.

    For what I understand, what you said I can do is what I want to know HOW to do.

    @SolomonYakobson:

    For what I understand, your solution is for "insert or update", but what I'm looking for is a way to o a "Select or update with insertion if key does not exist".


    For your answers, I understand there is no way to do this in one sentence (like merge does for insert/update).


    Thanks