SQL Server 2008-Transaktion, Rollback erforderlich?

Question

Ich habe eine gespeicherte Prozedur, die eine BEGIN TRANSACTION y COMMIT TRANSACTION Erklärung. Innerhalb der Transaktion ist eine select-Abfrage WITH(XLOCK, ROWLOCK) .

Die Transaktion kann möglicherweise aufgrund einiger Berechnungen fehlschlagen, die einen arithmetischen Überlauffehler verursachen, wenn Werte außerhalb des zulässigen Bereichs geliefert werden. Dieser Fehler würde vor jeder Einfüge-/Aktualisierungsanweisung auftreten.

Meine Frage ist, sollte ich die Transaktion in ein TRY/CATCH und Rollback verpacken oder ist dies nicht wirklich erforderlich und alle Sperren würden automatisch freigegeben werden, wenn die Transaktion fehlschlägt? Meine einzige Sorge dabei ist, dass SQL nicht alle Sperren der Transaktion freigeben würde, wenn die Transaktion fehlschlägt.

Danke,

Tom

Answer 1

Eine viel einfachere Möglichkeit ist:

set xact_abort on

Dies führt dazu, dass die Transaktion automatisch zurückgesetzt wird, wenn ein Fehler auftritt.

Beispiel-Code:

set xact_abort on
begin transaction
select 1/0
go
print @@trancount -- Prints 0

set xact_abort off
begin transaction
select 1/0
go
print @@trancount -- Prints 1

Wenn Sie das zweite Segment mehrmals ausführen, erhöht sich die Anzahl der Transaktionen auf 2, 3, 4 usw. Ein einmaliger Durchlauf des ersten Segments setzt alle Transaktionen zurück.

Answer 2

Kurze Antwort: Ja.

Immer wenn ich BEGIN TRANSACTION verwende, muss ich immer beinhalten die Verwendung von Fehlerbehandlung und ROLLBACK. Die Folgen einer unvorhergesehenen (und/oder unvorhersehbaren - Sie können nicht wissen, wie Ihr Code in der Zukunft geändert werden muss) Situation, die eine offene Transaktion auf einem Produktionsserver hinterlässt, sind zu schwerwiegend, um es nicht zu tun.

In SQL Server 2000 und früher müssen Sie die @@Error-Logik verwenden. In SQL 2005 und höher können Sie die (weit überlegene) TRY...CATCH... Syntax verwenden.

Answer 3

Brads Ansatz gefällt mir, aber er musste ein wenig bereinigt werden, damit man den Fehler, der das Problem verursacht hat, erkennen kann.

begin try
    begin transaction;

    ...

    commit transaction;
end try
begin catch
    declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    rollback transaction;
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

Answer 4

TRY/CATCH ist nicht erforderlich um Sperren zu lösen. Ich denke jedoch, dass die folgende Vorlage für die meisten Transaktionen gut geeignet ist.

BEGIN TRY
    BEGIN TRAN
    ...
    IF (@@error <> 0)
       ROLLBACK TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH
--BEGIN FINALLY (doesnt exist, which is why I commented it out)    
    IF (@@trancount > 0)
       COMMIT TRAN
--END FINALLY

Answer 5

begin transaction; -- you don't want to hit catch block if begin transaction will fail
begin try

     ... updates/inserts/selects ...

   commit transaction; -- the last statement in try code block is always commit
end try
begin catch
   rollback transaction; -- first step before other error handling code is rollback  transaction
   declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
   select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)),  @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
   raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch