Fragen zu Stored Procs und Transaktionen

[Karsten Strobel]

Hallo,
ich habe ein paar Fragen zu Transaktionen beim SQL Server im Zusammenhang mit Stored Procedures und im Vergleich mit dem Interbase Server:

Beim SQL-Server wird oft empfohlen, dass man mit BEGIN/END TRY..BEGIN/END CATCH Blöcken arbeitet. Wenn man dies _nicht_ tut (so habe ich den Eindruck), wird eine Prozedur nicht automatisch bei einem Fehlen (z.B. Verletzung eines Contraints) abgebrochen und die Änderungen zurückgenommen, sondern es geht einfach weiter mit dem nächsten Statement der Proc. Wenn man aber mit TRY..CATCH arbeitet, wird bei einem solchen Fehler immer der CATCH Block angesprungen. Stimmt die Beobachtung so weit?

Häufig sieht man Beispiele, in denen innerhalb einer Stored Proc eine Transaktionen gestartet wird (BEGIN TRANSACTION) und diese ggf. mit ROLLBACK (im CATCH Block) zurückgenommen wird. Wie verhält es sicht, wenn der aufrufende Client seinerseits den Prozeduraufruf in eine Transaktion einbettet? Werden die Transkationen innerhalb und außerhalb der Proc dann automatisch geschachtelt?

Bei Interbase hat jeder Datenbankzugruff einen Transaktionskontext, entweder implizit oder explizit. Der Transaktionskontext steuert zudem die Isolation und andere Parameter. Ist dies prinzipiell beim SQL Server auch so?

Vielen Dank für Euro Zeit
KS

[Andreas Kosch]

Hallo Karsten,

...wird bei einem solchen Fehler immer der CATCH Block angesprungen. Stimmt die Beobachtung so weit?

wenn "immer" aus dem Statz gestrichen wird, ist er richtig. Denn auch beim MS SQL Server 2005 kann ein TRY...CATCH-Block nicht alle Fehler abfangen. Zum Beispiel wird beim SELECT-Aufruf für eine nicht existiertende Tabelle der CATCH-Zweig nicht ausgelöst:

BEGIN TRY
  SELECT * FROM DieTabelleGibtEsNicht;
  PRINT 'Versuch 3 hat überlebt.';
END TRY
BEGIN CATCH
  PRINT 'Versuch 3 hat einen Fehler verursacht.'; 
END CATCH

In der Dokumentation wird daher nach dem Error Scope unterschieden:

Bei der Default-Einstellung von XACT_ABORT gilt das folgende Regelwerk:

a) Statement Termination: Die aktuelle Anweisung wird abgebrochen, aber die restliche Stapelanweisung wird fortgesetzt. Es gibt kein automatisches Rollback, der CATCH-Zweig wird aktiviert.

b) Level Abort: Der aktuelle Level of Execution (Bsp: Stored Procedure) wird abgebrochen, der Aufrufer erhält ein Statement Abort. Es gibt kein automatisches Rollback, der CATCH-Zweig wird aktiviert.

c) Batch Abort: Die aktuelle Stapelanweisung wird abgebrochen (dies betrifft auch den Aufrufer). Der SQL Server führt ein automatisches Rollback aus, der CATCH-Zweig wird aktiviert.

d) Connection Termination: Aufgrund des hohen Schweregrads wird die Datenbankverbindung sofort getrennt: Der SQL Server führt ein automatisches Rollback aus, der CATCH-Zweig wird nicht aktiviert.


Normalerweise nimmt der SQL Server immer dann alle Anweisungen über ROLLBACK zurück, wenn es beim Abarbeiten zu einem Fehler gekommen ist oder wenn die Datenbankverbindung aus irgendeinem Grund ausgefallen ist, bevor ein COMMIT ausgelöst wurde. Wenn jedoch ein Laufzeitfehler wie zum Beispiel ein Verstoß gegen eine CONSTRAINT-Regel in einer SQL-Stapelanweisung auftritt, die eine explizite Transaktion gestartet hat, so setzt der SQL Server in der Voreinstellung nur die SQL-Anweisung zurück, die diesen Laufzeitfehler provoziert hat (d.h. in diesem Fall wird kein ROLLBACK ausgelöst!). Über die SET XACT_ABORT-Anweisung kann diese Voreinstellung geändert werden. In der Voreinstellung hat XACT_ABORT den Wert OFF. Mit der Anweisung SET XACT_ABORT ON ändert sich das Verhalten des SQL Server: Die meisten Statement termination errors (Beispiel: Doppelter Primärschlüsselwert, NOT NULL-Fehler, CHECK bzw. CONSTRAINT-Fehler, Berechtigungsfehler) wirken als Batch-aborting errors, so dass die Stapelverarbeitung an dieser Stelle abgebrochen wird (d.h. alle relevanten Fehler landen im CATCH-Block - wobei als Nebeneffekt auch die Transaktion in den Failed-Zustand übergeht).

