XACT_ABORT OFF ACID incompatible?

Question

I'm new here so please be gentle! :) Ok, so I've got into the SQL Server community/world for about 2 years now (SQL Server Dev and DBA - 2005+ versions) and recently I have also discovered the ACID theory and I was just wondering how the hell is SQL Server ACID compatible? As a default it always comes with the XACT_ABORT OFF option, right? And here's an example from MSDN :

    IF OBJECT_ID(N't2', N'U') IS NOT NULL
        DROP TABLE t2;
    GO
    IF OBJECT_ID(N't1', N'U') IS NOT NULL
        DROP TABLE t1;
    GO
    CREATE TABLE t1
        (a INT NOT NULL PRIMARY KEY);
    CREATE TABLE t2
        (a INT NOT NULL REFERENCES t1(a));
    GO
    INSERT INTO t1 VALUES (1);
    INSERT INTO t1 VALUES (3);
    INSERT INTO t1 VALUES (4);
    INSERT INTO t1 VALUES (6);
    GO
    SET XACT_ABORT OFF;
    GO
    BEGIN TRANSACTION;
    INSERT INTO t2 VALUES (1);
    INSERT INTO t2 VALUES (2); -- Foreign key error.
    INSERT INTO t2 VALUES (3);
    COMMIT TRANSACTION;
    GO

select * from t2;

And the result set:

a
1
3

Where is the atomicity in this case? Did I got the whole ACID theory wrong perhaps?

P.S.:The reason I ask this is not just because of the ACID property but also because I have struggled many times in the past with this XACT_ABORT option and especially when it gets combined with CLR code from an app via ODBCs connections- but off topic :)

It depends what you were expecting. If you want transactions to abort on error `SET XACT_ABORT ON;`. The important thing is, you never get fields half written too. Every single statement is ACID. The `XACT_ABORT` setting does not alter the hidden workings of the engine, and therefore we don't even have to consider all the potential inconsistencies that are avoided on our behalf. — Jodrell, Apr 25 '13 at 11:20
Actually I was more curious if the ACID theory is compatible with SQL Server in the above case. Did I got it wrong, the whole Atomicity thing? From what I get, saying "All or nothing", it kind of implies that if a transaction fails everything in it should be rolled back, which in the above case, obviously, it doesn't. And what I wanted to stress out is that the XACT_ABORT comes by default as OFF. — Sergiu, Apr 25 '13 at 11:22
You are right, with the default setting of OFF the TRANSACTION statement is not atomic. It does however allow you to catch and handle but this is imperfect http://stackoverflow.com/a/919279/659190 — Jodrell, Apr 25 '13 at 11:25
Thank you Jodrell! That's all I wanted to know, it means that I did understood what atomicity is really about and also discovered that the default options of SQL Server are not 100% ACID compatible. =D — Sergiu, Apr 25 '13 at 11:28

score 2 · Accepted Answer · answered Apr 25 '13 at 11:16

2

Yep, you are right. When used like that, with XACT_ABORT OFF and without TRY/CATCH blocks, transactions in SQL Server are pretty much useless.

You should always use XACT_ABORT_ON and TRY/CATCH blocks to make your transactions safe.

In this case TRY/CATCH could work even with XACT_ABORT OFF:

    SET XACT_ABORT OFF;
    GO
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT INTO t2 VALUES (1);
        INSERT INTO t2 VALUES (2); -- Foreign key error.
        INSERT INTO t2 VALUES (3);
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK;
    END CATCH  
    GO

answered Apr 25 '13 at 11:16

Nenad Zivkovic

18,221
6
42
55

Yep...true! But I was more curious about how ACID comes into the default settings of SQL Server because normally, defaulted, it should have been XACT_ABORT ON in order to comply to the atomicity concept, right? – Sergiu Apr 25 '13 at 11:24
@Sergiu Exactly, Jordell's link in other comment explains perfectly. If XACT_ABORT is OFF it's up to you to chose whatever it's a good transaction or bad. Sometimes the errors are small or irrelevant and you can still decide to go and commit whatever is done. If you wan't full ACID, you must turn it ON. As for why have Microsoft chosen to make OFF default - that's a whole different question:) – Nenad Zivkovic Apr 25 '13 at 12:03

XACT_ABORT OFF ACID incompatible?

1 Answers1