Solved

Proper transaction management within stored procedures

Posted on 2011-02-19
2
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

751 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