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.
Please Consider Subscribing
