ReadCommitted (Snapshot) Huh.. – SQL Server (Azure) DBA

Note that Read Committed Snapshot is not an Isolation Level just an added behavior of using a snapshot of a record in case of an DML statement, here is a query to check it… good luck!

Readers don’t block Writers and Writers don’t block Readers .. be careful Writers block Writers and when data comes from the RowVersion is it ok what you do?

use StackOverflow2013
go

-- Off
alter database StackOverflow2013 
set read_committed_snapshot OFF 
with rollback immediate;

/*
READ_COMMITTED_SNAPSHOT is *not* isolation level, it is a database's option permitting 
to change the behavior of ReadCommitted isolation level database-wide.
Snapshot is RowVersioning.
*/

SELECT CASE  
          WHEN transaction_isolation_level = 1 
             THEN 'READ UNCOMMITTED' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 1 
             THEN 'READ COMMITTED SNAPSHOT' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED' 
          WHEN transaction_isolation_level = 3 
             THEN 'REPEATABLE READ' 
          WHEN transaction_isolation_level = 4 
             THEN 'SERIALIZABLE' 
          WHEN transaction_isolation_level = 5 
             THEN 'SNAPSHOT' 
          ELSE NULL
       END AS TRANSACTION_ISOLATION_LEVEL 
FROM   sys.dm_exec_sessions AS s
       CROSS JOIN sys.databases AS d
WHERE  session_id = @@SPID
  AND  d.database_id = DB_ID();

-- TRANSACTION_ISOLATION_LEVEL 
-- -> READ COMMITTED

/* On */
alter database StackOverflow2013 
set read_committed_snapshot ON
with rollback immediate;

SELECT CASE  
          WHEN transaction_isolation_level = 1 
             THEN 'READ UNCOMMITTED' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 1 
             THEN 'READ COMMITTED SNAPSHOT' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED' 
          WHEN transaction_isolation_level = 3 
             THEN 'REPEATABLE READ' 
          WHEN transaction_isolation_level = 4 
             THEN 'SERIALIZABLE' 
          WHEN transaction_isolation_level = 5 
             THEN 'SNAPSHOT' 
          ELSE NULL
       END AS TRANSACTION_ISOLATION_LEVEL 
FROM   sys.dm_exec_sessions AS s
       CROSS JOIN sys.databases AS d
WHERE  session_id = @@SPID
  AND  d.database_id = DB_ID();

-- TRANSACTION_ISOLATION_LEVEL
-- -> READ COMMITTED SNAPSHOT

Geef een reactieReactie annuleren