Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

Error encountered in SQL Server - "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"

Oct 2 2011 7:24AM by Paresh Prajapati   

During development of stored procedures, i ran into one issue and this was due to not applied best practice of stored procedures practice and this issue comes mostly when we call stored procedures inside another one and so on, which are used in nested in transactions.

So i  would like to write next post for the stored procedures development , as how we should write it, It will be a just a template of best practice. In this post i will explain the details of the issue which i suffered and how can we resolve it.

Let's for the demonstration we will created one table and stored procedures.

-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
  (
     TranId    INT NOT NULL PRIMARY KEY
     ,TranName VARCHAR(10)
  )

GO


-- Creating first stored procedure.
CREATE PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION

    -- Inserting records in table,
    -- Due to this insert error will be raised.
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION
      END TRY

      BEGIN CATCH
          ROLLBACK TRANSACTION

          PRINT 'Rollback Tran1'
      END CATCH
  END

GO


-- Creating Second stored procedure
CREATE PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

    -- Calling first created stored procedure here.
          EXEC Firttranproc

          COMMIT TRANSACTION
      END TRY

      BEGIN CATCH
          ROLLBACK TRANSACTION

          PRINT 'Rollback Tran2'
      END CATCH
  END

GO



-- Executing second stored procedure here and see what happen.
EXEC Secondtranproc

GO










In first stored we have written a code to raise error and due to transaction issue while running second stored procedure it raised error.

This is not best practice as we have write only "Begin Transaction". We should specify the name of the stored procedure.

# 1 Solution :
Let's specify the name of transactions in both of the stored procedures and altering stored procedures here and execute it again with same.

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON
   
   -- Here we have specified Tran1 as transaction name
 
   BEGIN TRY
          BEGIN TRANSACTION Tran1

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
   
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION Tran1
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran1'

          ROLLBACK TRANSACTION Tran1
      END CATCH
  END

GO

-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

  -- Here we have specified Tran2 as transaction name

      BEGIN TRY
          BEGIN TRANSACTION Tran2

    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

          -- Calling first stored procedure here
          EXEC Firttranproc

          COMMIT TRANSACTION Tran2
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran2'

          ROLLBACK TRANSACTION Tran2
      END CATCH
  END

GO


-- Executing second stored procedure which will also call of SP1 
EXEC Secondtranproc

GO









You can see here the error is not raised which is raided in first case. Here stored procedure find and commit/rollback correct and their transactions only.

# 2 Solution :
Le's look another alternative method which can also help to come out from issue.

The rewritten stored procedures,

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON
   
   -- Here we have specified Tran1 as transaction name
 
   BEGIN TRY
          BEGIN TRANSACTION 

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
   
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION 
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran1'

   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION 
      END CATCH
  END

GO

-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION 

    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

          -- Calling first stored procedure here
          EXEC Firttranproc

          COMMIT TRANSACTION 
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran2'

    
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION 
      END CATCH
  END

GO


-- Executing second stored procedure which will also call of SP1 
EXEC Secondtranproc

GO

 Here is same output as second case , it is also not raised error.








Let me know what you think over here. You also encountered this issue in past, Please share you experience and alternation solution. 

Tags: sql server, #SQLServer, data,


Paresh Prajapati
6 · 23% · 7444
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Although I agree with the gist of the first and the last code-block, I'm pretty sure the one in the middle does NOT do what you think it does.

    Disclaimer : I'm not sure how this behaves under Oracle, but the named transactions simply don't work like that in MSSQL.

    To explain this I think adding a simple PRINT of the error-message in the CATCH of both stored procedures will show that (as expected) there is a PK violation in Firttranproc but that you end up in the CATCH block of Secondtranproc because the ROLLBACK in Firttranproc didn't go through! (**)

    -- Altering first stored procedure here
    ALTER PROCEDURE Firttranproc
    AS
      BEGIN
          SET NOCOUNT ON
    
       -- Here we have specified Tran1 as transaction name
    
       BEGIN TRY
              BEGIN TRANSACTION Tran1
    
              INSERT INTO tbl_Tran
                          (TranId
                           ,TranName)
    
              SELECT
                1
                ,'Tran-1'
              UNION ALL
              SELECT
                1
                ,'Tran-1'
    
              COMMIT TRANSACTION Tran1
          END TRY
    
          BEGIN CATCH
              PRINT 'Firttranproc error : ' + ERROR_MESSAGE()
              PRINT 'Rollback Tran1'
    
              ROLLBACK TRANSACTION Tran1
          END CATCH
      END
    
    GO
    
    -- Altering second stored procedure here
    ALTER PROCEDURE Secondtranproc
    AS
      BEGIN
          SET NOCOUNT ON
    
      -- Here we have specified Tran2 as transaction name
    
          BEGIN TRY
              BEGIN TRANSACTION Tran2
    
        -- Inserting records
              INSERT INTO tbl_Tran
                          (TranId
                           ,TranName)
              SELECT
                2
                ,'Tran-2'
    
              -- Calling first stored procedure here
              EXEC Firttranproc
    
              COMMIT TRANSACTION Tran2
          END TRY
    
          BEGIN CATCH
              PRINT 'Secondtranproc error : ' + ERROR_MESSAGE()
              PRINT 'Rollback Tran2'
    
              ROLLBACK TRANSACTION Tran2
          END CATCH
      END
    
    GO
    
    
    -- Executing second stored procedure which will also call of SP1 
    EXEC Secondtranproc
    

    will then result in :

    Firttranproc error : Violation of PRIMARY KEY constraint 'PK__tbl_Tran__F70897C905E3CDB6'. Cannot insert duplicate key in object 'dbo.tbl_Tran'.
    Rollback Tran1
    Secondtranproc error : Cannot roll back Tran1. No transaction or savepoint of that name was found.
    Rollback Tran2
    

    I'm not sure yet why the ROLLBACK in Secondtranproc doesn't show an error then, but I'm guessing here that this is because that transaction is the 'outer' TRANSACTION (where @@TRANCOUNT goes from 0 to 1) which can be rolled back 'name-wise' without creating a savepoint explicitly.

    Named transactions would be 'great stuff' if they wouldn't be so ridiculously cumbersome to set up =/

    (**: always check WHY you ended up in a CATCH block, never assume you know why. Think about it : if you just run Firttranproc, there is no error raised at all, it's eaten entirely by the TRY..CATCH, so then why did you end up in the CATCH block of Secondtranproc ??)

    commented on Oct 6 2011 4:29AM
    Roby Van Hoye
    222 · 1% · 209
  • @Roby Van Hoye: I agree with you. The middle code is absolutely not doing what it appears to be doing.

    Nested transactions DO NOT exist in SQL Server. Whenever a ROLLBACK is issued, the rollback happens all the way to the outermost open transaction - unless of course, we are rolling back to a known SAVEPOINT. I had a whole series of Question-of-the-Day on SQL Server Central demonstrating this. You can also read more at: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx

    Paresh: Books On Line (http://msdn.microsoft.com/en-us/library/ms181299.aspx) also mentions this: "*ROLLBACK TRANSACTION without a savepointname or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.*"

    commented on Oct 22 2011 11:57PM
    Nakul Vachhrajani
    4 · 36% · 11624

Your Comment


Sign Up or Login to post a comment.

"Error encountered in SQL Server - "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"" rated 5 out of 5 by 3 readers
Error encountered in SQL Server - "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]