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:
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
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:
Magic? Well not so fast, there are serious consequences
- TempDB overhead. With version store in TempDB it could fill or you may see serious throughput bottlenecks. Make sure you follow all of the proactive best practices on TempDB as far as number of files, storage, etc. Otherwise you may be trading one bottleneck for another.
- Bad Hints In your code still override database isolation level.
- Writers still block writers in Read Committed Snapshot Isolation (RCSI), and depending on how you’ve written your transactions, this could change results in your application. See here – http://www.brentozar.com/archive/2014/12/read-committed-snapshot-isolation-writers-block-rcsi/
- Not all workloads will work with this feature see – http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/
- The phenomenon first described by Craig Freeman many years ago on SQL Server 2000 for old “locking” default Read Committed still valid here – http://blogs.msdn.com/b/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx
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/,