Sql Server - transactions roll back on error?

Switch on SET XACT_ABORT for security

SET XACT_ABORT, when turned ON, offers automatic rollback for transactions if a Transact-SQL space-time error rift occurs.Default value is OFF, but trust in the force and toggle it ON when running intricate transactions or dealing with verbose SQL command strings.

SET XACT_ABORT ON;
BEGIN TRANSACTION;
-- Trying to fetch the secrets of the universe? You'll need SQL.
COMMIT TRANSACTION;


Leveraging SET XACT_ABORT provides consistent transaction behavior across diverse programming languages, adding to transaction safety. Necessary bolt-on even when running commands from client apps – highly relevant for SQL Server 2005 and upwards.

TRY..CATCH block: Your shield against transactional anomalies

Transaction control with TRY-CATCH

A structured way to manage transactions and handle errors is by encapsulating your SQL commands within TRY...CATCH blocks.

BEGIN TRY
    BEGIN TRANSACTION;
    -- Your SQL command and not a magic spell, promise!
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    -- Error info not found in your fortune cookie. Fetching...
END CATCH


In an error zone, the control shifts to the CATCH block, where you decide whether a transaction is active and if a rollback is required.

Efficient error management

During an error situation, use @@TRANCOUNT to infer if a transaction is underway and raise a red flag using the RAISERROR command to signal error onset:

IF @@TRANCOUNT > 0 BEGIN
    PRINT 'Rolling back transaction...not unlike time travel'
    ROLLBACK;
END;

RAISERROR ('Black hole spotted! Error occurred', 16, 1);


This approach fortifies safe error handling, providing an assurance against incomplete transactions.

Capturing the error's life story

Within the CATCH block, declare variables to capture the nuances of an error.

DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;

SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

-- Secrets uncovered, ready to log the error's biography


Having a good error log contributes to forensics and debuggery, helping you nail down the culprit command.

Transaction behavior: Expect the unexpected

Consistency: Aiming for high five!

Client interactions with SQL Server transactions can be a salad of confusion. So, aim for transaction behavior consistency across multiple programming languages.

Manual Rollback: Precision tool for error control

Yes, SET XACT_ABORT ON offers automatic transaction rollback. But, there are times when you may want the control in your hands. Like when executing a verbose SQL command sequence, a routine check-up of results at specific checkpoints before deciding the transaction's fate could provide enhanced control.

-- SQL command taking longer than your coffee break? Anomaly alert.


This manual rollback approach proves its mettle when you might have to call off the transaction during a long SQL command journey.

References