Sql try/catch rollback/commit - preventing erroneous commit after rollback

Underpinning transaction integrity: @@TRANCOUNT

When dealing with nested transactions or transactions in stored procedures that might be nested within other transaction contexts, maintaining the integrity of the entire transaction across boundaries is key. This is where @@TRANCOUNT makes a star appearance.

Guarding data consistency

Utilizing @@TRANCOUNT allows you to prevent committing a transaction that might have previously been rolled back. Always check @@TRANCOUNT before performing a commit or rollback:

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;


Nested transactions: Layer by Layer

Managing nested transactions? Track the transaction count on each level and only rollback if you're on Level 1. This way, you dodge disrupting the integrity of the whole transaction chain:

IF @@TRANCOUNT = 1
    ROLLBACK TRANSACTION;
ELSE
    SAVE TRANSACTION MySavePoint;


Use what you've got: THROW and RAISERROR

Quality error handling equates to transparency and proper client-side notification. The THROW statement in SQL Server 2012 and onwards lets you rethrow a caught error:

THROW;  -- This error isn't going to handle itself


For custom error messages or maintaining compatibility with older SQL Server versions, fall back on RAISERROR:

RAISERROR ('This is a custom error message', 16, 1);


1. Custom messages: Safe to say, more context equals happier developers.
2. Error logging: Combine RAISERROR with your error logging procedures.
3. Severity levels: Customize severity levels to mirror error criticality.

Demystifying Errors: Diagnostics

Understanding the specifics of a failed transaction can significantly aid error diagnosis. Make use of variables like @ErrMsg to store error details:

DECLARE @ErrMsg NVARCHAR(2048);
SET @ErrMsg = ERROR_MESSAGE();
-- @ErrMsg is ready for further usage. Give it a job!


Error handling best practices

1. Store Details: Capture error messages, numbers, procedures, and line numbers.
2. Centralize logging: Funnel error specifics to a central error log table.
3. Alerts: Set up notifications informed by the error details.

Tread lightly with NOLOCK

While using the NOLOCK hint with your SELECT statements prevents blocking, it opens the door for dirty reads. Use this hint wisely, understanding its potential risks:

SELECT * FROM MyTable WITH (NOLOCK);


NOLOCK: Points to ponder

1. Data consistency: Data accuracy may take a hit.
2. Concurrency: Is read speed more important than data accuracy in your context?
3. Testing: Think twice and again before implementing in production.

Deadlock: Not as dreadful as it sounds!

Deadlocks can pose a challenge to transaction management. Equip yourself by setting deadlock priority and altering the isolation level if necessary:

SET DEADLOCK_PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


Anti-deadlock strategies

1. Indexing: Proper indexing reduces resource holding time.
2. Ordering: Follow consistent order of resource access across transactions.
3. Transaction duration: The shorter, the better.