MERGE and ORA-30926 – Teil 2 oder Unterschiede bei der Schreibkonsistenz zwischen Merge und Update

Im ersten Teil haben wir gesehen, wie Oracle ein Merge-Statement drei Mal ausführt, wenn es zu einem ORA-30926-Fehler kommt. Um zu verstehen, was dabei eigentlich passiert, sollten wir das Verständnis für das Konzept hinter „update restarts“ oder auch manchmal „mini rollbacks“ genannt, auffrischen. Das Konzept ist sehr gut von Tom Kyte beschrieben: Teil I, Teil II and Teil III. Wenn der Begriff „write consistency“ für Sie unbekannt ist, empfehle ich sehr, ertsmal diesen Links zu folgen.

Vielen Dank auch an Sergey Markelenkov für seinen ausgezeichneten Artikel über write consistency und statement restarts, leider nur in russisch verfügbar. Der ist auf jeden Fall eine Übersetzung Wert, ist aber 40 Seiten lang, sodass ich hier nur auf einige wichtige Erkenntnisse und Schlüsse zurückgreifen werde.

UPDATE restart

Zuerst können wir zeigen, wie ein statement restart mit einem UPDATE stattfindet. Ich bin davon ausgegangen, dass es dann genauso auch mit dem UPDATE-Teil vom  MERGE funktioniert. Tut es aber nicht!

Wir legen zurst eine Testtabelle mit einem Zeilentrigger an. Dieser Trigger referenziert keine :NEW oder :OLD – Werte und ist nur dafür da, um zu sehen, wie oft er „zündet“. Dann aktualisieren wir den Datensatz mit  ID=2 auf ID=3 ohne die Änderung mit einem COMMIT festzuschreiben.

--Session 1
DROP TABLE t_target;

CREATE TABLE t_target  (id NUMBER, val NUMBER);

INSERT INTO t_target (id, val) VALUES  (1,1);
INSERT INTO t_target (id, val) VALUES  (2,1);

COMMIT;

CREATE OR REPLACE TRIGGER test_trg
BEFORE UPDATE ON t_target
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Fired');
END;
/

UPDATE t_target SET id = 3 WHERE id = 2;

In einer anderen Session versuchen wir Datensätze mit ID < 3 zu aktualisieren. Natürlich wird diese Session blockiert und muss warten, bis die Session 1 entweder einen Commit oder Rollback macht.


--Session 2

UPDATE t_target t SET  t.val = t.val + 10 WHERE t.id < 3;
&#91;/code&#93;

Aber was für ein Ergebnis können wir vom  UPDATE in der Session 2 erwarten, wenn in der 1. Session ein Commit ausgeführt wird? Wird nur ein Datensatz auktualisert oder zwei? Es gab zwei Datensätze mit ID < 3 zum Zeitpunkt als das UPDATE in der Session 2 gestartet wurde. Aber nach dem Festschreiben in der Session 1 hat der zweite Datensatz die WHERE-Bedingung nicht mehr erfüllt.

<br>


UPDATE t_target t
SET    t.val = t.val + 10 
WHERE  t.id < 3;

Fired
Fired
Fired

1 row updated.
...
-------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |      3 |        |      0 |
|   1 |  UPDATE            | T_TARGET |      3 |        |      0 |
|*  2 |   TABLE ACCESS FULL| T_TARGET |      3 |      2 |      4 |
-------------------------------------------------------------------
&#91;/code&#93;

Wir können hier das Restart von unserem UPDATE beobachten. Das hat erst die zu aktualisierende Datensätze im konsistenten Modus zum Startzeitpunkt des Statements  identifiziert. Dann versucht Oracle, einen Lock auf diese Datensätze zu plazieren und diese zu aktualisieren. Der Datenbank-Block wird noch mal im "current" Modus geholt. Es macht Sinn, denn wir wollen die aktuellste Kopie der Daten aktualisieren und nicht die möglicherweise veraltete. Hier wird dann geprüft, ob die Daten, die in der Suchbedingung partizipieren, unverändert blieben. Falls die WHERE-Bedingung nicht mehr zutreffend ist, wird das ganze Statement neu gestartet. Alle Änderungen, die das UPDATE zu diesem Zeitpunkt gemacht hat, werden zurückgerollt. Wir fragen eine neue SCN an und selektieren alle zu aktualisierenden Datensätze im konsitenten Modus respektive dieser neuen SCN. Dabei werden diese Datensätze blockiert (aka SELECT FOR UPDATE). Soll das nicht möglich sein, wird neu versucht, insgesamt bis 5000 Mal. Nach dem alle Datensätze blockiert wurden, kann Oracle die alle sicher aktualsieren.

