Concurrent MERGE
October 13, 2011 Leave a comment
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.
OraStory 
Recent Comments