T-SQL Development debate, which is the correct approach

Question

The company I work at uses stored procedures (and a home-grown ORM) exclusively for all database interaction, selects, updates, deletes.

We have numerous procs that will insert and/or update and/or delete from tables.

When it's required to "transactionalize" an entire proc, then we obviously needed to have our commit and rollback statements.

Here's where the question comes.

When there are multiple statements in a transaction that either need to be committed or rolled-back together, then we obviously throw a try/catch block around everything.

Let's assume this example scenario for a script:

set nocount on

create table #test
(
    id int primary key identity(1,1),
    col varchar(2) not null,
    y int not null
)

begin tran

begin try
    insert into #test ( col, y ) values ('GA', 4)
    update #test set col = 'DO', y = y / 0
    insert into #test ( col, y ) values ('aa', 7)
    commit
end try
begin catch
    select error_message()
    rollback
end catch

select * from #test t

drop table #test

Now, this is the way that I would write the script.

Let's assume that the update statement (the 2nd statement) causes an exception. That should then move the focus to the catch block, and thus executing the rollback command undoing all of the work that was done in the insert.

THEY say that in order for the rollback to actually rollback ALL statements instead of the one that caused the exception is to write the script like this:

set nocount on
set xact_abort on ------------------------------------------------

create table #test
(
    id int primary key identity(1,1),
    col varchar(2) not null,
    y int not null
)

begin tran

begin try
    insert into #test ( col, y ) values ('GA', 4)
    update #test set col = 'DO', y = y / 0
    insert into #test ( col, y ) values ('aa', 7)
    commit
end try
begin catch
    select error_message()
    if (xact_state() <> 0) -------------------------------------------------
        rollback
end catch

select * from #test t

drop table #test

Now, when I test both of these examples, they both get the same desired result (no records in the table).

What is the point of doing the xact_abort and xact_state stuff when just wrapping multiple statements in a try/catch accomplishes the same thing (rolling back multiple statements when there's an exception)?

I don't see `xact_abort` referenced anywhere, and I don't see the code for `xact_state()` Am I missing something? — Robert Harvey, Sep 02 '16 at 19:16
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. **OFF is the default setting.** https://msdn.microsoft.com/en-us/library/ms188792.aspx — Robert Harvey, Sep 02 '16 at 19:19
Are you sure xact_abort isn't on by default? You may want to do your test with setting it explicitly to off. — JeffO, Sep 02 '16 at 19:20
@RobertHarvey even though ALL of the statements are wrapped in a try/catch block? — ganders, Sep 02 '16 at 19:20
What does that have to do with anything? The documentation clearly states that the behavior *may* be different if SET XACT_ABORT is OFF. By inference, your behavior *may* also be the same. — Robert Harvey, Sep 02 '16 at 19:21
XACT_STATE() just checks to see if an active transaction is in progress. https://msdn.microsoft.com/en-us/library/ms189797.aspx — Robert Harvey, Sep 02 '16 at 19:22

paparazzo · Accepted Answer · 2016-10-25T15:34:30.883

This would be better on dba.stackexchange.com

XACT_STATE() does not effect the behavior of the rollback. It is about state.

1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
0 There is no active user transaction for the current request.
-1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

SET XACT_ABORT

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

You have a TRY CATCH. An error/exception will cause a catch and the catch will call the ROLLBACK. XACT_ABORT does not effect an explicit ROLLBACK.

See the sample in the link. The second should rollback even without a try catch.

On the first I bet it will all rollback even with set xact_abort off.

T-SQL Development debate, which is the correct approach

1 Answers1