Spreading The Magic Dust -SQL Server Row Versioning With Read Committed Snapshot Isolation Level

Working with many customers on very intense database\application tuning engagements in the past many times I had to go back to my “bag of magic dust” to provide for significant SQL Server backed performance enhancements under load without recoding application or queries. One of such items in many cases  was Read Committed Snapshot Isolation Level feature introduced while ago in SQL Server 2005.

So as of SQL Server 2005 and later versions, SQL Server provides two physical implementations of the read committed isolation level defined by the SQL standard, “classic locking” read committed and read committed snapshot isolation (RCSI).

What is transaction isolation level? Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions. A lower isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter. Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another. Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level.

Lets start with SQL Server default “classic locking” read committed isolation level. When SQL Server executes a statement at the read committed isolation level, it acquires short lived share locks on a row by row basis, although depending on operation, indexing, statistics and number of rows that need to be scanned lock can be escalated to page level or even table level. The duration of these share locks is just long enough to read and process data. .  The sole purpose of these locks is to ensure that the statement only reads and returns committed data, i.e. avoid uncommitted\”dirty” reads.  The locks work because updates always acquire an exclusive lock which blocks any readers trying to acquire a share lock.

This is well known and default behavior and most of the time it works just fine under load, however sometimes with heavy OLTP loads excessive read blocking is noted.

Enter – Read Committed Snapshot utilizing Row Versioning. When transactions running under row versioning-based isolation read data, the read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data. Also, the overhead of locking resources is minimized as the number of locks acquired is reduced. Read committed isolation using row versioning and snapshot isolation are designed to provide statement-level or transaction-level read consistencies of versioned data.

When Read Committed Snapshot is  on , logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in TempDB. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in TempDB.  So basically there is a version store located in TempDB that allows read queries to avoid shared locks and be served last committed value.

That’s whole bunch of theory, but how does it work in practice?  Lets test it out really quick.  First I am going to create a little database on my “legacy” SQL 2008 R2 instance:

CREATE DATABASE [IsolationTests] ON  PRIMARY 
( NAME = N'IsolationTests', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.GENNADYK_LEGACY\MSSQL\DATA\IsolationTests.mdf' , SIZE = 50MB , FILEGROWTH = 4MB )
 LOG ON 
( NAME = N'IsolationTests_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.GENNADYK_LEGACY\MSSQL\DATA\IsolationTests_log.ldf' , SIZE = 5MB , FILEGROWTH = 10%)
GO

Next I will create a little table and insert some data into it.

USE IsolationTests
GO
CREATE TABLE IsolationTest
(
  ID INT IDENTITY PRIMARY KEY,
  VALUE1 INT,
  VALUE2 INT,
  VALUE3 INT 
)
GO
INSERT INTO IsolationTest (VALUE1,VALUE2,VALUE3) VALUES (1,2,3)
GO
INSERT INTO IsolationTest (VALUE1,VALUE2,VALUE3) VALUES (4,5,6)
GO
INSERT INTO IsolationTest (VALUE1,VALUE2,VALUE3) VALUES (7,8,9)
GO
INSERT INTO IsolationTest (VALUE1,VALUE2,VALUE3) VALUES (10,11,12)
GO
INSERT INTO IsolationTest (VALUE1,VALUE2,VALUE3) VALUES (13,14,15)

Note I am not enabling Snapshot and currently will start transactions under default Read Committed “classic locking” isolation level. Here comes my uncommitted transaction that will require locks on update:

BEGIN TRAN  
UPDATE IsolationTest SET VALUE1 = 25  
--Simulate long running transaction with long wait
WAITFOR DELAY '00:00:30'  
ROLLBACK

Next I will generate read transaction that will be blocked here

SELECT * FROM dbo.IsolationTest

Finally I will see my read being blocked via DMV query like this:

select 
  wt.session_id as waiting_session_id,
  db_name(tl.resource_database_id)as DatabaseName,
  wt.wait_duration_ms,
  wt.waiting_task_address,
  tl.request_mode,
  (select substring (st.text,(er.statement_start_offset/2)+1,
    ((case er.statement_end_offset
      when -1 then datalength(st.text)
      else er.statement_end_offset
      end - er.statement_start_offset)/2)+1)
   from sys.dm_exec_requests as er
   cross apply sys.dm_exec_sql_text(er.sql_handle) as st
   where er.session_id=tl.request_session_id)
   as waiting_query_text,
 tl.resource_type,
 tl.resource_associated_entity_id,
 wt.wait_type,
 wt.blocking_session_id,
wt.resource_description as blocking_resource_description,
case when wt.blocking_session_id>0 then
(select st2.text from sys.sysprocesses as sp
  cross apply sys.dm_exec_sql_text(sp.sql_handle) as st2
   where sp.spid=wt.blocking_session_id)
else null
end 
as blocking_query_text
from sys.dm_os_waiting_tasks as wt join sys.dm_tran_locks as tl
on wt.resource_address =tl.lock_owner_address
where wt.wait_duration_ms>5000
and wt.session_id>50

And I can clearly see my read transaction blocked here:

image

Now lets setup snapshot on our database and change read isolation level to read committed snapshot from default read committed.

USE master
GO
ALTER DATABASE IsolationTests SET SINGLE_USER;
GO
ALTER DATABASE IsolationTests SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE IsolationTests SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE IsolationTests SET MULTI_USER;

Next lets check what isolation level is on our test database.

Use master
GO

SELECT name, is_read_committed_snapshot_on FROM sys.databases

Looking good:

image

Finally lets “rinse and repeat” previous exercise with long running transactional update and read operations. Here is result as far as blocking via same DMV query:

image

Magic? Well not so fast, there are serious consequences

For more see – http://blogs.technet.com/b/sql_server_isv/archive/2010/12/21/using-read-committed-snapshot-isolation-mini-lab.aspx, http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/, https://www.sqlpassion.at/archive/2014/01/21/myths-and-misconceptions-about-transaction-isolation-levels/,

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s