Tech Off Thread

7 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Transactions Issue

Back to Forum: Tech Off
  • User profile image
    Shrage

    I have 2 sql statements, the first inserts a row to the order table, the second inserts items to the order table.

    The second step can take a long time and sometimes we can get a timeout error from ado.net, but i dont want to have the order record if the items are not going to be added, so i want the first statement to be rolledback if second statment fails.

    My question is if the following will work

    BEGIN TRANSACTION
       STEP 1 INSERT.....
       STEP 2 INSERT.....

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK
        RETURN
    END

    COMMIT

    Since the timeout will come from ado.net the following can hapen, either the IF @Error will never return <>0 or the line will never execute.

    So how do i handle such situations when the error can be a timeout error and not any other error.


  • User profile image
    phreaks

    Shrage wrote:
    I have 2 sql statements, the first inserts a row to the order table, the second inserts items to the order table.

    The second step can take a long time and sometimes we can get a timeout error from ado.net, but i dont want to have the order record if the items are not going to be added, so i want the first statement to be rolledback if second statment fails.

    My question is if the following will work

    BEGIN TRANSACTION
       STEP 1 INSERT.....
       STEP 2 INSERT.....

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK
        RETURN
    END

    COMMIT

    Since the timeout will come from ado.net the following can hapen, either the IF @Error will never return <>0 or the line will never execute.

    So how do i handle such situations when the error can be a timeout error and not any other error.




    If it isn't committed , it should roll back regardless of error type.

  • User profile image
    Shrage

    when ADO throws a command tiemout error, is code in the stored procedure also stoped?

  • User profile image
    phreaks

    Shrage wrote:
    

    when ADO throws a command tiemout error, is code in the stored procedure also stoped?



    I believe that the DTC manages the interaction between the 2. (I could be wrong here). Doesn't matter for your question though as I *think* that Sql Server will catch the timeout and roll-back the transaction since it was never comitted.

  • User profile image
    Shrage

    Thanks a lot

  • User profile image
    Matthew van Eerde

    The general answer to these questions is "try it and see"... stick a WAIT FOR DELAY ... statement after the first insert to force a timeout, and see if the INSERT commits.

    In this particular instance, I believe that ADO.NET is actually passing on a command-timeout parameter to the SQL Server.  So I'm guessing that SQL Server is setting its own countdown, and going back to ADO.NET with a "sorry, time ran out" error (which ADO.NET then passes back to you.)  I would guess that ADO.NET is *not* just giving up on SQL Server, which is merrily finishing the transaction after ADO.NET hangs up.

    So yes, I think it would work.  But don't take my word for it... try it and see.  Make a one-off app.

  • User profile image
    Shrage

    Matthew van Eerde wrote:
    ... stick a WAIT FOR DELAY ... statement


    I was waiting for this....i hoped someone will give me a hint how to reproduce a timeout error. thanks for you help

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.