SA0130 : Explicit error handling for statements between BEGIN TRAN and COMMIT/ROLLBACK TRAN is required

The topic describes the SA0130 analysis rule.

Message

Explicit error handling for statements between BEGIN TRAN and COMMIT/ROLLBACK TRAN is required

Description

The rule checks for BEGIN TRAN – COMMIT/ROLLBACK TRAN statements which do not have handling of transaction errors.

How to fix

To ensure the transaction errors are properly handled, you can do one of the following:

  1. Encapsulate all statements between the BEGIN and COMMIT/ROLLBACK statements in a TRY..CATCH block.

  2. Set XACT_ABORT ON before the BEGIN TRAN statement

If the transaction is spread across more than one batch – check the @@TRANCOUNT is > 0 before executing each of the statements, in order to ensure that there will be no data modified outside the already completed transaction.

For example:

SQL
Copy
 1SET XACT_ABORT ON
 2GO
 3BEGIN TRAN
 4GO
 5IF @@trancount >0 INSERT INTO Table_5 (testkey) SELECT 2
 6GO
 7IF @@trancount >0 INSERT INTO Table_5 (testkey) SELECT 3
 8GO
 9IF @@trancount >0 INSERT INTO Table_5 (testkey) SELECT 4
10GO
11IF @@trancount >0 INSERT INTO Table_5 (testkey) SELECT 4
12GO
13IF @@trancount >0 
14    COMMIT TRAN

This check is important, because, by default, SQL Server will not rollback all the previous changes within a transaction if a particular statement fails and setting XACT_ABORT is not ON.

Scope

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

Parameters
Name Description Default Value
ConsiderXactAbortSetting

When the XACT_ABORT setting is set to ON before the checked statements, the statements are ignored.

yes
ReportFirstStatementOnly

If set to ‘yes’, a warning message for only the first DML statement in the transaction scope will be generated.

yes
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE [dbo].[Table_5] ( [testkey] [int] NOT NULL PRIMARY KEY)
 2
 3
 4-- this example inserts 3 rows even though there is an error
 5-- in the transaction because of the primary key.  
 6begin tran
 7
 8insert into Table_5 (testkey) select 2
 9insert into Table_5 (testkey) select 3
10insert into Table_5 (testkey) select 4
11insert into Table_5 (testkey) select 4
12if @@trancount = 1 
13commit tran
14
15-- This example does not insert any rows because of
16-- the SET XACT_ABORT ON
17SET XACT_ABORT ON
18
19begin tran
20
21insert into Table_5 (testkey) select 2
22insert into Table_5 (testkey) select 3
23insert into Table_5 (testkey) select 4
24insert into Table_5 (testkey) select 4
25if @@trancount = 1 
26commit tran
27
28SET XACT_ABORT OFF
29-- This example does not insert any rows because of
30-- the explicit rollback in the catch block.
31begin try
32        begin tran
33
34        insert into Table_5 (testkey) select 2
35        insert into Table_5 (testkey) select 3
36        insert into Table_5 (testkey) select 4
37        insert into Table_5 (testkey) select 4
38
39        commit tran
40end try
41begin catch
42        rollback tran
43        RAISERROR('error', 1, 1) 
44end catch
45
46if @@trancount = 1 
47commit tran
48
49-- This example will not insert any rows because of
50-- the the transaction is rolled back
51
52begin tran
53
54if @@trancount >0 insert into Table_5 (testkey) select 2
55if @@trancount >0 insert into Table_5 (testkey) select 3
56if @@trancount >0 insert into Table_5 (testkey) select 4
57if @@trancount >0 insert into Table_5 (testkey) select 4
58
59if @@trancount >0 
60commit tran
Analysis Results
  Message Line Column
1 SA0130 : Explicit error handling for statements between BEGIN TRAN and COMMIT/ROLLBACK TRAN is required. 8 0
See Also

Other Resources

Analysis Rules