Click or drag to resize

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.

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 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
 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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0130

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.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

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

 MessageLineColumn
1SA0130 : Explicit error handling for statements between BEGIN TRAN and COMMIT/ROLLBACK TRAN is required.80
See Also

Other Resources