SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement

The topic describes the SA0137 analysis rule.

Message

BEGIN TRANSACTION statement is missing a following COMMIT statement

Description

The rule checks T-SQL code for BEGIN TRANSACTION statements, which are not followed by same number of COMMIT statements.

If an active explicit transaction is not committed or rolled back, it will stay open until the connection is closed and all data changes made since the start of the transaction will be discarded.

How to fix

Review the code and add COMMIT statement to the code branch where it is applicable.

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 OuterTran;  
 3
 4PRINT N'Transaction count after BEGIN OuterTran = '  
 5    + CAST(@@TRANCOUNT AS nvarchar(10));  
 6
 7INSERT INTO TestTran VALUES (1, 'aaa');  
 8
 9-- This statement sets @@TRANCOUNT to 2.  
10BEGIN TRANSACTION Inner1;  
11
12PRINT N'Transaction count after BEGIN Inner1 = '  
13    + CAST(@@TRANCOUNT AS nvarchar(10));  
14
15INSERT INTO TestTran VALUES (2, 'bbb');  
16
17-- This statement sets @@TRANCOUNT to 3.  
18BEGIN TRANSACTION Inner2;  
19
20PRINT N'Transaction count after BEGIN Inner2 = '  
21    + CAST(@@TRANCOUNT AS nvarchar(10));  
22
23INSERT INTO TestTran VALUES (3, 'ccc');  
24
25-- This statement decrements @@TRANCOUNT to 2.  
26-- Nothing is committed.  
27COMMIT TRANSACTION Inner2;  
28
29PRINT N'Transaction count after COMMIT Inner2 = '  
30    + CAST(@@TRANCOUNT AS nvarchar(10));  
31
32-- This statement decrements @@TRANCOUNT to 1.  
33-- Nothing is committed.  
34--COMMIT TRANSACTION Inner1;  
35
36PRINT N'Transaction count after COMMIT Inner1 = '  
37    + CAST(@@TRANCOUNT AS nvarchar(10));  
38
39-- This statement decrements @@TRANCOUNT to 0 and  
40-- commits outer transaction OuterTran.  
41--COMMIT TRANSACTION OuterTran;  
42
43PRINT N'Transaction count after COMMIT OuterTran = '  
44    + CAST(@@TRANCOUNT AS nvarchar(10));
Analysis Results
  Message Line Column
1 SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement. 2 0
2 SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement. 10 0
See Also

Other Resources

Analysis Rules