de-escalation in nested transactlion

Rajesh Kumar Yadav 1 Reputation point
2023-05-31T06:08:56.31+00:00

hi,

I have one nested transaction and want to deescalate to outer  transaction's isolation/ or lower isolation.

is it the right way?


SET TRANSACTION ISOLATION LEVEL read committed;
				BEGIN TRY
				BEGIN TRAN 

DBCC USEROPTIONS
--want to do some job in read commited

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
				BEGIN TRY
				BEGIN TRAN 
DBCC USEROPTIONS

--want to do some job in SERIALIZABLE
COMMIT TRANSACTION;

				END TRY
				BEGIN CATCH
					IF @@TRANCOUNT >0  ROLLBACK TRANSACTION

				END CATCH

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL read committed;
-- want to get back to outer transactions isolation or in simple i want to get into new lower isolation.



COMMIT TRANSACTION;

				END TRY
				BEGIN CATCH
					IF @@TRANCOUNT >0  ROLLBACK TRANSACTION

				END CATCH


DBCC USEROPTIONS

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,272 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,860 questions
{count} votes

2 answers

Sort by: Most helpful
  1. B santhiswaroop naik 385 Reputation points
    2023-05-31T06:42:05.8533333+00:00

    Nested transactions in SQL Server are not fully supported and do not have their own isolation levels. The isolation level is determined by the outermost transaction, and you cannot change the isolation level within a nested transaction.

    In your code example, the SET TRANSACTION ISOLATION LEVEL statements within the nested transaction will not have any effect. The isolation level will remain the same as the outer transaction's isolation level.

    If you need different isolation levels for different parts of your code, you will need to structure your transactions accordingly. You can start a new transaction with the desired isolation level and then commit or rollback that transaction before continuing with the outer transaction.

    Here's an example of how you can achieve this:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRY
        BEGIN TRAN OuterTransaction;
    
        -- Code within the outer transaction with READ COMMITTED isolation level
    
        -- Start a new transaction with SERIALIZABLE isolation level
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        BEGIN TRY
            BEGIN TRAN InnerTransaction;
    
            -- Code within the inner transaction with SERIALIZABLE isolation level
    
            COMMIT TRAN InnerTransaction; -- Commit the inner transaction
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK TRAN InnerTransaction; -- Rollback the inner transaction
            THROW; -- Re-throw the error
        END CATCH
    
        -- Continue with the outer transaction
    
        COMMIT TRAN OuterTransaction; -- Commit the outer transaction
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN OuterTransaction; -- Rollback the outer transaction
        THROW; -- Re-throw the error
    END CATCH
    
    
    
    

    In this example, the code within the inner transaction will have the SERIALIZABLE isolation level, while the code within the outer transaction will have the READ COMMITTED isolation level.

    Remember to handle any errors appropriately and ensure that transactions are committed or rolled back as needed.

    1 person found this answer helpful.

  2. Erland Sommarskog 106K Reputation points MVP
    2023-05-31T21:48:31.86+00:00

    There are no nested transactions in SQL Server. All that happens when you say BEGIN TRANSACTION a second or a third time is that @@trancount is incremented + 1. When you say COMMIT TRANSACTION, @@trancount is decremented by 1. If @@trancount now is zero, the transaction is committed. Else nothing else happens beside the decrement when you run COMMIT.

    The main using for transaction is when you have nested stored procedure. Say that you have a procedure A that performs an atomic piece of work, and therefore defines a transaction. Then procedure B calls A as part of a bigger plot. B also defines a transaction, to make sure that its work is performed in whole or not at all.

    When you say ROLLBACK TRANSACTION it does not matter what value @@trancount has. The transaction is always rolled back in its entirety, even if the ROLLBACK occurs inside a "nested" transaction.

    When it comes to the isolation level, it can change many times during the course of the transaction. It can also be overridden for a specific table with a hint. There is no relation between transaction isolation level and transaction nesting.

    Note: I'm here talking about regular T-SQL code. Natively compiled stored procedures is a somewhat different matter.

    0 comments No comments