sql server programming: Transactions... -- sql server programming

Transactions...

Mike Labosh
6/28/2005 9:49:49 PM

sql server programming:

Let's say I have a big fat SP that goes something like this:

BEGIN TRANSACTION
-- Outer transaction

-- Do some stuff.

BEGIN TRANSACTION
-- Inner Transaction 'A': Do other Stuff
COMMIT TRANSACTION

BEGIN TRANSACTION
-- Inner Transaction 'B': Do More Stuff
COMMIT TRANSACTION

-- Try to do some more stuff
-- BOOM! Primary Key Violation causes Rollback

COMMIT TRANSACTION

Will transactions A and B also be rolled back?

Also, what if it goes like this:

BEGIN TRANSACTION
-- Do some stuff.
EXEC sp1
EXEC sp2
-- Try to do some more stuff
-- BOOM! Primary Key Violation causes Rollback
COMMIT TRANSACTION

And sp1 & sp2 both have a BEGIN TRAN / COMMIT TRAN block in them. Same
behavior as the first example?
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

Re: Transactions...

Brian Selzer
6/28/2005 11:55:57 PM

Absolutely. A rollback transaction rolls back to the outer most
transaction. The inner transactions really don't do anything at all, except
increment @@TRANCOUNT. If you want to be able to roll back part of a
transaction, use SAVE TRANSACTION savepoint/ROLLBACK TRANSACTION savepoint.

Both methods have the same results. The one exception is if BOOM! happens
in Inner transaction A, then Inner transaction B will commit, and the final
COMMIT statement will cause an error to occur. The same is true if BOOM!
happens in sp1. That's why you should always, always, always check @@ERROR
after every INSERT/UPDATE/DELETE statement in a stored procedure or query
batch.

[quoted text, click to view]