Beim SQL Server 2000 kannte die aktuelle Session nur 2 Transaktionszustände: Aktiv und Inaktiv. Mit dem SQL Server 2005 kommt der 3. Transaktionszustand Failed (alias "doomed") hinzu. Eine Transaktion fällt automatisch in den Failed-Status, wenn ein Fehler mit dem Severity-Level 17 (oder höher) auftritt, der verhindert, dass diese Transaktion über COMMIT abgeschlossen werden kann. In diesem Zustand darf die Session keine Aktionen auslösen, die als Seiteneffekt die Logdatei der Datenbank beschreiben (d.h. nur Lesezugriffe sind noch erlaubt). Der neue Transaktionszustand Failed kann über die Funktion XACT_STATE erkannt werden (d.h. im CATCH-Zweig dient der Wert -1 als Indikator für den notwendigen ROLLBACK-Aufruf).

...jeder Datenbankzugruff einen Transaktionskontext, entweder implizit oder explizit

Das ist beim MS SQL Server genau so, wobei gleich 3 Modelle unterstützt werden:

1. Autocommit-Transaktionsmodus (Default)
2. Impliziter Transaktionsmodus (SET IMPLICIT_TRANSACTIONS ON)
3. Expliziter Transaktionsmodus (BEGIN TRANSACTION, SqlTransaction-Klasse von ADO.NET, Deklarative Transaction vom DTC (COM+), TransactionScope-Klasse von .NET 2.0)

Der Unterschied des impliziten Transaktionsmodus zum Autocommit-Modus besteht darin, dass diese automatisch (implizit) gestartete Transaktion offen bleibt. Erst dann, wenn COMMIT TRANSACTION aufgerufen wird, ist die Aktion für andere Datenbanksitzungen sichtbar. Beim Autocommit-Modus wird auch das COMMIT automatisch ausgelöst, wenn kein Fehler aufgetreten ist.

.. dann automatisch geschachtelt?

Im expliziten Transaktionsmodus kann eine Stapelanweisung mehrfach BEGIN TRANSACTION aufrufen, um verschiedene Transaktionen zu schachteln. Die Anzahl der aktiven Transaktionen stellt der SQL Server über @@TRANCOUNT zur Verfügung. Im Normalfall - wenn die Transaktion erfolgreich bestätigt werden soll - muss je BEGIN TRANSACTION auch ein korrespondierender COMMIT TRANSACTION-Aufruf erfolgen. Nur dann, wenn die Transaktion fehlgeschlagen ist und über ein ROLLBACK verlassen werden soll, reicht ein einziger ROLLBACK TRANSACTION-Aufruf aus. In diesem Fall wird der Zählerstand von @@TRANCOUNT sofort auf 0 gesetzt. Der SQL Server 2005 unterstützt die so genannte Named Transaction, die als Lesezeichen innerhalb einer T-SQL-Stapelanweisung genutzt werden kann. Über SAVE TRANSACTION wird ein Lesezeichen gesetzt, dessen einzelne Aktionen später zum Beispiel über ROLLBACK TRANSACTION zurückgenommen werden können, um in der Fehlerbehandlung einen 2. Versuch zu starten.

Wenn die Datenbankverbindung (Session) unter der Kontrolle des DTC (Distributed Transaction Coordinator von Windows) ist, greift der Mechanismus des 2-Phasen-Commit auch dann, wenn nur eine einzige Datenbank beteiligt ist. Der DTC kann somit auch dann eine Transaktion vollständig zurücknehmen, wenn innerhalb der Stored Procedure bereits ein COMMIT alle Schreibzugriffe bestätigt hat.

[Karsten Strobel]

Hallo Andreas,
danke für Deine ausführliche Antwort!!!
Gruss
K.