Importance of Understanding SQL Transactions

Yesterday we encountered a disaster where a lot of records were gone.  After digging for a while the root cause was something like this:

 BEGIN TRAN
--
DELETE --records in table
--
INSERT INTO Livetable
   --values
--SELECT    from table
--
COMMIT TRAN

Basically the insert into from a select failed, but the delete passed.  Whoever wrote that code did not understand that by default transactions will only rollback the statement that generated the error unless it is above a certain error level.

A useful post is from here:

http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/

The author shows example sql:

 CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO
BEGIN TRANSACTION
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
-- Fails. Cannot insert null into a non-null column
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- fails. Duplicate key
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
COMMIT TRANSACTION
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

When run this code generates the following messages and results

Messages:

(1 row(s) affected)
Msg 515, Level 16, State 2, Line 6
Cannot insert the value NULL into column 'ID', table 'OdeToFood.Models.OdeToFoodDb.dbo.TestingTransactionRollbacks'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 12
Violation of PRIMARY KEY constraint 'PK__TestingT__3214EC271ED998B2'. Cannot insert duplicate key in object 'dbo.TestingTransactionRollbacks'. The duplicate key value is (2).
The statement has been terminated.

(1 row(s) affected)

(3 row(s) affected)

Results

ID   SomeDate
1    2014-03-08 13:23:35.583
2    2014-03-08 13:23:35.583
3    2014-03-08 13:23:35.583

So as one can see this is not ideal at all.  In a manner similar to the above I made an extension to that example:

--3 records from aboveinsert into LiveTable
    (SomeDate)
select SomeDate
    from TestingTransactionRollbacks

insert into LiveTable
    (Id,SomeDate)
select ID,  somedate
    from TestingTransactionRollbacks
   
Begin tran
   
    delete
    from LiveTable

    insert into LiveTable
        (Id,SomeDate)
    select null, somedate
        from TestingTransactionRollbacks
       
commit
       
select * from LiveTable

Messages:

(3 row(s) affected)
Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'ID', table 'OdeToFood.Models.OdeToFoodDb.dbo.LiveTable'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(0 row(s) affected)

Results:

 --none

So how to prevent horrible things like this?

1) Placing Set Xact_Abort On before the Transaction in the above sql and running it outputs the following message:

(3 row(s) affected)
Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'ID', table 'OdeToFood.Models.OdeToFoodDb.dbo.LiveTable'; column does not allow nulls. INSERT fails.

But when you run "select * from LiveTable"  you get the records that were deleted before without the Set Xact_Abort On.  This method works for these kind of errors.  There is one thing in the docs that says that XACT_Abort will not rollback on syntax errors, so I might have to investigate that further.


2) The other way is similar to .Net Try Catches.

Begin tran
    begin try
        delete
        from LiveTable

        insert into LiveTable
            (Id,SomeDate)
        select null, somedate
            from TestingTransactionRollbacks
       
        commit
    end try   
    begin catch
        rollback
    end catch
       
select * from LiveTable

Doing this way only outputs the results of the select at the end without the disastrous delete. However there should probably be some error logging or something, since this is the equivalent of an empty try catch in .NET.
Messages:

(3 row(s) affected)

(0 row(s) affected)

(3 row(s) affected)

Results:

1    2014-03-08 14:12:34.910
2    2014-03-08 14:06:41.740
3    2014-03-08 14:12:34.910

Now that I understand this better I can go on to assist my co-workers in understanding this as well.