Statement Failures Within TRANSACTIONS

  • Hello,

    I wonder if someone could just confirm for me what should (or should not) happen when a statement within a BEGIN TRANSACTION .... COMMIT fails.

    I have two INSERT statements within the transaction that insert into two tables. The second INSERT failed due to an overflow of a TINYINT table column. I had assumed that when something like this happened that the entire transaction would auto rollback as a result of a failure to complete. As someone quite new to all this, it sort of seems logical that such failures should rollback the transaction without manual intervention, but it does not appear to do that - i.e I have entries written into the first table correctly from the first INSERT executed within the transaction block.

    Am I going to have to use BEGIN TRY ... CATCH blocks and ROLLBACK whenever I want a transaction to cleanly rollback when an error occurs?

    Regards

    Steve

  • raotor (8/28/2012)


    Hello,

    Am I going to have to use BEGIN TRY ... CATCH blocks and ROLLBACK whenever I want a transaction to cleanly rollback when an error occurs?

    This is the normal way of handling rollbacks.

    It gives you more control over what happens & you can output your own error message.

  • Thinking about it, you can set up automatic transactions, although I've never done it:

    http://msdn.microsoft.com/en-us/library/ms188792.aspx

  • CREATE TABLE TEST(

    id INT NOT NULL,

    name VARCHAR(50)

    )

    --This would still insert because two inserts are considered different transactions because you

    --explicitly didn't committed it yet!

    BEGIN TRAN

    INSERT INTO TEST(id, name) VALUES(1,'a')

    INSERT INTO TEST(name) VALUES('b')

    COMMIT TRAN

    --Any code that fails in the begin try scope, it will shift and automatically go to the begin catch scope

    --which would eventually rollback the whole transaction from the statement that fails

    --to the statement after the begin try

    BEGIN TRAN

    BEGIN TRY

    INSERT INTO TEST(id, name) VALUES(2,'c')

    INSERT INTO TEST(name) VALUES('d')

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN

    END CATCH

    SELECT * FROM TEST

    DROP TABLE TEST

    You can refer to the link for controlling transactions.

    http://msdn.microsoft.com/en-us/library/ms175523%28v=sql.105%29

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

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • laurie-789651 (8/28/2012)


    Thinking about it, you can set up automatic transactions, although I've never done it:

    http://msdn.microsoft.com/en-us/library/ms188792.aspx

    Thanks very much for the link.

    I will take a look at it. Does seem to me though that having an alternative to manually catching errors for the purpose of reversing a transaction would make sense (I hope!).

  • There is one. See the blog post I referenced.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/28/2012)


    There is one. See the blog post I referenced.

    Just read it and it's most helpful and just what I was looking for. Thanks.

  • GilaMonster (8/28/2012)


    There is one. See the blog post I referenced.

    Hello Gail,

    Just one thing I've noticed that I think is important to clarify. When using the SET XACT_ABOIRT ON it should be noted that nothing after the COMMIT is executed until a subsequent GO is reached with more statements thereafter.

    Given that my transaction block occurs within a stored procedure and that there is code following that block, using XACT_ABORT ON will effectively abort the entire SP should an error occur within the transaction block.

    OK, not so much of a problem as using BEGIN TRY .. CATCH to manually deal with errors is the alternative, but this point almost tripped me up so I think it's worth mentioning.

    Regards

    Steve

  • Hadn't noticed that because I use TRY .. CATCH all the time. Honestly, you should be catching errors and handling them, not just silently rolling stuff back.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply