SQL Server Transaction und Error Handling

Modified on 16/06/2009 15:52 by Manuel Siebert Categorized as Plattform
((([image| |http://www.cp-austria.at/website/DesktopModules/Wiki/images/Home.gif|MainPage] » [MainPage|Startseite] » [netFramePlattform|netFrame als Plattform Menü] » [TippsundTricks|Tipps und Tricks] » SQL Server Transaction und Error Handling | {searchbox} [Search.aspx?FullText=1|Erweiterte Suche])))
{br}
# [#1|Einleitung]
# [#2|Transaktionen]
# [#3|Geschachtelte Transaktionen]
# [#4|Save TRAN und Save Points]
# [#5|Error Handling]
{br}
{br}
[anchor|#1]
==Einleitung==
Die folgenden Beispiele beziehen sich auf die '''Pubs''' Datenbank, welche bei jeder neuen Installation eines SQL Servers bereits vorhanden sind. Sollten Sie eine frische Installation der "'''Pubs'''" Datenbank benötigen, folgen Sie dem Text.{br}
{br}
*Führen Sie den folgenden osql Befehl aus um die '''Pubs''' Datenbank zu finden, wir benutzen dazu die Stored Procedure sp_detach_db.{BR}{BR}
<code sql>osql -U sa -P "" -Q "exec sp_detach_db 'Pubs'" </code>{BR}
*Löschen Sie die Datenbankdateien der "'''Pubs'''" Datenbank (pubs.mdf, pubs_log.ldf). Diese Dateien befinden sich im '''\Data''' Ordner.{BR}{BR}
*Die Neuinstallation der "'''Pubs'''" Datenbank benötigt das Instpubs.sql Skript.{BR}{BR}
<code sql>osql -U sa -P "" -i 
"C:\Program Files\Microsoft SQL Server\MSSQL\Install\InstPubs.sql"</code>{BR}
{br}
{TOP}
{br}
{br}
[anchor|#2]
==Transaktionen==
Transactions gruppieren eine Reihe von Aufgaben in eine einzelne. Jede Transaction beginnt mit einer bestimmten Aufgabe und endet erst dann wenn alle Aufgaben erfolgreich ausgeführt wurden. Wenn eine einzelne Aufgabe nicht ausgeführt werden kann, kann die Transaction nicht ausgeführt werden. Aus diesem Grund hat eine Transaction nur zwei Ergebnisse: Erfolg oder Fehler. Fehlerhafte Schritte ergeben eine fehlerhafte Transaction.{br}
{br}
Benutzer können zwei oder mehrere Transact-SQL in eine einzelne Transaction mit folgenden Anweisungen gruppieren:{br}
{br}
* Begin Transaction
* Rollback Transaction
* Commit Transaction
{br}
Läuft etwas mit den gruppierten Anweisungen schief, müssen alle Änderungen abgebrochen werden. Der Prozess um Änderungen umzukehren wird '''ROLLBACK''' genannt.{br}
Wenn alles sauber funktioniert werden alle Änderungen zusammen in die Datenbank aufgenommen. In der SQL Terminologie nennt man dies '''COMMIT'''.{br}
{br}
Hier ist ein kleines Beispiel für eine Transaction:{br}
{br}
<code sql>USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END</code>
{br}
Bevor die Echtzeitverarbeitung startet sagt der Befehl '''BEGIN TRAN''' das alle folgenden Anweisungen als eine Transaction zu behandeln sind, gefolgt von zwei '''UPDATE''' Anweisungen. Wenn während der Ausführung keine Fehler auftreten werden diese Änderungen durch den Befehl '''COMMIT TRAN''' bestätigt und in die Datenbank geschrieben. Sollten jedoch Fehler auftreten wird die Ausführung bei '''PROBLEM''' weitergeführt. Nachdem der Server dem Benutzer eine Meldung ausgegeben hat, werden alle Änderungen rückgängig gemacht.{br}
{br}
{TOP}
{br}
{br}
[anchor|#3]
==Geschachtelte Transaktionen==
Der SQL Server erlaubt es Ihnen mehrere Transaktionen zu schachteln. Das bedeutet eine neue Transaktion kann gestartet werden wenn die vorherige noch nicht abgeschlossen ist. Transact-SQL ermöglicht es Ihnen mehrere Transaktion zu schachteln, hierzu wird der Befehl '''BEGIN TRAN''' verwendet. Die automatische Variable '''TRANCOUNT''' kann ausgeführt werden um das Level der Schachtelung aufzuheben '''0''' keine Verschachtelung, '''1''' indiziert eine Verschachtelung ein Level tiefer, usw..{br}
{br}
Wird ein '''COMMIT''' Befehl gegen eine Transaktion außer die äußerste ausgeführt, werden keine Änderungen in die Datenbank geschrieben - es vermindert lediglich die automatische Variable '''TRANCOUNT'''. Ein '''ROLLBACK''' wiederum funktioniert unabhängig von dem Level in welchem es ausgeführt wird, es macht alles rückgängig unabhängig vom Verschachtelungslevel. Wenn ein verschachtelter '''COMMIT''' Befehl permanente Änderungen in die Datenbank geschrieben hat, kann ein äußerer '''ROLLBACK''' Befehl dies nicht mehr rückgängig machen.{br}
{br}
Wenn Sie explizit eine Transaktion starten erhöht sich der Zähler der automatischen Variable '''TRANCOUNT''' von '''0''' zu '''1''' wenn Sie mit einem '''COMMIT''' Befehl bestätigen, der Zähler verringert sich um '''1''' wenn Sie einen '''ROLLBACK''' Befehl ausführen. Wie Sie sehen ist das Verhalten von '''COMMIT''' und '''ROLLBACK''' nicht symmetrisch, wenn Sie Transaktionen verschachteln vermindert '''COMMIT''' das Verschachtelungslevel immer um '''1''' wie Sie in Abbildung 1 sehen können. Der '''ROLLBACK''' Befehl auf der anderen Seite widerruft die komplette Transaction (Abbildung 2). Die Ungleichmäßigkeit zwischen '''COMMIT''' und '''ROLLBACK''' ist der Schlüssel um Fehler in verschachtelten Transaktionen zu handhaben.{br}
{br}
[image||http://www.codeproject.com/KB/database/SQLServerTransactions/pic1.jpg]{br}
{br}
'''Abbildung 1:''' Ein '''COMMIT''' gleicht immer ein '''BEGIN TRANSACTION''' durch vermindern des Versachtelungszähler um '''1''' aus.{br}
{br}
[image||http://www.codeproject.com/KB/database/SQLServerTransactions/pic2.jpg]{br}
{br}
'''Abbildung 2:''' Ein einzelnes '''ROLLBACK''' löst die gesamte Transaktion auf.{br}
{br}
Wie Sie in Abbildung 1 und 2 sehen können, können Sie Transaktionen verschachteln und die automatische Variable '''TRANCOUNT''' benutzen um das Level zu bestimmen. Sie haben außerdem gelernt, dass sich '''COMMIT''' und '''ROLLBACK''' nicht symmetrisch verhalten:{br}
{br}
'''COMMIT''' vermindert nur das Level der '''TRANCOUNT''' Variable um 1.{br}
'''ROLLBACK''' setzt das Level der '''TRANCOUNT''' Variable auf 0.{br}
{br}
Dies hat die Auswirkung das eine Transaktion nie komplett ausgeführt wird bis das letzte '''COMMIT''' übergeben wird. Egal wie tief Ihre Verschachtelung ist, sie hat keine Auswirkung wenn das letzte '''COMMIT''' nicht übergeben wird.{br}
{br}
Hier ist ein Beispiel für eine verschachtelte Transaktion:{br}
{br}
<code sql>
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0

BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1

    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 2

        DELETE titleauthor
    COMMIT TRAN nested
                   -- Does nothing except decrement the value of @@TRANCOUNT


    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 1

ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0 
-- because ROLLBACK TRAN always rolls back all transactions and sets 
-- @@TRANCOUNT to 0.


SELECT TOP 5 au_id FROM titleauthor
</code>
{br}
In diesem Beispiel sehen wir, dass trotz des verschachtelten '''COMMIT TRAN''' der äußere '''ROLLBACK''' die Auswirkung der '''DELETE''' titleauthor rückgängig macht.{br}
{br}
Hier ist ein weiteres Beispiel einer verschachtelten Transaktion:{br}
{br}
<code sql>
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0

BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1

    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
               -- The value of @@TRANCOUNT is 2

        DELETE titleauthor
    ROLLBACK TRAN
  
    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 
    -- to 0.


IF (@@TRANCOUNT > 0) BEGIN
    COMMIT TRAN -- Never makes it here cause of the ROLLBACK

    SELECT 'After COMMIT TRAN', @@TRANCOUNT
END

SELECT TOP 5 au_id FROM titleauthor
</code>
{br}
In diesem Beispiel erreicht die Ausführung niemals den '''COMMIT TRAN''' auf Grund der '''ROLLBACK TRAN''', sie macht alle Transaktionen rückgängig die zur Zeit ausgeführt werden und setzt den '''TRANCOUNT''' wieder auf '''0'''. Sofern '''ROLLBACK TRAN''' ohne Sicherungspunkt ausgeführt wird, wird '''ROLLBACK TRAN''' alle Transaktionen rückgängig machen und den '''TRANCOUNT''' wieder auf '''0''' setzen. Egal in welchem Zusammenhang sie aufgerufen wird.{br}
{br}
{TOP}
{br}
{br}
[anchor|#4]
==Save TRAN und Save Points==
Savepoints bieten einen Vorgang um Teile einer Transaktion rückgängig zu machen. Der Benutzer kann einen Savepoint oder eine Markierung in einer Transaktion setzen. Der Savepoint definiert einen Bereich zu der die Transaktion zurückkehren kann, wenn ein Abschnitt der Transaktion bedingungsweise abgebrochen wird. Der SQL Server bietet Ihnen die Möglichkeit einen Savepoint mit Hilfe der '''SAVE TRAN''' zu setzen, welche den Wert des '''TRANCOUNT''' nicht beeinflusst. Ein Rückschritt zu einem Savepoint (nicht einer Transaktion) beeinflusst den Wert der '''TRANCOUNT''' ebenfalls nicht. Allerdings muss das '''ROLLBACK''' explizit den Namen des Savepoints beinhalten, sonst wird die komplett Transaktion rückgängig gemacht.{br}
{br}
Das folgende Skript zeigt wie man Savepoints benutzt:{br}
{br}
<code sql>USE pubs
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 0


BEGIN TRAN main
    SELECT 'After BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 1

    DELETE sales
    SAVE TRAN sales  -- Mark a save point

    SELECT 'After SAVE TRAN sales', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 1


    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2

        DELETE titleauthor
        SAVE TRAN titleauthor  -- Mark a save point

        SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT
        -- The value of @@TRANCOUNT is still 2

    ROLLBACK TRAN sales

    SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT
     -- The value of @@TRANCOUNT is still 2


    SELECT TOP 5 au_id FROM titleauthor

IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 

    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 

    -- to 0.

END
    
SELECT TOP 5 au_id FROM titleauthor</code>{br}
{TOP}
{br}
{br}
[anchor|#5]
==Error Handling==
Die hier gezeigten Beispiele sind spezifisch für Stored Procedures, da sie die gewünschte Methode sind um mit einer Datenbank zu interagieren. Wird ein Fehler in einer Stored Procedure gefunden, ist das Beste was Sie machen können die sequentielle Ausführung des Codes zu stoppen und{br}
{br}
* entweder in ein anderes Code Segment der Prozedur zu springen (abzweigen)
* oder die Kontrolle wieder an die aufrufende Applikation zurückgeben.
{br}
Die automatische Variable '''ERROR''' wird benutzt um das Error Handling auszuführen. Sie beinhaltet die Fehler ID die von der letzten ausgeführten SQL Anweisung des Benutzers. Wenn eine Anweisung erfolgreich ausgeführt wird ist der Wert der '''ERROR''' Variable '''0'''. Um festzulegen ob eine Anweisung erfolgreich ausgeführt wurde wird eine '''IF''' Anweisung direkt nach der Ausführung des Statements ausgeführt um den Wert der '''ERROR''' Variable zu prüfen. Es ist unerlässlich die '''ERROR''' Variable nach der Ausführung eines Statements zu prüfen, weil der Wert nach der erfolgreichen Ausführung des nächsten Statements wieder auf '''0''' gesetzt wird. Tritt ein abfangbarer Fehler auf hat '''ERROR''' einen Wert größer '''0'''. Der SQL Server setzt den Wert nach jedem erfolgreichen Befehl wieder auf '''0''', d.h. sie müssen den Fehlerwert sofort erfassen. Meistens testen Sie die '''ERROR''' Variable auf Änderungen nach jeder '''INSERT''', '''UPDATE''' oder '''DELETE''' Anweisung.{br}
{br}
<code sql>CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), 
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

COMMIT TRAN

RETURN 0</code>
{br}
Diese Art der Lösung birgt beträchtige Wiederholung, gerade dann wenn in Ihrer Unternehmenslogik die Implementierung von mehr als zwei Transact-SQL benötigt wird. Eine elegantere Lösung ist es den Code in eine typische Bearbeitungsprozedur zu gruppieren:{br}
{br}
<code sql>CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1</code>
{br}
{TOP}
CP Wiki

Navigation¶

SQL Server Transaction und Error Handling
