none
For what all error levels transaction are not rolled back? RRS feed

  • Question

  • In an exercise below (I've also documented this scenario here)

    - the Transaction t1, is not rolledbacked when Primary Key violation occurred (Error level = 14).

    - the Transaction t2 is rolledbacked when Datatype conversion error occurred (Error level = 16).

     

    create table x (nbr int primary key)
    
    insert into x values(1)
    insert into x values(2)
    
    
    -- Tran t1:
    begin transaction t1
        insert into x values(3)
        insert into x values(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
        insert into x values(4)
    commit transaction t1
    
    select * from x
    -- Result 4 inserted rows, Transaction is not rolledback, only statement is terminated
    
    
    -- Tran t2:
    begin transaction t2
        insert into x values(5)
        insert into x values('a') -- Msg 245, Level 16, State 1, Line 19, Conversion failed...
        insert into x values(6)
    commit transaction t2
    
    select * from x
    -- No results, Entire transaction is rolledback.
    
    drop table x
    
    
    

    The transaction t1 can be automatically rolledbacked provided it is binded in TRY-CATCH block or XACT_ABORT is SET to ON, like this:

    SET XACT_ABORT ON
    begin transaction t1
        insert into x values(3)
        insert into x values(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
        insert into x values(4)
    commit transaction t1
    SET XACT_ABORT OFF
    
    select * from x
    

    But why this difference between level 14 & 16 errors?

    Check the demo here: http://sqlwithmanoj.com/2011/12/01/xact_abort-with-transactions/


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011

    Tuesday, December 6, 2011 9:44 AM

Answers

  • The severity level has nothing to do with it. At least I have not been able to discern a pattern.

    It is just the way it is. Error handling in SQL Server is such a big mess. Some errors aborts the batch and rolls back the transaction. Other errors terminates and rolls back the current statement only. Other errors terminates the scope without rolling back. And in SQL 2012 we get errors that aborts the batch without rolling back the transaction. You are strongly adviced not trying to find a pattern. That would not be good for your mental health. Well, the errors that terminates the scope are always compilation errors (although not all may seem like one).

    If you run with SET XACT_ABORT ON, the level of consistency raises dramatically, since when XACT_ABORT is ON, the vast majority of errors now abort the batch and roll back the transaction. But of course not. After all, this is SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 6, 2011 11:56 AM

All replies

  • The level 14 error does not stop a batch from completing so commit transaction t1 still gets executed.  However, a level 16 error stops a batch from completing so the commit transaction t2 does not execute so the records are not committed.  If you run the following, you'll see that t1 insert also do not get committed.

          create table x (nbr int primary key)
        
    insert into x values(1)
    insert into x values(2)

    -- Tran t1:
    begin transaction t1
        insert into x values(3)
        insert into x values(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
        insert into x values(4)

    select * from x


    -- Tran t2:
        insert into x values(5)
        insert into x values('a') -- Msg 245, Level 16, State 1, Line 19, Conversion failed...
        insert into x values(6)
    commit transaction

    select * from x

    drop table x


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Edited by Mr. Wharty Tuesday, December 6, 2011 9:59 AM
    Tuesday, December 6, 2011 9:57 AM
  • In addition http://www.sommarskog.se/error_handling_2005.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, December 6, 2011 9:58 AM
    Answerer
  • The severity level has nothing to do with it. At least I have not been able to discern a pattern.

    It is just the way it is. Error handling in SQL Server is such a big mess. Some errors aborts the batch and rolls back the transaction. Other errors terminates and rolls back the current statement only. Other errors terminates the scope without rolling back. And in SQL 2012 we get errors that aborts the batch without rolling back the transaction. You are strongly adviced not trying to find a pattern. That would not be good for your mental health. Well, the errors that terminates the scope are always compilation errors (although not all may seem like one).

    If you run with SET XACT_ABORT ON, the level of consistency raises dramatically, since when XACT_ABORT is ON, the vast majority of errors now abort the batch and roll back the transaction. But of course not. After all, this is SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 6, 2011 11:56 AM