Consider the following SQL:

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

INSERT Bands
    (  Name  )
SELECT 'Depeche Mode'
UNION
SELECT 'Arcade Fire'

    -- I've indented the inner transaction to make it clearer.

    BEGIN TRAN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT *
      FROM Bands

    COMMIT

-- What is the isolation level right here?

UPDATE Bands
   SET Name = 'Modest Mouse'
 WHERE Name = 'Oddest House'

COMMIT

In sum, we start a transaction and set its isolation level to READ COMMITTED. We then do some random SQL and start another, nested transaction. In this transaction we change the isolation level to READ UNCOMMITTED. We then commit that transaction and return to the other.

Now, my guess is that after the inner commit, the isolation level returns to READ COMMITTED. Is this correct?

sql-server transactions
Edit
Report