Transactions Issue | Tech Off

Shrage
Let's Tallk

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.

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.

Shrage
Let's Tallk

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

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.

Shrage
Let's Tallk

Thanks a lot

Matthew van Eerde
AKA Maurits

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.