Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Proper transaction management within stored procedures

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
derdle
Asked:
derdle
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
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
 
derdleAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now