0

Hi i have a scenario where 2 different merge statements has to run on the same table on 2 different data sets. the merge or update should happen without any locks.

i tried the isolation level 'SERIALIZABLE' but couldn't achieve the results.

can anyone help me out with this?

create table date_test ( MIS_DATE date not null, runskey integer not null);

date run 30/09/2016 1
29/09/2016 2

create table test_RP ( MIS_DATE date not null, runskey integer not null, acct_no number not null, band number, entity varchar2(10));

the above table has data for 2 different dates

date run cnt 30/09/2016 1 3000000 29/09/2016 2 3000000

i am running 2 merges on the table to modify the band column. For the data under 30/09, the band should be 1 and 2 for 29/09.

merge into test_rp a using (select mis_date,runskey from DATE_TEST where mis_Date='30-sep-2016') b on (a.mis_date=b.mis_Date) when matched then update set band=1;

merge into test_rp a using (select mis_date,runskey from DATE_TEST where mis_Date='29-sep-2016') b on (a.mis_date=b.mis_Date) when matched then update set band=1;

when both these updates are running in parallel on the same table, only when the changes of one of the merges are committed, the other one starts.

is there any way that both the merges run independently and modify the data.

Maharajaparaman
  • 141
  • 3
  • 12
  • what's your SQL? – Ori Marko Jun 28 '17 at 04:54
  • 1
    Show us the table definitions, and the merge statements you are using. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) If both statements **really** change different rows, there shouldn't be anything you have to do. –  Jun 28 '17 at 05:29
  • What locks? Please specify the error if any. Here, is some analysis from a case involving deadlock, [not row level; turned out to be at the block level): "Reference discussions on Oracle metalink on block level deadlocks about increasing the “interested transaction list”, aka ITL which is set by the initrans setting on the table. Other attribitutes on the table that may impact the parallel update is the percent free, PCTFREE. " INITRANS was done first (increased ti the # of "parallel" processes): deadlock still happened; then PCTFREE in creases and deadlock cleared. – Roger Cornejo Jun 28 '17 at 11:40
  • PFA the details.. there are no locks but the 1st transaction has to be committed for the 2nd transaction to initate the changes. – Maharajaparaman Jun 30 '17 at 06:47

0 Answers0