Another Transaction question

Gert · March 11th, 2004, 04:52 AM

Say you have the following:

Code:

BEGIN TRANSACTION 
--do some work
--do some more work
--etc
COMMIT

If there is a problem during the work done, will SQL Server automatically perform a ROLLBACK on this transaction? Or is it my responsibility to check for errors and then rollback?

Thanks in advance.

Gert

Jeff Mason · March 11th, 2004, 07:41 AM

If you explicitly begin a transaction, then you are responsible for explicitly committing it and/or explicitly rolling it back. You must check @@ERROR after each statement and act accordingly.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

happygv · March 11th, 2004, 07:43 AM

Gert,

Here, it is your responsibility to check for errors and Rollback.

You can do that this way.

BEGIN TRANSACTION
--do some work
If @@ERROR<>0
ROLLBACK TRAN
RETURN
END
COMMIT TRAN

Cheers,

-Vijay G

Gert · March 11th, 2004, 04:23 PM

Thanks to both of you.

But I wonder, what will happen if the transaction is written like in my original posting? Will the work be committed even if an error occurs?

Gert

Jeff Mason · March 11th, 2004, 06:19 PM

Quote:

quote:
But I wonder, what will happen if the transaction is written like in my original posting? Will the work be committed even if an error occurs?

Yup, assuming that no fatal error occurs which causes the entire stored procedure to abort.

Since you took no action on any error by testing @@ERROR, the code will run as written, happily falling through to your COMMIT...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff Mason · March 12th, 2004, 12:46 PM

I neglected to point out that there is a SET option which you can use to control this. See BOL for:

SET XACT_ABORT ON

Sorry for the oversight.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Gert · March 12th, 2004, 01:52 PM

Thanks Jeff. This was more to my liking.. :)

defiant · March 16th, 2004, 01:20 PM

Quote:

quote:Originally posted by Jeff Mason

Quote:

quote:

Quote:

But I wonder, what will happen if the transaction is written like in my original posting? Will the work be committed even if an error occurs?

Yup, assuming that no fatal error occurs which causes the entire stored procedure to abort.

Since you took no action on any error by testing @@ERROR, the code will run as written, happily falling through to your COMMIT...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

and if a fatal error ocurs? will the transactions be rolld back then? in BOL it says that the process is suspended and the connection is broken, but nothing about transactions...

thanks,
defiant.

Jeff Mason · March 16th, 2004, 02:44 PM

If a severe/fatal error occurs which will cause the SP to abort, the transaction is rolled back. If the connection is broken because of e.g. network errors, the transaction is rolled back. If the error is not severe (syntax error in a query, constraint errors, etc. for example) such that only the currently executing statement is aborted, the stored procedure will continue to execute and transaction COMMIT/ROLLBACKs will be executed as they are encountered. In this case it is the responsibility of the programmer to test @@ERROR and act accordingly.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com