Snapshot isolation level when database is set to read only.

Recently there was a question posted on #sqlhelp about why a database switched to Snapshot isolation level when you make it read only and then reverts back to read committed mode when its switched back to read write.

You can use the below script to recreate the behavior being described

IF EXISTS (
        SELECT 1
        FROM sys.databases
        WHERE name = 'example1'
        )
    DROP DATABASE example1
    GO

CREATE DATABASE example1 GO

ALTER DATABASE example1

SET recovery FULL GO

SELECT name
    ,snapshot_isolation_state_desc
    ,recovery_model_desc
    ,is_read_only
FROM sys.databases
WHERE name = 'example1' GO

ALTER DATABASE example1

SET READ_ONLY GO

SELECT name
    ,snapshot_isolation_state_desc
    ,recovery_model_desc
    ,is_read_only
FROM sys.databases
WHERE name = 'example1' GO

ALTER DATABASE example1

SET read_write GO

SELECT name
    ,snapshot_isolation_state_desc
    ,recovery_model_desc
    ,is_read_only
FROM sys.databases
WHERE name = 'example1' GO

ALTER DATABASE example1

SET recovery SIMPLE GO

ALTER DATABASE example1

SET READ_ONLY GO

SELECT name
    ,snapshot_isolation_state_desc
    ,recovery_model_desc
    ,is_read_only
FROM sys.databases
WHERE name = 'example1' GO

ALTER DATABASE example1

SET read_write GO

SELECT name
    ,snapshot_isolation_state_desc
    ,recovery_model_desc
    ,is_read_only
FROM sys.databases
WHERE name = 'example1' GO

SELECT *
FROM sys.fn_dblog(NULL, NULL)

This issue can be replicated on other versions of SQL Server as well and we can see it doesn’t really have to do with the t log management part of it. I did notice that this behavior didn’t seem to occur for a database in which I have implemented query store.

So why is this question important?

If the database is in read_only mode why does it change to snapshot isolation level? What is the advantage? For this we need to understand what Snapshot isolation level does. When a database has snapshot isolation level enabled the database engine starts implementing row versioning and moves pages that need to be updated to the version store in tempdb. But for a database that is read only there will be no DML so there won’t be any row versions created. In other words Enabling snapshot isolation level doesn’t really cause any row version to be generated thus making the behavior of row versioning defunct.

So clearly we don’t get any advantaged due to that behavior so maybe it has to do it the fewer number of locks that are required.

To simulate the locking behavior try running the below script using the different modes read_write and Read_only.

USE example1

BEGIN TRAN

DECLARE @counter INT = 0

WHILE @counter < = 1000
BEGIN
    SELECT *
    FROM abcd
    WHERE id = @counter

    SET @counter = @counter + 1
END

COMMIT TRAN

While the above query is executing run the below query in another window.

SELECT db_name(resource_database_id)
    ,*
FROM sys.dm_tran_locks

The below screenshots show the locks acquired in read_write and read_only mode.

Read_Write – As expected we acquire page level locks here to ensure ACID


Read_only- Here we see we still acquire a HOBT BULK Operation lock which can be avoided by putting a clustered index on the table as shown in the next step.


Read_only with clustered index – Notice we no longer acquire any locks on individual pages on HOBT allocations like partitions etc.

Naturally this makes lock management a lot easier. So there is an advantage to having the read only database running in snapshot isolation level.