Solved

Proper transaction management within stored procedures

Posted on 2011-02-19
2
325 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now