Nested transactions in Sql Server

Imagine the following scenario:

I am using SQL Server 2005. I have a transaction that is calling, among other SQL statements, a stored procedure that also has a transaction inside. The outer transaction sometimes fails and it is rolled back after the stored procedure is called and committed successfully.

  • Get Month from Calendar Week (SQL Server)
  • SSRS Report with SQL
  • Repeat first element XML SQL Server using for path
  • TABLOCK vs TABLOCKX
  • Create a group where either value from two columns for a row matches either value from two columns for a different row
  • Why are dot-separated prefixes ignored in the column list for INSERT statements?
  • My question is, does the stored procedure’s transaction rollback too?

  • Visual Studio Report Designer: How do I print something on every second page of a report?
  • What is my Connection String Visual studio 2015
  • Group By Largest revision string
  • How to insert column data from one table into another in SQL Server
  • MSSQL - Return Select
  • SQL Join on partial column data (SQL Server)
  • 5 Solutions collect form web for “Nested transactions in Sql Server”

    With a nested transaction, a commit does not write any changes to disk, except for the top level transaction. A rollback, however works regardless of the level of the transaction, so yes, it will roll the inner transaction back.

    Absolutely yes, the top level transaction will own all the data changes until it is committed or rolled back.

    However I would encourage you to think carefully about the transaction model. The more such scenarios exist in your system the greater your exposure to locking issues. Also the computational expense of the procedure increases.

    It’s remarkable how often, when rationalising SQL, I find transactions have been implemented where they just aren’t required. I encourage you (and anyone working with transactions) to think carefully about why you are using them in each context and what would happen were the transaction not implemented. Just my 2c worth!

    Yes the stored procedure will be rolled back.

    Here is the overall flow of your code:

    BEGIN TRY
    
        BEGIN TRANSACTION
    
        EXEC SotredProcedureName
    
        --Do some other activity
    
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    
        --IF an error occurs then rollback the current transaction, which includes the stored procedure code.
        ROLLBACK TRANSACTION
    
    END CATCH
    

    Cheers, John

    I’ve tried with begin tran and commit inside the stored procedure say usp_test.
    Exec these sp with some other query as below

    update x set name='xxx'
    select * from x---contains 'xxx'
    begin tran
    update x set name='yyy'
    select * from x---contains 'yyy'
    exec usp_test
    select * from x---contains 'zzz' inside the sp
    rollback tran
    

    While executing the above query name in x table must be ‘xxx’ its not ‘zzz’ since the first begin tran rollbacked even the sp tran commit.
    So, first begin tran own the data changes.

    This is a useful article when understanding transactions in SQL Server

    it offers a number of good examples and simple definitions.

    SQL-Server-Transactions-and-Error-Handling

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.