MERGE und nie wieder ORA-00001? Doch!

In ferner Vergangenheit musste man sich beim Einfügen oder Ändern von Tabelleninhalten selbst darum kümmern, ob ein INSERT oder UPDATE erforderlich ist. Entweder hat man zuerst ein INSERT probiert, die Exception abgefangen , wenn der Datensatz schon vorhanden war, und dann halt ein UPDATE ausgeführt. Oder eben genau anders herum: Geprüft, ob der Datensatz geändert werden kann und wenn nichts geändert wurde, hat man das INSERT nachgeschoben. Oder es wurde erst einmal geprüft, ob ein Datensatz vorhanden ist und dann entschieden, ob INSERT oder UPDATE gemacht werden muss.

Das alles war mit Oracle 9i Geschichte, denn da wurde das MERGE-Statement eingeführt. Man konnte nun eine Art Join-Bedingung definieren. anhand derer die Datenbank selbst entschieden hat, ob ein INSERT oder UPDATE erforderlich ist. Nie wieder “ORA-00001: unique constraint violated”. Oder doch nicht?

Spannend wird es wie immer erst bei konkurrierenden Zugriffen. Starten wir mit einer simplen Test-Tabelle:

MARCO @ ORCL19:PDB1:>create table test_tab (                                             
  2       id  number                                                                     
  3      ,txt varchar2(100 char)                                                         
  4* );                                                                                  
                                                                                         
Table TEST_TAB created.                                                                  
                                                                                         
MARCO @ ORCL19:PDB1:>alter table test_tab add constraint test_tab_pk primary key (id);   
                                                                                         
Table TEST_TAB altered.                                                                  

Es gibt also einen Unique Constraint auf der ID-Spalte, soweit nicht ungewöhnlich. Nun starten wir in Session 1 und wollen einen Datensatz mittels MERGE einfügen oder ändern.

MARCO @ ORCL19:PDB1:>merge into test_tab dst          
  2  using (                                          
  3      select 1 id, 'Session 1' txt from dual       
  4  ) src                                            
  5  on (src.id = dst.id)                             
  6  when matched then                                
  7      update                                       
  8          set dst.txt = src.txt                    
  9  when not matched then                            
 10      insert (id, txt)                             
 11      values (src.id, src.txt)                     
 12* ;                                                
                                                
1 row merged.

Sehr gut, wie erwartet hat das geklappt. Starten wir eine zweite Session und machen dort das gleiche:

MARCO @ ORCL19:PDB1:>merge into test_tab dst
  2  using (
  3      select 1 id, 'Session 1' txt from dual
  4  ) src
  5  on (src.id = dst.id)
  6  when matched then
  7      update
  8          set dst.txt = src.txt
  9  when not matched then
 10      insert (id, txt)
 11      values (src.id, src.txt)
 12* ;

Diese Session wartet erst einmal ab, was in Session 1 passiert. Das Statement kehrt daher nicht zurück. Beenden wir also die Transaktion in Session 1:

MARCO @ ORCL19:PDB1:>commit;   
                               
Commit complete.

Nun steht der Datensatz mit ID=1 definitiv in der Datenbank. Session 2 könnte daher nun einhergehen und ein UPDATE auf den Datensatz machen. Jedoch:

MARCO @ ORCL19:PDB1:>merge into test_tab dst
  2  using (
  3      select 1 id, 'Session 1' txt from dual
  4  ) src
  5  on (src.id = dst.id)
  6  when matched then
  7      update
  8          set dst.txt = src.txt
  9  when not matched then
 10      insert (id, txt)
 11      values (src.id, src.txt)
 12* ;
 ^
Error starting at line : 1 in command -
merge into test_tab dst
using (
    select 1 id, 'Session 2' txt from dual
) src
on (src.id = dst.id)
when matched then
    update
        set dst.txt = src.txt
when not matched then
    insert (id, txt)
    values (src.id, src.txt)
Error report -
ORA-00001: unique constraint (MARCO.TEST_TAB_PK) violated

Session 2 begrüßt uns mit einem “ORA-00001”! Aber es hätte doch ein UPDaTE werden sollen? Prüfen wir den Inhalt der Tabelle in Session 2:

MARCO @ ORCL19:PDB1:>select * from test_tab;

   ID          TXT
_____ ____________
    1 Session 1

Der Datensatz aus Session 1 ist natürlich zu sehen. Die Änderung aus Session 2 jedoch nicht, denn es gab ja den “ORA-00001”. Ein weiterer MERGE-Versuch in Session 2 ist dann erfolgreich:

MARCO @ ORCL19:PDB1:>merge into test_tab dst
  2  using (
  3      select 1 id, 'Session 2' txt from dual
  4  ) src
  5  on (src.id = dst.id)
  6  when matched then
  7      update
  8          set dst.txt = src.txt
  9  when not matched then
 10      insert (id, txt)
 11      values (src.id, src.txt)
 12* ;

1 row merged.

MARCO @ ORCL19:PDB1:>select * from test_tab;

   ID          TXT
_____ ____________
    1 Session 2

Die Datenbank entscheidet also schon beim Start des SQLs, ob ein INSERT oder UPDATE erfolgen soll. Hat eine andere Session bereits einen identischen Key eingefügt, aber noch nicht commited, dann wird zwar auf diese Session gewartet, nicht aber das MERGE-Statement neu evaluiert, wenn die Sperre freigegeben wird. Daher kommt es trotz MERGE zum “ORA-00001”. Man kann und darf sich also nicht blind darauf verlassen, dass ein MERGE-Statement immer erfolgreich durchläuft.