Is SQL Begin /Commit / Rollback Tran Always Good To Use?

Q. Can the  SQL Begin /Commit / Rollback Tran be used in ALL Store Procedure and Triggers?

Q. Is this too much coding over-kill?
WorknHardrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Studying for exams are we?

>Q. Can the  SQL Begin /Commit / Rollback Tran be used in ALL Store Procedure and Triggers?
Yes.

>Q. Is this too much coding over-kill?
Define the terms 'too much' and 'coding'.  Mind readers we ain't, unless you would like me to summon the Magic 8-ball here.
0
WorknHardrAuthor Commented:
I suppose the therm 'coding over-kill' is a bit too broad. I find many SP's and Trigs written very simple without the extra Trans tags or Try Catch's.
0
Scott PletcherSenior DBACommented:
I'd avoid coding any COMMITs within a trigger, since you're automatically already within a transaction you know nothing else about.  You could cause data inconsistencies and/or application errors, depending on how the app was coded.

Similarly, you need to check for an existing transaction before issuing a COMMIT statement in ANY stored proc or other code.  If code called your proc or other code and the calling code had already started a transaction,  you've just committed THEIR transaction, which could completely destroy their intended use of the transaction and destroy data consistency, and again cause application errors.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

WorknHardrAuthor Commented:
Good advice!
0
Scott PletcherSenior DBACommented:
I should have been more careful with my wording though.

>> If code called your proc or other code and the calling code had already started a transaction,  you've just committed THEIR transaction <<

Should be "you MAY have just committed THEIR transaction".


To be clearer, if a transaction is already in effect, issuing a BEGIN TRAN and a COMMIT TRAN effectively does nothing in SQL Server, since SQL doesn't have true embedded transactions -- it simply incremements @@TRANCOUNT and then decrements @@TRANCOUNT.

But if your issues a COMMIT w/o having issued your BEGIN TRAN, perhaps by accident, and the trancount is currently one, then you've committed the entire transaction, no matter at what level of code it began, even level(s) up from your code.

Thus, you should always check @@trancount before using a BEGIN TRAN -- you can skip the BEGIN TRAN ... COMMIT if you're already within a transaction.



More generally, and likely more to the point of your original q, if no transaction is in effect, you should use your own transaction when you need the all-or-none actions are applied that a transaction guarantees.

The classic example is transferring money in bank accounts.  Say you want to move $100 from savings to checking.  You sure don't want the money to leave savings but not end up in checking; likewise, the bank won't add money to checking unless it's sure it's removed from savings.  Thus, those two actions should always be combined in a single transaction:

SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.savings SET balance = balance - 100 WHERE user = 123
UPDATE dbo.checking SET balance = balance + 100 WHERE user = 123
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE <> 0
    ROLLBACK TRANSACTION
END CATCH
0
WorknHardrAuthor Commented:
I think XACT_STATE is cool, just never used it, found more info here.

Based on original post:
Q. Is there any reason to use Trans in a sp with just a simple Select...?
0
WorknHardrAuthor Commented:
Back to the XACT_STATE, I currently have this code which inserts any error in a log table and calls Rollback.

Q. How would I use XACT_STATE in the Try Catch using my code example?

ALTER TRIGGER [dbo].[UpdateInventory] ON [dbo].[Inventory]
AFTER INSERT 
AS

DECLARE @T TABLE(Id INT);
SET NOCOUNT ON;
SET XACT_ABORT ON

BEGIN
   BEGIN TRY
         BEGIN TRAN
               UPDATE ...
               UPDATE...
         COMMIT TRAN
   END TRY
   BEGIN CATCH
        DECLARE @ErrorMsg VARCHAR(MAX), 
                         @ErrorNumber INT, 
                         @ErrorProc sysname, 
                         @ErrorLine INT 

	SELECT @ErrorMsg = ERROR_MESSAGE(), 
                     @ErrorNumber =ERROR_NUMBER(),                   
                     @ErrorProc = ERROR_PROCEDURE(), 
                     @ErrorLine = ERROR_LINE();

	ROLLBACK TRAN;

	INSERT INTO Trigger_ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine)
		VALUES (@ErrorMsg, @ErrorNumber, @ErrorProc, @ErrorLine)

   END CATCH 
END

Open in new window

0
Scott PletcherSenior DBACommented:
>> Q. Is there any reason to use Trans in a sp with just a simple Select...? <<

NO.  No transaction will ever be logged for just SELECT statements, so there is no point in even beginning a trans.  Trans only apply to DELETE and/or INSERT and/or UPDATE statements.



>> Q. How would I use XACT_STATE in ... Try Catch ... code << 

Use it exactly as I did in my sample above, i.e., by checking it for "<> 0" before rolling back.

If the XACT_STATE() is 0, there is no transaction to rollback, and issuing a ROLLBACK then could cause an error ("The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.").
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WorknHardrAuthor Commented:
I agree, thx...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.