Zurück zu unserem Beispiel: das  UPDATE in der Session 2 hat den ersten Datensatz mit ID=1 aktualisiert, der Trigger wird zum ersten Mal ausgelöst. Dann muss diese zweite Session auf die erste warten, weil unmöglich ist, einen Lock auf den Datensatz mit ID=2 zu platzieren. Nach dem Commit in der Session 1 wird es dann möglich sein. Der Trigger wird zum ersten Mal ausgelöst. Aber dann sagt die Prüfung, dass der Datensatz der WHERE-Bedingung nicht mehr entspricht, denn die Session 1 hat die ID auf 3 aktualisiert. Die bereits gemacht Änderung am ersten Datensatz wird rückgängig gemacht. Ein neuer Versuch wird gestartet, mit einer neuen SCN und kann die Änderungen der Seesion 1im konsistenten Modus sehen. Demzufolge wird nur ein Datensatz mit ID=1 selektiert und dann aktualisiert. Die dritte Auslösung vom Trigger.

Wir können sehen, wie Oracle uns 3 Starts in Explain-Plan-Ausgabe zeigt. Auch A-Rows ist 4, obwohl wir nur einen Datensatz aktualisiert haben. Ich denke, es wurde beim ersten Versuch auf zwei hochgezählt, dann auf drei beim SELECT FOR UPDATE und dann auf vier bei der eigentlichen Aktualisierung vom Datensatz.

We können auch eine Bestätigung von dem Restart sehen, indem wir die Session-Statistiken vor und nach dem UPDATE abfragen:
<ul>
	<li><em>calls to kcmgas</em> - we können den Request der neuen SCN sehen</li>
	<li><em>rollback changes - undo records applied</em> - we sehen ein Rollback ohne ihn explizit auszuführen</li>
	<li><em>table scan rows gotten</em> - wird größer als erwartet sein</li>
</ul>
Für mich ist es eine Art von "optimistic locking",  implementiert direkt in der Datenbank. Ähnlich wie eine Applikation gegen verlorene Updates vorgehen sollte, indem z.B. die abgefragten Daten mit den aktuellen Daten in der Datenbank unmittelbar vor der Aktualisierung abgeglichen werden, stellt Oracle hier sicher, dass wirklich nur die Datensätze  aktualisiert werden, die zur Startzeit identifiziert wurden. Und wenn das nicht klappt, wird diese Startzeit eben neu definiert 😉   Spaß beiseite - ein ziemlich sinnvolles Feature.

Einige generelle Anmerkungen noch: nicht nur bei UPDATE Statements, sondern auch bei DELETE, SELECT FOR UPDATE und MERGE können Restarts auftreten. Das kann nur passieren, wenn die Transaktion in der Isolationsebene READ COMMITTED arbeitet. Bei  SERIALIZABLE bekommt man in entsprechender Situation den Fehler ORA-08177 can't serialize access.

Es gibt einen anderen Fall, der zum Restart führt: wenn wir einen BEFORE Row Trigger haben, der die :OLD Werte referenziert, und diese sich seit Statement Beginn geändert haben. Wir betrachten aber diesen Fall nicht in dem Post.

<strong>Und was ist mit MERGE?</strong>

Aber warten Sie! Wollten wir nicht eigentlich, das Verhalten von einem MERGE Statement untersuchen? Verhält sich der UPDATE-Teil von einem MERGE genauso? I würde sagen, ja, sollte definitiv so sein, aber...

Wir werden in der Session 1 exakt dasselbe tun. In der Session 2 werden wir das UPDATE mit einem MERGE ersetzen und sehen, was nach dem Commit in der Session 1 passiert:


MERGE INTO t_target t
USING (select 1 from dual) q
ON    (t.id < 3) 
WHEN MATCHED THEN UPDATE SET  t.val = 0; 

Fired 
Fired 

2 rows merged. 

--------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
|   0 | MERGE STATEMENT      |          |      1 |        |      0 |
|   1 |  MERGE               | T_TARGET |      1 |        |      0 |
|   2 |   VIEW               |          |      1 |        |      2 |
|   3 |    NESTED LOOPS      |          |      1 |      2 |      2 |
|   4 |     FAST DUAL        |          |      1 |      1 |      1 |
|*  5 |     TABLE ACCESS FULL| T_TARGET |      1 |      2 |      2 |
--------------------------------------------------------------------

