SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement

Introduction

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:

-- Example of a transaction with missing COMMIT
BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary * 1.10 WHERE DepartmentID = 1;
-- 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:

-- Example of corrected transaction
BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary * 1.10 WHERE DepartmentID = 1;
-- Correctly complete the transaction
COMMIT;

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.

Additional Information

BEGIN TRANSACTION

COMMIT TRANSACTION

Example Test SQL

-- This statement sets @@TRANCOUNT to 1.
BEGIN TRANSACTION tran1;

-- This statement sets @@TRANCOUNT to 2.
BEGIN TRANSACTION tran2;

INSERT INTO table1 VALUES (2, 'bbb');

-- This statement sets @@TRANCOUNT to 3.
BEGIN TRANSACTION tran3;

 -- This statement sets @@TRANCOUNT to 2.
COMMIT TRANSACTION tran3;

--COMMIT TRANSACTION tran2;
--COMMIT TRANSACTION tran1;

PRINT 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

Analysis Rules