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.