Isolation Levels Are Inherited

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!



Leave a Reply

Your email address will not be published. Required fields are marked *