SELECT * FROM t_target;

 ID   VAL
---------- ----------
  1     0
  3     0
&#91;/code&#93;

Offensichtlich kein Restart hier! Unser MERGE hat gerade die Spalte VAL zu 0 aktualisiert an einem Datensatz, der dem Suchkriterium ID < 3 gar nicht mehr entspricht!

Aber was passiert, wenn wir das Beispiel so abändern, dass die Session 1 die Spalte VAL statt ID aktualisiert?
<br>

--Session 1

UPDATE t_target SET val = 3 WHERE id = 2;

--Session 2

MERGE /*+ gather_plan_statistics */ INTO t_target t
USING (SELECT 10 val FROM dual) q
ON    (t.id < 3)  
WHEN MATCHED THEN UPDATE SET  t.val = t.val+q.val; 

Fired 
Fired 
Fired 
Fired 

2 rows merged. 
...
--------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
|   0 | MERGE STATEMENT      |          |      3 |        |      0 |
|   1 |  MERGE               | T_TARGET |      3 |        |      0 |
|   2 |   VIEW               |          |      3 |        |      6 |
|   3 |    NESTED LOOPS      |          |      3 |      2 |      6 |
|   4 |     FAST DUAL        |          |      3 |      1 |      3 |
|*  5 |     TABLE ACCESS FULL| T_TARGET |      3 |      2 |      6 |
--------------------------------------------------------------------

SELECT * FROM t_target;

 ID VAL
--- ---
  1  11
  2  13
&#91;/code&#93;
Man kann klar sehen, dass ein Restrat stattgefunden hat.  Unser MERGE hat die Spalte VAL aktualisiert und 10 zu dem Wert 3 addiert. Dieser Wert 3 war wiederum das Ergebnis von dem Update aus der Session 1, das wir erst NACH (!) dem MERGE gestartet und committet hatten.

Und was ist, wenn unsere Session denselben Datensatz mehrfach zu aktualisieren versucht? Kein UPDATE in der Session 1 diesmal, einfach ein MERGE in der Session 2

<br>

MERGE /*+ gather_plan_statistics */ INTO t_target t
USING (SELECT 2 id, 10 val FROM dual
       UNION ALL
       SELECT 2 id, 10 val FROM dual) q
ON (t.id = q.id)
WHEN MATCHED THEN UPDATE SET  t.val = t.val + q.val;
Fired
Fired
Fired

MERGE /*+ gather_plan_statistics */ INTO t_target t
                                         *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

--------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
|   0 | MERGE STATEMENT      |          |      3 |        |      0 |
|   1 |  MERGE               | T_TARGET |      3 |        |      0 |
|   2 |   VIEW               |          |      3 |        |      5 |
|*  3 |    HASH JOIN         |          |      3 |      2 |      5 |
|   4 |     VIEW             |          |      3 |      2 |      6 |
|   5 |      UNION-ALL       |          |      3 |        |      6 |
|   6 |       FAST DUAL      |          |      3 |      1 |      3 |
|   7 |       FAST DUAL      |          |      3 |      1 |      3 |
|   8 |     TABLE ACCESS FULL| T_TARGET |      3 |      2 |      6 |
--------------------------------------------------------------------

Erstmal, sieht man, dass das MERGE restartet wurde. Danach kommt aber der Fehler ORA-30926. Eine Trace-Datei wird dabei erzeugt, in der wir sehen können, was dem Oracle nicht gefällt:

Compare value:
  cmpp            : 0x6da03ff1
  cmpl            : 2
  cmpf            : 0
  Data: < c1 02 >
Current value:
  u->kduudcol[(piececol)]    : 0x70c9bff1
  u->kduudlen[(piececol)]    : 2
  u->kduudflag[(piececol)]   : 0
  Data: < c1 0c >

Die erste Aktualisierung ändert den Wert der Spalte VAL auf 11, das ist "C1 0C" im internen Speicherformat. Der zweite Versuch stellt fest, dass der aktuelle Wert der Spalte "C1 0C" nun anders ist, als der vom konsistenten Lesemodus "C1 02" (interne Darstellung von 1). Mein Verständnis ist es, an der Stelle ist klar, dass der Fehler ORA-30926 zurückgegeben wird. Warum versucht Oracle trotzdem dieselbe Vorgehensweise mit dem Restart? Es kann doch an der Situation nichts ändern...

Fazit

