2

I have a plsql procedure used to insert/update records to a table and this procedure can be called from more than 1 place at a given moment from parallel execution threads.

This method checks whether a record exists in the table using the keys and updates it or inserts a new one.

We have noticed that this procedure sometimes gives us issues and after some investigation, we noticed that the procedure gets called from 2 places at the same time (same millisecond) trying to insert the same record. So the Check_Exist will return FALSE to both executions and then when both executions try to insert the same record where one fails with an Error and one completes successfully.

PROCEDURE Insert_Aaa(
    rec_ IN Aaa_TAB%ROWTYPE)
IS
  --some code
BEGIN
  --some code
  IF NOT Check_Exist___(rec_.a,
                        rec_.b,
                        rec_.c) THEN
     Insert___(rec_);
  ELSE
     Update___(rec_);
  END IF;
END Insert_Aaa;

I thought of using dbms_session.sleep() with a random value in seconds, but it will add around 5-10 mins additional time to this execution as this procedure will be hit from around 120 places. So that is not a good solution for me.

Subash Basnayake
  • 133
  • 2
  • 10
  • 1
    Insert of the same record in the same millisecond? So it doesn't matter, which insert succeeds and which fails. After all the record gets inserted as desired. Just catch the exception and swallow it. (`EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;`) – Thorsten Kettner Aug 02 '23 at 10:49
  • the second answer(ordered by votes) is the same as your comment – nbk Aug 02 '23 at 10:59
  • Just handle duplicate key error , because sleep is not a locking mechanism – astentx Aug 02 '23 at 11:12
  • 1
    Don't check then insert/update. Just use a single `MERGE` statement (as per the top answer of the linked duplicate). – MT0 Aug 02 '23 at 12:02

0 Answers0