Concurrent MERGE

Another post on MERGE, this time about a situation I’ve seen being done a couple of times in the last few months.

This is a bit of a back-to-basics article with a twist of “why do you even need to do this” – a very valid question.

Remember when you used to write insert/update code like this:

SQL> create table t1
  2   (col1 number 
  3   ,col2 number             
  4   ,constraint pk_t1 primary key(col1));

Table created.

SQL> select * from t1;

no rows selected

SQL> begin
  2    insert into t1
  3    (col1, col2)
  4    values
  5    (1,1);
  6    exception 
  7       when dup_val_on_index then
  8         update t1
  9         set    col2 = col2 + 1
 10         where  col1 = 1;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

      COL1       COL2
---------- ----------
         1          1

SQL> begin
  2    insert into t1
  3    (col1, col2)
  4    values
  5    (1,1);
  6    exception 
  7       when dup_val_on_index then
  8         update t1
  9         set    col2 = col2 + 1
 10         where  col1 = 1;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

      COL1       COL2
---------- ----------
         1          2

SQL> 

Or maybe you did it the other way round because you figured that over time you’d insert a particular row once but maybe update lots of times so it would probably be more efficient?

SQL> delete from t1;

1 row deleted.

SQL> begin
  2    update t1
  3    set    col2 = col2 + 1
  4    where  col1 = 1;
  5    if sql%notfound
  6    then
  7       insert into t1
  8       (col1, col2)
  9       values
 10       (1,1);
 11    end if;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> begin
  2    update t1
  3    set    col2 = col2 + 1
  4    where  col1 = 1;
  5    if sql%notfound
  6    then
  7       insert into t1
  8       (col1, col2)
  9       values
 10       (1,1);
 11    end if;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

      COL1       COL2
---------- ----------
         1          2

SQL> 

And then along came MERGE and you thought “how cool! no more insert…update”?

SQL> delete from t1;

1 row deleted.

SQL> merge 
  2  into   t1
  3  using (select 1 col1
  4         ,      1 col2
  5         from   dual) x
  6  on    (t1.col1 = x.col1)
  7  when matched then
  8         update
  9         set    col2 = col2 + 1
 10  when not matched then
 11         insert
 12         (col1, col2)
 13         values
 14         (x.col1, x.col2);

1 row merged.

SQL> merge 
  2  into   t1
  3  using (select 1 col1
  4         ,      1 col2
  5         from   dual) x
  6  on    (t1.col1 = x.col1)
  7  when matched then
  8         update
  9         set    col2 = col2 + 1
 10  when not matched then
 11         insert
 12         (col1, col2)
 13         values
 14         (x.col1, x.col2);

1 row merged.

SQL> select * from t1;

      COL1       COL2
---------- ----------
         1          2

SQL> 

Well, perhaps there are situations where this usage of MERGE is not the silver bullet to end all INSERT … UPDATE logic?

To cut a patchy and incomplete story short, I’ve seen a couple of applications which result in concurrent transactions having to save some data based on the same key (which may or may not yet exist).

And I’ve got two things to say about this.

Firstly, the merge can fail in a way that the old insert … update will not:

session1>delete from t1;

1 row deleted.

session1>commit;

Commit complete.

session1>merge 
  2  into   t1
  3  using (select 1 col1
  4         ,      1 col2
  5         from   dual) x
  6  on    (t1.col1 = x.col1)
  7  when matched then
  8         update
  9         set    col2 = col2 + 1
 10  when not matched then
 11         insert
 12         (col1, col2)
 13         values
 14         (x.col1, x.col2);

1 row merged.

session1>

Session 2 waits:

session2>merge 
  2  into   t1
  3  using (select 1 col1
  4         ,      1 col2
  5         from   dual) x
  6  on    (t1.col1 = x.col1)
  7  when matched then
  8         update
  9         set    col2 = col2 + 1
 10  when not matched then
 11         insert
 12         (col1, col2)
 13         values
 14         (x.col1, x.col2);

until

session1>commit;

Commit complete.

session1>

then

merge
*
ERROR at line 1:
ORA-00001: unique constraint (DOM.PK_T1) violated

session2>

Not unexpected, I hope?
But you won’t get this with the old “insert … update”:

session1>delete from t1;

1 row deleted.

session1>commit;

Commit complete.

