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.

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.

Categories

Design Rules

Additional Information
Example Test Script
SQL
 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