Error handling in Transaction

Error handling in SQL Server transactions is implemented using TRY...CATCH blocks to ensure transactional consistency. The goal is to guarantee that either all operations succeed and are committed, or all changes are rolled back if an error occurs. In production systems, the recommended pattern is to start a transaction inside a TRY block, commit on success, and use XACT_STATE() or @@TRANCOUNT inside the CATCH block to determine whether a rollback is required. XACT_STATE() is particularly important because it identifies whether the transaction is committable, absent, or doomed. Modern SQL Server development recommends using THROW instead of RAISERROR because it preserves the original error details. Proper transaction error handling prevents data corruption, blocking, open transactions, and transaction log growth, making it a critical component of enterprise database applications.

Why we need for error handling in transaction

Error handling in transactions is required to maintain data consistency, data integrity, and atomicity. If an error occurs during a transaction and it is not handled properly, some statements may succeed while others fail, leaving the database in an inconsistent state. Error handling allows us to detect the error, rollback the transaction, log the issue, and return a meaningful message to the application.

Syntex of try catch

BEGIN TRY     BEGIN TRAN;      -- SQL Statements      COMMIT TRAN; END TRY BEGIN CATCH     IF @@TRANCOUNT > 0         ROLLBACK TRAN;     THROW; END CATCH;

Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE Employee (     EmployeeID INT PRIMARY KEY,     EmployeeName VARCHAR(50) ); GO INSERT INTO Employee VALUES (1,'Bagesh');

Table created successfully.

Now I am going to insert few records

BEGIN TRY     BEGIN TRAN;     INSERT INTO Employee VALUES (2,'Rohan');     INSERT INTO Employee VALUES (1,'Duplicate Key');       COMMIT TRAN; END TRY BEGIN CATCH     IF @@TRANCOUNT > 0         ROLLBACK TRAN;     THROW; END CATCH;

Here 1 record is good to insert but another one has issue it has duplicate id . in this table id is PK. In try block our code break and trans is not committed. In the catch block it is rollbacked.

If we are not using try catch then if any error occurred during the transaction our transaction is opened and it will block the resources.

Ø  Blocking

Ø  Locking

Ø  Open Transactions

Ø  Log Growth

Best Practices

Ø  Always use TRY...CATCH with transactions

Ø  Use XACT_STATE() in CATCH block

Ø  Rollback on failure

Ø  Use THROW instead of RAISERROR for new development

Ø  Log errors into an audit table

Ø  Keep transactions as short as possible

Ø  Never leave transactions open