开发者

What is the scope of TRANSACTION in Sql server

I was creating a stored procedure and I see some differences between my methodology and my colleague's.

I am using SQL Server 2005

  1. My Stored procedure looks like this

    BEGIN TRAN
        BEGIN TRY
            INSERT INTO Tags.tblTopic 
                (Topic, TopicCode, Description)
                VALUES(@Topic, @TopicCode, @Description)
    
    
            INSERT INTO Tags.tblSubjectTopic
                (SubjectId, TopicId)
                VALUES(@SubjectId, @@IDENTITY)
          COMMIT TRAN
    END TRY
    
    BEGIN CATCH
            DECLARE @Error VARCHAR(1000)
            SET @Error= 'ERROR NO : '+ERROR_NUMBER() + ', LINE NO : '+ ERROR_LINE() + ', ERROR MESSAGE : '+ERROR_MESSAGE()
            PRINT @Error
            ROLLBACK TRAN
    END CATCH
    
  2. And my colleague's way of writing looks like the below one

    BEGIN TRY
        BEGIN TRAN
            INSERT INTO Tags.tblTopic 
                (Topic, TopicCode, Description)
                VALUES(@Topic, @TopicCode, @Description)
    
    
            INSERT INTO Tags.tblSubjectTopic
                (SubjectId, TopicId)
                VALUES(@SubjectId, @@IDENTITY)
       COMMIT TRAN
    END TRY
    
    BEGIN CATCH
            DECLARE @Error VARCHAR(1000)
            SET @Error= 'ERROR NO : '+ERROR_NUMBER() + ', LINE NO : '+ ERROR_LINE开发者_如何学Go() + ', ERROR MESSAGE : '+ERROR_MESSAGE()
            PRINT @Error
            ROLLBACK TRAN
    END CATCH
    

Here the only difference between the two methods is the position of Begin TRAN. According to me my colleague's method should not work when an exception occurs i.e. Rollback should not get executed because TRAN doesn't have scope in method 2. But when I tried to run both the methods, they were working in the same way.

In Method 1, scope of TRAN is outside of try block so it should be visible in both try block and catch block and should give result as per the scope methodology of programming works.

In Method 2, scope of TRAN is limited within Try block so Commit and Rollback should occur within the try block and should throw exception when a Rollback with no Begin Tran exists in catch block, but this is also working perfectly.

I am confused about how TRANSACTION works. Is it scope-free?


Transactions are not "scoped" in the way that programming languages are.

Transactions are nested for the current connection. Each BEGIN TRAN starts a new transaction and this transaction ends whenever a COMMIT or ROLLBACK is called, it does not matter where in your stored proc this is.


Transactions are nested for the current connection. Each BEGIN TRAN starts a new transaction and this transaction ends whenever a COMMIT or ROLLBACK is called, it does not matter where in your stored proc this is.

only to add that ROLLBACK ends "all" open transactions for the connection...

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