How to rollback or commit a transaction in SQL Server

Employing TRY…CATCH for error handling

When running transactions, wrapping your SQL statements in BEGIN TRY and END TRY with a subsequent BEGIN CATCH and END CATCH provides an error safety net, enabling transaction rollback upon encountering error.

BEGIN TRANSACTION;

BEGIN TRY
    -- Insert your SQL Code here, no pressure...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION; -- Oopsie, let's pretend this never happened
    -- Place your error handling operations here
END CATCH;


In above structure, any error surfacing within the TRY block hands over control to the CATCH block, where the transaction can be safely rolled back. By checking @@TRANCOUNT, we can ensure a ROLLBACK is only attempted if a transaction is active.

Keep transactions brief

Aim to keep transactions brief for targeting optimal performance and avoiding needless locks. Construct your SQL outside of the BEGIN TRANSACTION and COMMIT/ROLLBACK pattern to minimize lock holding duration.

Transaction demystified: scopes and batches

In SQL Server, transactions can expand across multiple batches, which offers more flexibility but demands careful management. If a transaction is neither committed nor rolled back within the same batch, it remains active potentially blocking resources, leading to performance degradation or deadlocks.

Always verify active transactions presence using @@TRANCOUNT before committing or rolling back in a different batch.

Nailing robust transactions: best practices

Techniques for error handling

Achieving data integrity demands robust exception handling, especially when a statement within a transaction fails. Transactions should either complete in their entirety or not all, preventing incomplete updates potentially compromising your database state.

Consistency across Stored Procedures

When encapsulating database logic within stored procedures, ensure to use transactions consistently across all the procedure calls. This helps creating a predictable behavior easing error handling process.

Savepoints: rollback rescue

In complex transaction sequences, savepoints allow for partial rollbacks within a transaction, thus offering more control:

SAVE TRANSACTION SavePointName;
-- Some SQL code that could potentially trip over a cable
ROLLBACK TRANSACTION SavePointName; -- This rolls back to the savepoint
COMMIT TRANSACTION; -- This is where all changes become permanent


Transactions decoded

SET XACT_ABORT: why and when?

Enabling SET XACT_ABORT ON simplifies the error handling process by automatically rolling back the transaction upon encountering a run-time error:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
-- Your Transact-SQL Code goes here, trust me you got this!
COMMIT TRANSACTION;


With this configuration, any error within the transaction is enough to trigger a complete rollback eliminating the need for explicit error handling code.

Deadlocks: prevention

With transactions in operation, deadlocks can occur if resources are accessed by multiple transactions concurrently. Proactively detecting and handling deadlocks within your Transact-SQL code can prevent these issues from escalating into manual intervention needs.