TSQL Isolation Levels in Dynamic Query

Question

Does the specified isolation level in a stored procedure carry through to the dynamic queries in that stored procedure?

CREATE PROCEDURE MySP AS 
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dSQL VARCHAR(max) = 'SELECT col FROM table';
EXEC(@dSQL);
END

So, what is the isolation level in that dynamic query?

Answer 1

It does carry through to dynamic queries, you can check with something like this:

DBCC useroptions;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dSQL VARCHAR(max) = 'DBCC useroptions';
EXEC(@dSQL);

Answer 2

More generally than just TRANSACTION ISOLATION LEVEL, SET statements are session-level so carry through to sub-processes. However, while they can be changed in a subprocess, those changes do not carry back to the calling / parent context.

Please see the MSDN page for SET Statements for plenty more details.

You can test this easily by doing the following:

SSMS Query Tab 1:

-- DROP TABLE ##tmp;
CREATE TABLE ##tmp (Col1 INT);
INSERT INTO ##tmp (Col1) VALUES (1);

BEGIN TRAN
SELECT * FROM ##tmp WITH (HOLDLOCK, TABLOCKX);
WAITFOR DELAY '00:02:00.000'; -- 2 minute timer
ROLLBACK TRAN;

SSMS Query Tab 2:

-- First, highlight the following and run. It will hang. Cancel the query.
-- SELECT * FROM ##tmp;

-- Second, hit F5. It will return the row twice.
-- If the SET command in the EXEC affected the parent process, the 2nd SELECT
-- would hang.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

EXEC (N' SELECT * FROM ##tmp; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;');

SELECT * FROM ##tmp;

Answer 3

I believe you do carry the isolation level. I did this test, and i get isolation level 1 on the dynamic query

-- Create the SP (my test is on AdventureWorks2012)
CREATE PROCEDURE MySP AS 
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dSQL VARCHAR(max) = 'SELECT * FROM Person.Person; WAITFOR DELAY ''02:00'';';
EXEC(@dSQL);
END

-- Exec SP
EXEC MySP

-- Check the isolation level while your dynamic code is running
SELECT transaction_isolation_level, * FROM sys.sysprocesses t1
JOIN sys.dm_exec_requests t2 on t1.spid = t2.session_id
WHERE t1.spid > 50