Isolation Levels Are Inherited In SQL Server Queries

Posted on by Erik Darling

Client Yell

While working with a client who has a nasty blocking problem involving triggers, we were talking about using Snapshot Isolation to help fix the issue.

The thing is, there’s more than just stored procedures causing problems. Some of the issue is with triggers.

The prospect of having to modify a bunch of objects was a rightfully frightful one.

Funboy & Funboy & Funboy

In my database, I have Snapshot Isolation enabled.

I have a table with a trigger on it. The trigger doesn’t do anything except show me what my session’s isolation level is

CREATE TABLE dbo.t ( id INT NULL, dimdate DATETIME NULL );
CREATE TRIGGER dbo.dim_trigger
ON dbo.t
AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @UserOptions TABLE ( SetOption VARCHAR(100), Value VARCHAR(100));
        DECLARE @IsolationLevel VARCHAR(100);

        INSERT @UserOptions
        EXEC ( 'DBCC USEROPTIONS WITH NO_INFOMSGS' );

        SELECT @IsolationLevel = 'Trigger:' + Value
        FROM   @UserOptions
        WHERE  SetOption = 'isolation level';

        PRINT @IsolationLevel;

    END;

I also have a stored procedure that checks the isolation level, and inserts into my table so that the trigger fires off.

CREATE OR ALTER PROCEDURE dbo.insert_t
AS
    BEGIN
        SET NOCOUNT ON;

        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

        DECLARE @UserOptions TABLE ( SetOption VARCHAR(100), Value VARCHAR(100));
        DECLARE @IsolationLevel VARCHAR(100);

        INSERT @UserOptions
        EXEC ( 'DBCC USEROPTIONS WITH NO_INFOMSGS' );

        SELECT @IsolationLevel = 'Procedure:' + Value
        FROM   @UserOptions
        WHERE  SetOption = 'isolation level';

        PRINT @IsolationLevel;

        INSERT dbo.t ( id, dimdate )
        VALUES ( 0, GETDATE());

    END;

On Notice

I’m only setting the isolation level in the stored procedure here.

When I run it, both will return the same isolation level no matter what you set it to.

Consistency

Thanks to Scott Ivey, whose code I borrowed for this.

And thank you for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts