Avoid Read-Write Blocking with Read Committed Snapshot Isolation

Writes don't block reads anymore!

In this article, we are going to take a look at how write operations block read operations under default isolation level - Read Committed and how to avoid this using the optimistic isolation level - Read Committed Snapshot.

Topics covered in this article:

• Demo of blocking session in Read Committed isolation level

• What is Read Committed Snapshot isolation level(RCSI)

• How to enable RCSI

• Demo of the same session, but without blocking in Read Committed Snapshot isolation level

Let's start with the demo of a blocking session in the default isolation level, Read Committed.

Demo - Read vs Write blocking session

1. Verify DBATest database isolation level - Read Committed.

   USE DBATEST;
   DBCC USEROPTIONS

2. On SSMS window, run an update statement on table dbo.t1 with begin tran, but do not commit yet.
   Result: Update ran but not committed yet.

   BEGIN TRAN
   UPDATE dbo.t1 SET b='b_0' WHERE id=1

   

3. In another SSMS window, run SELECT statement on the same table for id=1.
   Result: Does not retrieve result. SELECT is blocked by UPDATE.

   SELECT * FROM dbo.t1 WHERE id=1

4. Check sp_whoisactive and locks granted/waited from dmv sys.dm_tran_locks.
   Result: Read session 58 is waiting for S(shared) lock, it is blocked by update session 61 that has X(exclusive) lock. Since X lock is not compatible with any other lock, the read query cannot proceed.

sp_whoisactive 58  

--Locks granted/waited
SELECT request_session_id
	,resource_type
	,resource_description
	,request_type
	,request_mode
	,request_status
FROM sys.dm_tran_locks
WHERE request_session_id IN (58,61)

5. Issue commit in session 61 which has the update query, then X lock is released and read query is granted shared lock and retrieves the result.

What did we see:

• Read was blocked by write.

• You could use NOLOCK, so the read query doesn’t wait to get shared lock and reads the uncommitted data. Though people use it, that’s NOT the right thing to do. You could be reading data that is uncommitted, rolled back or duplicates and that is why it is called dirty reads.

• I don’t suggest using NOLOCK in your queries unless you don’t care about seeing wrong/missed data.

• The point I’m trying to make is, if you’re frequently experiencing reads being blocked by writes, which usually happens on a very busy OLTP system, then you can take advantage of ‘READ COMMITTED SNAPSHOT’ isolation level.

What is READ COMMITTED SNAPSHOT isolation level(RCSI)

• When RCSI is enabled, read queries can read the last committed data saved in version store in tempdb (assuming Accelerated Database Recovery is not enabled).

• Concurrently, writes can be done on that same table and reads won’t get blocked.

• RCSI affects only the behavior of the read operations. It has no impact on write operations.

• Blocking between write operations from different sessions still exists and there is no change in locking.

• If NOLOCK hint is used in queries under RCSI, then NOLOCK hint overrides the isolation level. That means, the query with NOLOCK hint reads uncommitted data from the table, instead of reading versioned rows from version store in tempdb.

Difference between Read Committed and Read Committed Snapshot isolation level

Basically, you’re telling SQL Server:

• Read Committed isolation level ⇒ “Read committed rows.”

• Read Committed Snapshot isolation level ⇒ “Read last committed rows from the snapshot,” saved in version store in tempdb when writes are happening on those rows.

How to enable RCSI

1. Verify the status of snapshot isolation and read committed snapshot in the database.
   Result: Value 0, means RCSI disabled.

   SELECT snapshot_isolation_state,is_read_committed_snapshot_on, name
   FROM sys.databases
   WHERE name ='DBATest'

   

2. To turn ON RCSI, active transactions need to finish. So, perform this during off-hours so there are not many active transactions. If there still happens to be any, set single user with rollback immediate.

   ALTER DATABASE DBATest 
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

3. Turn ON ALLOW_SNAPSHOT_ISOLATION. This helps in storing the row versions in tempdb, before write operations begin.

   ALTER DATABASE DBATest
   SET ALLOW_SNAPSHOT_ISOLATION ON  

4. Next, SET READ_COMMITTED_SNAPSHOT ON, so the queries can access the versioned rows in tempdb.

   ALTER DATABASE DBATest
   SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

5. Verify options are ON.
   Result: Snapshot isolation and read committed snapshot options are ON as expected.

   SELECT snapshot_isolation_state,is_read_committed_snapshot_on, name
   FROM sys.databases
   WHERE name ='DBATest'

   

6. If single_user mode was set earlier, set it back to multi_user.

   ALTER DATABASE DBATest 
   SET MULTI_USER WITH ROLLBACK IMMEDIATE 

Let’s run the same blocking demo again but in read committed snapshot isolation level

Demo - Read vs Write blocking session in RCSI

1. Verify DBATest database isolation level

SELECT snapshot_isolation_state,is_read_committed_snapshot_on, name, *
FROM sys.databases
WHERE name ='DBATest'

2. On a ssms window, run an update statement on table dbo.t1 with begin tran, but do not commit yet.
   Result: Update ran but not committed yet.

BEGIN TRAN
UPDATE dbo.t1 SET b='b_1' WHERE id=1

3. On another ssms window, run select statement on the same table for id=1.
   Result: Even though update is not committed, select statement still retrieves data without being blocked. This data is the last committed data, saved in version store in tempdb, as a result of read committed snapshot isolation level.
   In the previous demo with read committed isolation level, select was blocked since update was not committed yet.

SELECT * FROM dbo.t1 WHERE id=1

4. Issue commit in the update window to finish the transaction.

What did we see:

In RCSI, reads do not get blocked by writes unlike read committed isolation level.
Here, reads do not need S lock as they read the versioned rows in tempdb and that’s why they don’t get blocked by writes which has X lock.

Note:

• Though this isolation level helps in resolving (write vs read)blocking issues, it is necessary to address the root cause of the blocking scenarios, and eliminate it. To read more about addressing blocking issue, read Microsoft documentation.

• When RCSI is enabled, caution is needed regarding the increase in version store size, as it could cause tempdb to grow significantly due to long-running open transactions. I will discuss this further in the next article.

Summary

• Read Committed Snapshot Isolation (RCSI) allows read queries to access versioned data without being blocked by concurrent writes.

• Behavior of write operations remain the same. Blocking still exists between write operations. Only the behavior of reads change as they read from version store and as a result won’t get blocked by writes.

• RCSI helps reduce read-write blocking in busy OLTP systems, but it's crucial to address the root cause of blocking.

• Enabling RCSI involves changing database settings and there shouldn’t be any active transactions at the time of enabling RCSI.

• If NOLOCK hint is used in queries under RCSI, then NOLOCK hint overrides the isolation level.

Resources

• https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

Thanks for reading,
Haripriya.