Pozor na transakce – rollback není vždy automatický!

Nezkušeného vývojáře, ale jak jsem se bohužel na vlastní oči mnohdy přesvědčil – mnohdy i velmi zkušeného vývojáře, dokáže zdrcujícím způsobem překvapit výsledek následujícího jednoduchého příkladu:

CREATE TABLE TransactionTestTable
(
    TransactionTestID int IDENTITY PRIMARY KEY,
    TransactionText nvarchar(100)
)
GO

CREATE PROCEDURE TransactionTest
AS
    BEGIN TRANSACTION
        
        -- Korektní SQL statement uvnitř transakce
        INSERT INTO TransactionTestTable(TransactionText) VALUES('První insert')

        -- Simulace chyby uvnitř transakce (do identity-column nelze zapisovat)
        INSERT INTO TransactionTestTable(TransactionTestID, TransactionText) VALUES(1, 'Druhý insert')

        -- Korektní SQL statement uvnitř transakce, po chybě
        INSERT INTO TransactionTestTable(TransactionText) VALUES('Třetí insert')

    COMMIT TRANSACTION
GO

EXEC TransactionTest
SELECT * FROM TransactionTestTable
GO

…spuštěná transakce (zde pouze pro formu zabalená do procedury TransactionTest) selže – druhý INSERT zfailuje, protože nemůže zapisovat přímo do identity-column. To zde není podstatné, je to pouze simulace pro zfailování statementu uvnitř transakce.

Velkým překvapením mnohých však je, že v tabulce TransactionTestTable budou po skončení transakce následující data:

TransactionTestID	TransactionText
1	První insert
2	Třetí insert

Zfailováním druhého statementu nedojde k automatickému rollbacku celé transakce!
Run-time chyby statementů rollbackují automaticky pouze statement, který chybu způsobil!

Pokud chceme, aby run-time chyba automaticky zrollbackovala celou transakci, musíme nastavit SET XACT_ABORT ON:

CREATE PROCEDURE TransactionTest
AS
    SET XACT_ABORT ON
    BEGIN TRANSACTION
        
        -- Korektní SQL statement uvnitř transakce
        INSERT INTO TransactionTestTable(TransactionName) VALUES('První insert')

        -- Chybný SQL statement uvnitř transakce, do identity-column nelze zapisovat
        INSERT INTO TransactionTestTable(TransactionTestID, TransactionName) VALUES(1, 'Druhý insert')

        -- Korektní SQL statement uvnitř transakce, po chybě
        INSERT INTO TransactionTestTable(TransactionName) VALUES('Třetí insert')

    COMMIT TRANSACTION
GO

Jemněji než automatický rollback pomocí SET XACT_ABORT ON lze v praxi použít ošetření chyb pomocí TRY..CATCH (i když i to má svá úskalí):

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
   -- Generate a constraint violation error.
   DELETE FROM Production.Product
      WHERE ProductID = 980;
END TRY
BEGIN CATCH
   SELECT 
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() as ErrorState,
      ERROR_PROCEDURE() as ErrorProcedure,
      ERROR_LINE() as ErrorLine,
      ERROR_MESSAGE() as ErrorMessage;

   IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
   COMMIT TRANSACTION;
GO

…případně „postaru“ testovat @@ERROR <> 0 po každém statementu.