SA0138 : BEGIN TRANSACTION statement without ROLLBACK statement

In SQL Server, transactions are used to ensure data integrity. A BEGIN TRANSACTION statement starts a transaction, but if an error occurs and there is no ROLLBACK statement, changes may not be reverted, leading to inconsistent or corrupted data.

For example:

1-- Example of problematic transaction handling
2BEGIN TRANSACTION;
3-- Some operations
4-- No rollback in case of errors
5COMMIT TRANSACTION;

This example is problematic because it lacks a ROLLBACK statement to undo changes if an error occurs during the transaction. This can lead to:

• Data inconsistencies since there is no mechanism to reverse partial changes.

• Increased difficulty in diagnosing and fixing errors during transaction execution.

This fix addresses missing ROLLBACK statements following a BEGIN TRANSACTION. Implementing ROLLBACK ensures proper error handling and data integrity in SQL Server.

Follow these steps to address the issue:

1. Review your transaction logic where BEGIN TRANSACTION is used and identify branches of code where errors may occur.

2. Add a ROLLBACK statement in each error-handling branch to revert changes if an error happens during the transaction. This ensures that the transaction can be safely undone.

3. Ensure that each transaction includes both COMMIT and ROLLBACK options as demonstrated in the examples below.

For example:

 1-- Example of corrected transaction handling
 2BEGIN TRANSACTION;
 3BEGIN TRY
 4    -- Some operations
 5    COMMIT TRANSACTION;
 6END TRY
 7BEGIN CATCH
 8    ROLLBACK TRANSACTION;
 9    -- Handle the error, log it, etc.
10END CATCH;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules

BEGIN TRANSACTION

ROLLBACK TRANSACTION

General Pattern for Error Handling

Other Resources