Hi TOMS,

I have gone through the deadlocks questions posted on your website.Thanks for the answers you have shared.

However I have a scenario where there are multiple instances of a process are running and causing a deadlock.
1. Design of process P

<1.1> It will be querying a table TABLE1 having below columns using "select for update"
NUM1 NUMBER
NUM2 NUMBER
DATE3 DATE(format DD/MM/YYYY)
NUM4 NUMBER
COL5 VARCHAR2(3)

Query : select for update as below
select NUM4
from TABLE1
where NUM1=99
AND DATE3=to_date('27/08/2016','DD/MM/YYYY')
AND COL5='USD' for update;

<1.2> If value exists for NUM4 then update the NUM4 as below
update TABLE1
set NUM4 = NUM4+1
where NUM1=99
AND DATE3=to_date('27/08/2016','DD/MM/YYYY')
AND COL5='USD';
<1.3> else
Insert into TABLE1(NUM1,NUM2,DATE3,NUM4,COL5)
Values(99,1,to_date('27/08/2016','DD/MM/YYYY'),10,'USD');
end if;


Test Case :

1. Process P started with 10 instances running in parallel at 10:00AM
2. Each instance will be reading the input value NUM1,DATE1 in format(DD/MM/YYYY) and COL5 field and perform the operations as in design.
3. Now after a particular time one instance executed for update for specific combination and another instance did the same operation select for
update for the same combination of NUM1,DATE3 and COL5.
This caused the instances to wait and resulted in deadlock.

Expected : Could you please suggest any possible solution or approach which could resolve this problem ?
Will using PRAGMA AUTONOMOUS TRANSACTION by creating stored procedure and placing 1.2 and 1.3 in it will be a good solution ?

Awaiting your response !!

Thanks
Sumit

SQL> create table t as select 1 x, 1 y from dual;

Table created.

SQL> alter table t add primary key (x);

Table altered.

--
-- session 1
--
SQL> declare
  2    z int;
  3  begin
  4    select 1 into z from t where x = 2 for update;
  5    update t set y = y + 1 where x = 2;
  6  exception
  7    when no_data_found then
  8      insert into t values (2,2);
  9  end;
 10  /

PL/SQL procedure successfully completed.

--
-- session 2
--

SQL> declare
  2    z int;
  3  begin
  4    select 1 into z from t where x = 3 for update;
  5    update t set y = y + 1 where x = 3;
  6  exception
  7    when no_data_found then
  8      insert into t values (3,3);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2    z int;
  3  begin
  4    select 1 into z from t where x = 2 for update;
  5    update t set y = y + 1 where x = 2;
  6  exception
  7    when no_data_found then
  8      insert into t values (2,2);
  9  end;
 10  /

[session 2 now stuck]

--
-- session 1
--
SQL>
SQL> declare
  2    z int;
  3  begin
  4    select 1 into z from t where x = 3 for update;
  5    update t set y = y + 1 where x = 3;
  6  exception
  7    when no_data_found then
  8      insert into t values (3,3);
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 8
ORA-01403: no data found