Wenn wir Datensätze einer Tabelle aktualisieren, können wir hier zwei Aspekte betrachten, unabhängig davon, ob wir es mit einem UPDATE oder MERGE tun.  Der erste: welche Datensätze werden aktualisiert? Kann andere DML-Aktivität, die nach dem Starten von der Aktualisierung stattfindet, die Menge dieser Datensätze beeinflüßen?   Und der zweite: kann es zu "verlorenen updates" kommen?

Wenn meine Erkenntnisse korrekt sind, kann man sagen:

  • Ein lang laufendes UPDATE kann die Änderungen sehen, die nach dem Start gemacht und festgeschrieben wurden, wenn diese die WHERE-Bedingung beeinflüßen werden und somit ide Menge der zu aktualisierenden Datensätze. Ein UPDATE wird nur Datensätze aktualsieren, die im konsistenten Lesemodus zur SCN vom Startzeitpunkt oder zu einer neuen  SCN, falls es zu einem Restart kam, identifiziert wurden. Es kann aber auch sein, dass das ganze Update fehlschalgen wird, falls es nicht möglich war die Datensätze nach dem Restart zu blockieren. Es gibt aber keinen Mechanismus, um vor verlorenen Updates zu schützen.
  • Ein lang laufendes MERGE wird nur Datensätze aktualsieren, die im konsistenten Lesemodus zur SCN vom Startzeitpunkt vom MERGE identifiziert wurden. Die Menge wird eindeutig an diesem Zeitpunkt definiert. Wenn andere Transaktion die Daten ändert, die in der ON-Bedingung benutzt wird, und diese Änderung festschreibt, wird das keine Auswirkung haben - die Datensätze werden trotzdem mit dem zuvor gestarteten MERGE aktualisiert. Auf der anderen Seite, ein MERGE wird restartet bei Änderungen in den  Spalten  der SET-Klausel. Somit gibt es quasi einen Schutz vor verlorenen Updates für Laufzeit des Statements

Dieser Unterschied ist etwas verwirrend für mich. Meines Erachtens handelt es sich hier um einen Bug. Ich habe eine Diskussion auf AskTom gefunden, wo Tom Kyte damit auch einverstanden ist und mitteilt, dass er dafür einen Bug eingetsellt hat (2006!) Das scheint aber ein nicht öffentlicher Bug zu sein, denn man findet ihn nicht auf MOS-Seiten.

Hdr: 5214824 Abstract: READ CONSISTENCY NOT WORKING CORRECTLY ON MERGE STATEMENT

Nach dieser Diskussion auf Ask Tom wird es in dem Bug bemängelt, dass der Restart nicht stattfindet bei Änderungen in ON-Spalten. Nun, dass der Restart bei Änderungen in SET-Spalten  doch stattfindet, ist genauso nicht dokumentiert. In der Dokumentation steht nur das:

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

Das erklärt aber das ganze Verhalten nicht wirklich. Das erklärt nur, dass der ORA-30926 durchaus berechtigt kommt. Wenn er kommt... Denn es gibt wohl gewisse Unregelmäßigkiten dabei, was ich in einem anderen Post bereits erwähnt habe.

Ein anderer Bug mit MERGE ist es auch meiner Sicht, dass Oracle ein Restart bei Datendiskrepanz in SET-Spalten auch dann durchführt, wenn diese Diskrepanz durch vorherige Aktualisierung aus derselben Sitzung verursacht wird und es berechtigterweise zu ORA-30926 kommt. Aber davor wird die ganze Arbeit unnötig dreifach wiederholt. Ich kann keine plausible Erklärung finden, warum das nötig sein könnte. Sie vielleicht?

Die ganze Forschung zeit nochmal, wie gefährlich es sein kann ein Massenupdate durchzuführen, wenn andere DML-Aktivität auf der Tabelle stattfindet. Das Ergebnis variiert vom deutlich höheren Resourcenverbrauch bis zu unvorhersagbaren und schlicht falschen Ergebnissen.

Anzumerken ist es auch, dass das erwähnte Feature von MERGE, wobei nach Feststellung der Datendiskrepanz in den SET-Spalten ein Restart durchgeführt wird, keinesfalls als Ersatz für ein echtes Konzept vom Optimistc Locking in Ihrer  Applikation verstanden werden darf. Hierbei geht es nur um die Änderungen, die nach dem Starten vom MERGE gemacht wurden und nicht in der Zeit zwischen Abfragen der Daten, Editieren in der Applikation und dann dem Ausführen von dem MERGE-Befehl.

Weiterführende Links

Diskussion on Asktom über write consistency

Weitere Diskussion über Merge

Oracle Dokumentation - Merge Statement

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*