SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement

Unmatched BEGIN TRANSACTION and COMMIT statements can result in uncommitted transactions, leading to data loss and performance problems.

Description

This problem arises when a transaction in SQL Server is started with a BEGIN TRANSACTION but is not properly ended with an equal number of COMMIT or ROLLBACK statements. Such oversight can lead to uncommitted transactions, causing potential data loss and application performance issues.

For example:

SQL
Copy
1-- Example of a transaction with missing COMMIT
2BEGIN TRANSACTION;
3UPDATE Employees SET Salary = Salary * 1.10 WHERE DepartmentID = 1;
4-- Missing COMMIT or ROLLBACK

In the above example, the transaction is initiated but not completed. This omission can lead to:

  • Open transactions that lock resources unnecessarily, impacting database performance and concurrency.

  • Potential data loss if the transaction is implicitly rolled back when the connection closes.

How to fix

Ensure that every BEGIN TRANSACTION statement is properly concluded with a corresponding COMMIT or ROLLBACK statement to prevent data integrity issues and performance degradation.

Follow these steps to address the issue:

  1. Identify all transactions starting with BEGIN TRANSACTION by reviewing your T-SQL code.

  2. Ensure that each transaction has a corresponding COMMIT or ROLLBACK statement to end the transaction properly.

  3. Insert the COMMIT statement at applicable code branches where the transaction logic is complete and should be persisted.

  4. Consider adding ROLLBACK statements in your error handling logic to gracefully handle exceptions and revert changes when necessary.

For example:

SQL
Copy
1-- Example of corrected transaction
2BEGIN TRANSACTION;
3UPDATE Employees SET Salary = Salary * 1.10 WHERE DepartmentID = 1;
4-- Correctly complete the transaction
5COMMIT;

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
20 minutes per issue.
Categories

Design Rules

Additional Information
Example Test SQL
SQL
Copy
 1-- This statement sets @@TRANCOUNT to 1.  
 2BEGIN TRANSACTION tran1;  
 3
 4-- This statement sets @@TRANCOUNT to 2.  
 5BEGIN TRANSACTION tran2;  
 6
 7INSERT INTO table1 VALUES (2, 'bbb');  
 8
 9-- This statement sets @@TRANCOUNT to 3.  
10BEGIN TRANSACTION tran3;
11
12 -- This statement sets @@TRANCOUNT to 2.  
13COMMIT TRANSACTION tran3;  
14
15--COMMIT TRANSACTION tran2;  
16--COMMIT TRANSACTION tran1;  
17
18PRINT N'@@TRANCOUNT is  ' + CAST(@@TRANCOUNT AS nvarchar(10));
Analysis Results
  Message Line Column
1 SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement. 3 0
2 SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement. 6 0
See Also

Other Resources

Analysis Rules