Solved

Proper transaction management within stored procedures

Posted on 2011-02-19
2
329 Views
Last Modified: 2012-10-23
OK, hope this is an acceptable post/question(apologies if not)...in that I know that there are a TON of resources and examples on the web.  I've reviewed enough to reach a level of uncertainty to which I could continue reading for countless hours...or have an educational discussion here...   ...or I could get flamed out to go continue reading elsewhere (which I would understand and accept).  Just thinking that an active discussion will resolve confusion much faster.

Anyway..SQL 2008.  Trying to understand and determine the best 'shell' for stored procedures to manage transactions properly.

At this time, I'm looking at the following, but some things I have read, have me unsure.

CREATE PROCEDURE [dbo].[test_proc]
AS
BEGIN

   --flag for whether this sproc originated the transaction 0=no, 1=yes
   DECLARE @v_trans_started   bit
   BEGIN TRY

      PRINT 'Executing test procedure...'


      SET @v_trans_started = 0
     --trans count would be > 0 if called from another proc. if 0 we start new trans
      IF @@TRANCOUNT = 0
         BEGIN
            BEGIN TRANSACTION
            SET @v_trans_started = 1
         END
     
      --Execute logic within transaction...
      print '...doing a bunch of stuff/CAD within transaction.'

      --when logic complete, if we started the current trans...commit it
      --if we did not start the current trans, just return and allow whoever
      --started the trans to commit it, which would include actions done in this proc
      IF @v_trans_started = 1
         BEGIN
            SET @v_trans_started = 0
            COMMIT TRANSACTION
         END
      ELSE
         BEGIN
              RETURN 0
        END
   END TRY

   BEGIN CATCH
      --if there were any errors and we started the current trans....roll it back
      IF @v_trans_started = 1
         BEGIN
            SET @v_trans_started = 0
            ROLLBACK TRANSACTION
         END
         --if we didn't start the trans...we just return and let whoever started the trans roll it back
         RAISERROR ('Error in test proc!',16,1)
         RETURN -1

   END CATCH
END

I guess I am open to any and all comments on how to better this, by addition, deletion, destruction (lol)....  I've read a lot that has said to take this route in order to properly deal with commiting and rolling back based on if you started the trans, but I feel like I should not worry about that and always start a new trans that ends with either a commit or rollback.

more simply..like such

CREATE PROCEDURE [dbo].[test2_proc]
AS
BEGIN
   BEGIN TRY
      PRINT 'Executing test2 procedure...'
      BEGIN TRANSACTION
               
              --Execute logic within transaction...
              print '...doing a bunch of stuff/CAD within transaction.'
 
             COMMIT TRANSACTION
             RETURN 0
   END TRY

   BEGIN CATCH
                 
         ROLLBACK TRANSACTION
         RAISERROR ('Error in test proc!',16,1)
         RETURN -1

   END CATCH
END

Does using the TRY/CATCH allow you to avoid placing an @@ERROR check after every action in the transaction...any situations that it would miss?

Hope this was OK to open up for somewhat general discussion.

Thanks guys.
0
Comment
Question by:derdle
2 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
ID: 34936284
hi basically your procedure should look like this if you want to manage transactions

CREATE PROCEDURE TEST
AS
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
END
0
 

Author Comment

by:derdle
ID: 34938470
OK, we can work with that example as well, although I don't see this managing the transactions any more than my second example except for gathering the error data (which I left out for brevity, but do agree should be used).  

The questions come into play if/when this proc has been called from another, versus running independently.  I've seen references that say COMMIT will always only lower TRANSCOUNT by 1, and until a COMMIT occurs when TRANSCOUNT=1...the transaction is not truly committed.  Have also seen indication that ROLLBACK will always take it back to 0 regardless of the current #...and in order to only ROLLBACK the current transaction you need to do it by name (which I meant to show in my second example).  

 Guess I need to go run some tests...which I plan to do today, but figured there would be some good insight in a discussion with those that know.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question