Transaction Isolation Levels and sp_executesql

2019-08-19 · by Hannah Vernon · in basics

SQL Server has several transaction isolation levels; probably the one most people are familiar with is the default of “read committed”. However, you may not realize it, but running dynamic code via sys.sp_executesql doesn’t necessarily change the isolation level the way you’ve specified. Isolation Levels and sp_executesql don’t mix quite as nicely as you might hope.

Frédéric Soulacroix – The Rose – No Transaction Isolation Levels and sp_executesql here!

The Microsoft Docs say this about read-committed isolation:

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. This option is the SQL Server default.

One commonly misused isolation level is “read uncommitted”, which is commonly invoked via the WITH (NOLOCK) table hint. Read-uncommitted isolation, in the form of the WITH (NOLOCK) hint, is commonly believed to be a “go faster” button for SQL Server, since it requires no shared read locks, and can read data that is locked for writing by other sessions. This results in reduced “blocking”, and is often perceived as the application running faster. It also allows reading of uncommitted data, non-repeatable reads, etc, as documented thoroughly by Paul White here.

Imagine you have a piece of code where you don’t care about the downsides to the “read uncommitted” isolation level, and do your due diligence by adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; at the start of your code. The code following that statement will run under that isolation level, as expected. However, if you call dynamic T-SQL with sys.sp_executesql, and set the isolation level there, the dynamic code will run under the READ UNCOMMITTED, however the isolation level will not be changed for the calling code. In other words, be careful about where you set the isolation level.

Example Script

Here’s a minimal, complete, and verifiable example showing that:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

IF OBJECT_ID(N'tempdb..#useroptions', N'U') IS NOT NULL
DROP TABLE #useroptions;
CREATE TABLE #useroptions
(
    [Set Option] sysname NOT NULL
    , [Value] sql_variant NULL
);
DECLARE @cmd nvarchar(max);

SET @cmd = N'DBCC USEROPTIONS;';
INSERT INTO #useroptions ([Set Option], [Value])
EXEC sys.sp_executesql @cmd;

SELECT *
FROM #useroptions uo
WHERE uo.[Set Option] = N'isolation level';
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

IF OBJECT_ID(N'tempdb..#useroptions', N'U') IS NOT NULL

DROP TABLE #useroptions;

CREATE TABLE #useroptions

(

[Set Option] sysname NOT NULL

, [Value] sql_variant NULL

);

DECLARE @cmd nvarchar(max);

SET @cmd = N'DBCC USEROPTIONS;';

INSERT INTO #useroptions ([Set Option], [Value])

EXEC sys.sp_executesql @cmd;

SELECT *

FROM #useroptions uo

WHERE uo.[Set Option] = N'isolation level';

The results:

╔═════════════════╦════════════════╗
║   Set Option    ║     Value      ║
╠═════════════════╬════════════════╣
║ isolation level ║ read committed ║
╚═════════════════╩════════════════╝

In the same session, let’s use sys.sp_executesql to change the isolation level:

TRUNCATE TABLE #useroptions;

DECLARE @cmd nvarchar(max);
SET @cmd = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DBCC USEROPTIONS;';
INSERT INTO #useroptions ([Set Option], [Value])
EXEC sys.sp_executesql @cmd;

SELECT *
FROM #useroptions uo
WHERE uo.[Set Option] = N'isolation level';
GO

TRUNCATE TABLE #useroptions;

DECLARE @cmd nvarchar(max);

SET @cmd = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DBCC USEROPTIONS;';

INSERT INTO #useroptions ([Set Option], [Value])

EXEC sys.sp_executesql @cmd;

SELECT *

FROM #useroptions uo

WHERE uo.[Set Option] = N'isolation level';

The output shows the isolation level was changed inside the Dynamic T-SQL execution context:

╔═════════════════╦══════════════════╗
║   Set Option    ║      Value       ║
╠═════════════════╬══════════════════╣
║ isolation level ║ read uncommitted ║
╚═════════════════╩══════════════════╝

However, if we now check the isolation level outside the dynamic SQL, we’ll see it’s back to “read committed”:

TRUNCATE TABLE #useroptions;

DECLARE @cmd nvarchar(max);
SET @cmd = N'DBCC USEROPTIONS;';
INSERT INTO #useroptions ([Set Option], [Value])
EXEC sys.sp_executesql @cmd;

SELECT *
FROM #useroptions uo
WHERE uo.[Set Option] = N'isolation level';

TRUNCATE TABLE #useroptions;

DECLARE @cmd nvarchar(max);

SET @cmd = N'DBCC USEROPTIONS;';

INSERT INTO #useroptions ([Set Option], [Value])

EXEC sys.sp_executesql @cmd;

SELECT *

FROM #useroptions uo

WHERE uo.[Set Option] = N'isolation level';

The output:

╔═════════════════╦════════════════╗
║   Set Option    ║     Value      ║
╠═════════════════╬════════════════╣
║ isolation level ║ read committed ║
╚═════════════════╩════════════════╝

The take-away here is changes made to transaction isolation levels inside dynamic T-SQL don’t affect the calling code. Be careful with isolation levels and sp_executesql.

Check out the rest of our posts on SQL Server basics, and if you found this post worthwhile, please take a moment to share it with your friends and colleagues. Thanks!

Transaction Isolation Levels and sp_executesql

Example Script

Related

One Response

Leave a ReplyCancel reply

Search

Categories

Pages

Meta