SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement

Ensure that ROLLBACK TRANSACTION is always preceded by a BEGIN TRANSACTION to avoid runtime errors related to transaction control flow.

Description

The proper use of BEGIN TRANSACTION and ROLLBACK TRANSACTION is crucial for managing transaction control flow in SQL Server. A common issue arises when a ROLLBACK TRANSACTION is executed without a preceding BEGIN TRANSACTION, leading to an error because @@TRANCOUNT is zero.

For example:

SQL
1-- Problematic query example
2ROLLBACK TRANSACTION;

This statement will cause a runtime error if there isn’t an active transaction when it’s executed. Such errors disrupt normal execution flow, often leading to application-level issues.

  • Causes runtime errors when transaction count is zero.

  • Interrupts the logical unit of work if improperly handled.

How to fix

Ensure proper match between ROLLBACK and BEGIN TRANSACTION statements to prevent runtime errors.

Follow these steps to address the issue:

  1. Identify all ROLLBACK TRANSACTION statements in your SQL code.

  2. Verify that each ROLLBACK TRANSACTION statement is preceded by a corresponding BEGIN TRANSACTION. This ensures there is an active transaction when the rollback is executed.

  3. If a ROLLBACK TRANSACTION lacks a preceding BEGIN TRANSACTION, remove it or modify the logic to include initiating a transaction appropriately.

  4. Test the corrected SQL statements to ensure there are no runtime errors due to mismatched transaction control statements.

For example:

SQL
1-- Corrected query example
2BEGIN TRANSACTION;
3-- ... SQL operations ...
4ROLLBACK TRANSACTION;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
 1ROLLBACK
 2
 3BEGIN TRANSACTION
 4
 5ROLLBACK
 6
 7COMMIT 
 8
 9ROLLBACK
10
11BEGIN TRANSACTION
12ROLLBACK
13
14ROLLBACK

Analysis Results
  Message Line Column
1 SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement. 1 0
2 SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement. 9 0
3 SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement. 14 0
See Also

Other Resources