session1>begin
  2    insert into t1
  3    (col1, col2)
  4    values
  5    (1,1);
  6    exception 
  7       when dup_val_on_index then
  8         update t1
  9         set    col2 = col2 + 1
 10         where  col1 = 1;
 11  end;
 12  /

PL/SQL procedure successfully completed.

session1>

Session 2 waits:

session2>begin
  2    insert into t1
  3    (col1, col2)
  4    values
  5    (1,1);
  6    exception 
  7       when dup_val_on_index then
  8         update t1
  9         set    col2 = col2 + 1
 10         where  col1 = 1;
 11  end;
 12  /

Session1 commits:

session1>commit;

Commit complete.

session1>

At which point session2 is hunky dory:

PL/SQL procedure successfully completed.

session2>

However, you might not have the same success with the old “update … insert” code:

session1>delete from t1;

1 row deleted.

session1>commit;

Commit complete.

session1>begin
  2    update t1
  3    set    col2 = col2 + 1
  4    where  col1 = 1;
  5    if sql%notfound
  6    then
  7       insert into t1
  8       (col1, col2)
  9       values
 10       (1,1);
 11    end if;
 12  end;
 13  /

PL/SQL procedure successfully completed.

session1>

----
session2>begin
  2    update t1
  3    set    col2 = col2 + 1
  4    where  col1 = 1;
  5    if sql%notfound
  6    then
  7       insert into t1
  8       (col1, col2)
  9       values
 10       (1,1);
 11    end if;
 12  end;
 13  /

----
session1>commit;

Commit complete.

session1>

-----

begin
*
ERROR at line 1:
ORA-00001: unique constraint (DOM.PK_T1) violated
ORA-06512: at line 7

session2>

It’s all fairly obvious stuff, right?

But the second thing to say is to question why this situation might arise in the first place.

I’m not going to argue in favour of it but having seen it a couple of times recently, this is just an observation that if you are doing this then the different methods are not equal.

A more valid question might be which of these concurrent transactions is the one which should be persisted?
(the premise is that this is data with a key which may or may not exist, the attributes of the entity might be the same, might be different)

And if it doesn’t matter (how can it not matter?) then why not use the MERGE for ease of code and just discard the dup_val_on_index exception should it arise.

MERGE oddity

Here’s a little observation on some slightly odd behaviour with MERGE and some apparent inconsistency of error ORA-30926 (prompted by a question on the otn forums).

If you’re not familar with ORA-30926:

ORA-30926: unable to get a stable set of rows in the source tables

then this is expected with MERGE if the set of SOURCE rows (identified in the USING clause) that you’re merging into the TARGET (i.e. the MERGE INTO <target>) contain duplicate entries for the key columns concerned, i.e. if there are duplicates, which one do you want to be preserved by the MERGE.

Setup:

SQL> create table a
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> create table b
  2  (key number
  3  ,val varchar2(1));

Table created.

SQL> 
SQL> insert into a values(1,'A');

1 row created.

SQL> 
SQL> insert into b values(1,1);

1 row created.

SQL> 
SQL> insert into b values(1,2);

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> select * from b;

       KEY V
---------- -
         1 1
         1 2
SQL> 

We might expect ORA-30926 to be raised in this next example, but it’s not:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 
SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> 
SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> 

But if we run it again:

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b) b
  5  on (a.key = b.key)
  6  when matched then 
  7   update
  8   set a.val = decode(b.val,'1','A','B');
merge into a
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables



SQL> rollback;

Rollback complete.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

If we change this slightly by adding and altering an ORDER BY:

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val ASC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 B

SQL> merge into a
  2  using (select b.val
  3         ,      b.key
  4         from   b
  5         order by b.val DESC) b  --<----- ORDER BY   
  6  on (a.key = b.key)
  7  when matched then 
  8   update
  9   set a.val = decode(b.val,'1','A','B');

2 rows merged.

SQL> select * from a;

       KEY V
---------- -
         1 A

SQL> 

Perhaps we get an insight into how the check that results in ORA-30926 is implemented?

It seems to be not as simple as a question of whether there are duplicates in the SOURCE but whether the MERGE would update the same row twice – i.e. an UPDATE that results in an UPDATE not just updating it to the existing value.

The bottom line is that you should never be merging in multiple updates to the same target row.
If there are such incoming data, you have to have logic to filter that down.

Follow

Get every new post delivered to your Inbox.

Join 62 other followers