Begin Tran / Commit Tran

Begin Tran / Commit Tran

Post by Mike Labos » Wed, 24 Aug 2005 03:30:31


Any reason why you would need to wrap a transaction around a single update?

BEGIN TRANSACTION

UPDATE ThisTable
SET blah blah blah
WHERE foo = @bar

COMMIT TRANSACTION

?!?

--
Peace & happy computing,

Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield
 
 
 

Begin Tran / Commit Tran

Post by Tibor Kara » Wed, 24 Aug 2005 03:48:49

No need. The only reason I can think of if you were to name the transaction. However, naming
transaction has little benefit.

I think you can see the transaction names as they are rolled forward and rolled back during
automatic recovery in the errorlog (at least you used to be able to do that). That is about the only
reason I can think of. Without a name = waste of precious keyboard keys.

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/
Blog: http://www.yqcomputer.com/

 
 
 

Begin Tran / Commit Tran

Post by Mike Labos » Wed, 24 Aug 2005 05:25:48

> No need. The only reason I can think of if you were to name the

Yeah, ok, that's what I thought. Thanks for the much needed "sanity-check".
--
Peace & happy computing,

Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield
 
 
 

Begin Tran / Commit Tran

Post by Brian Selz » Wed, 24 Aug 2005 05:44:41

If that's all that's there in the proc, then it's a waste of keystrokes.
Otherwise, if it ain't broke, don't fix it. The real question is, where's
the error handling and the ROLLBACK? someone may have modified another
working procedure that updated multiple tables and just changed the names to
protect the innocent. It won't hurt anything to leave it in, and if you
ever need to add an update to another table, you've already got all of the
error handling and cleanup in place.

You may have a structure something like:

CREATE PROCEDURE procName ...
BEGIN
...
SET @_TRANCOUNT = @@TRANCOUNT
IF @_TRANCOUNT > 0
SAVE TRANSACTION transactionName
ELSE
BEGIN TRANSACTION transactionName
...
IF @_TRANCOUNT = 0 -- only commit if new tran started
COMMIT TRANSACTION transactionName

RETURN 0 -- indicate success

ERROR:
IF @_TRANCOUNT IS NOT NULL -- only roll back if necessary
IF @@TRANCOUNT > 0 -- roll back any changes made by this proc
ROLLBACK TRANSACTION transactionName

IF @RC > 0 RETURN @RC -- return error from called sp
IF @_ERROR > 0 RETURN @_ERROR -- return error raised in this sp
RETURN -1 -- indicate failure
END



update?