Read Committed Snapshot Isolation Level

Read Committed Snapshot Isolation Level in SQL Server

In this article, I will discuss the Read Committed Snapshot Isolation Level in SQL Server with Examples. Please read our previous article discussing ALLOW_SNAPSHOT_ISOLATION in SQL Server with Examples. The Read Committed Snapshot Isolation Level in SQL Server is not at all a different Kind of isolation level. It is another way to implement the Read Committed Isolation Level in SQL Server. 

Read Committed Snapshot Isolation Level (RCSI) in SQL Server

The Read Committed Snapshot Isolation (RCSI) level in SQL Server is a variation of the Read Committed Isolation Level that uses row versioning to provide a consistent view of the database when the query starts. This differs from the Read Committed Isolation Level, which locks rows as they are read to prevent other transactions from modifying until the current transaction is complete. Read Committed Snapshot Isolation (RCSI) aims to reduce locking and blocking by allowing transactions to read historical versions of rows rather than waiting for locks to be released.

How Read Committed Snapshot Isolation Level (RCSI) Works:
  • Row Versioning: When Read Committed Snapshot Isolation Level (RCSI) is enabled, SQL Server maintains versions of each row in a hidden version store in the tempdb database. Whenever a row is modified, a new version of the row is created and stored in this version store. The original row is updated or deleted in the main database.
  • Consistent View: When a transaction reads data under RCSI, it sees the version of the rows as they existed at the beginning of the transaction. This is achieved by reading the row versions that were committed before the transaction started, ensuring a consistent view of the data without being affected by concurrent modifications.
  • Non-blocking Reads: Since transactions under RCSI do not acquire shared locks on the data they read, other transactions can modify the data concurrently. This reduces locking contention and improves system concurrency and performance.
Example to Understand Read Committed Snapshot Isolation Level in SQL Server:

Let us understand the Read Committed Snapshot Isolation Level in SQL Server with an example. We are going to use the following Products table.

Read Committed Snapshot Isolation Level in SQL Server

Please use the SQL script below to create and populate the Products table with the required data.

CREATE DATABASE ProductDB;
GO

USE ProductDB;
GO

-- Create Products table
CREATE TABLE Products
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Quantity INT
)
Go

-- Insert test data into Products table
INSERT INTO Products values (1001, 'Mobile', 10)
INSERT INTO Products values (1002, 'Tablet', 20)
INSERT INTO Products values (1003, 'Laptop', 30)
Example using Read Committed Transaction Isolation Level:

Let us first see an Example using the Read Committed Transaction Isolation Level and observe the transaction execution. Then, we will rewrite the same example using the Read Committed Snapshot Isolation Level in SQL Server.

First, open two instances of SQL Server Management Studio. From the first instance, execute the Transaction 1 code, and from the second instance, execute the Transaction 2 code, and you will notice that Transaction 2 is blocked until Transaction 1 completes its execution.  

--Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

  UPDATE Products SET Quantity = 5 WHERE Id = 1001
  WAITFOR DELAY '00:00:15'

COMMIT TRANSACTION

--Transaction 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
  SELECT * FROM Products WHERE Id = 1001	
COMMIT TRANSACTION

As you can see here, we are using the Read Committed Transaction Isolation Level, and you can also observe that Transaction 2 is blocked until Transaction 1 completes its execution.

Let’s see how to enable Transaction 2 to use the row versioning technique provided by SQL Server instead of the locking mechanism we have seen in the previous example by using the Read Committed Transaction Isolation Level. To use the Row Versioning Technique, we must enable the Read Committed Snapshot Isolation at the database level.

How Do We Enable READ_COMMITTED_SNAPSHOT Isolation Level in SQL Server?

Please execute the following code to enable the READ_COMMITTED_SNAPSHOT Isolation Level in SQL Server. Please close all the opened SQL Server Management Studio Instances or all the database connections except the one where you want to execute the below code; otherwise, it will not complete its execution.

ALTER DATABASE ProductDB SET READ_COMMITTED_SNAPSHOT ON

Do We Need to Add SET Transaction Statement in SQL Server for Read Committed Snapshot Isolation Level?

In SQL Server, when you use the Read Committed Snapshot Isolation (RCSI) level, you do not explicitly need to use the SET TRANSACTION ISOLATION LEVEL statement in your session to enable RCSI. This is because once RCSI is enabled at the database level, it automatically applies to all transactions that use the default Read Committed isolation level. This means all the standard SELECT statements will use snapshot isolation to read row versions without explicitly setting the isolation level in each session or transaction.

Now, open two instances of SQL Server Management Studio. From the first instance, execute the Transaction 1 code, and from the second instance, execute the Transaction 2 code, and you will notice that Transaction 2 is not blocked until Transaction 1 completes its execution.  It is executed immediately and returns the committed data that is present in the database before Transaction 1 starts. This is because Transaction 2 is now using the Read committed snapshot Isolation Level. 

--Transaction 1
BEGIN TRANSACTION
  UPDATE Products SET Quantity = 5 WHERE Id = 1001
  WAITFOR DELAY '00:00:10'
COMMIT TRANSACTION

--Transaction 2
BEGIN TRANSACTION
  SELECT * FROM Products WHERE Id = 1001 
COMMIT TRANSACTION
Benefits of RCSI
  • Improved Performance: By reducing locking contention, RCSI can significantly improve the performance of a database system, especially in high-concurrency environments.
  • Reduced Blocking: Applications experience fewer delays due to blocking, as read operations do not block write operations and vice versa.
  • Consistent Data View: Transactions see a consistent snapshot of the database as of the start of the transaction, which simplifies application development by reducing the need to handle read inconsistencies.
Considerations of RCSI
  • TempDB Usage: Read Committed Snapshot Isolation Level (RCSI) increases the load on the tempdb database because of the row versioning mechanism. Sufficient disk space and performance tuning of tempdb are essential.
  • Long-Running Transactions: Since each transaction needs to see the data as it was at the start of the transaction, long-running transactions can lead to increased version store usage, potentially impacting system performance.
Differences Between Read Committed and Read Committed Snapshot Isolation Level in SQL Server

In SQL Server, both the Read Committed (RC) and Read Committed Snapshot Isolation (RCSI) levels aim to provide a stable view of the database by preventing dirty reads. However, they implement this in different ways, leading to differences in how transactions are handled, performance implications, and concurrency behavior. Here’s a detailed comparison of the two:

Locking vs. Row Versioning
  • Read Committed (RC): This is the default isolation level in SQL Server. It uses locks to prevent dirty reads. When a transaction reads data, shared locks are placed on the data, preventing other transactions from modifying it until the read is complete. However, this can lead to blocking, where one transaction must wait for another to release its locks.
  • Read Committed Snapshot Isolation (RCSI): Instead of using locks, RCSI uses row versioning. When this option is enabled at the database level, SQL Server automatically creates a version of a row before it is modified. When a transaction reads data, it sees the most recent committed version of the row as of the start of the transaction, thereby avoiding the need to wait for locks placed by other transactions.
Blocking Behavior
  • RC: Transactions may block each other due to shared and exclusive locks. For example, if one transaction is reading data (with shared locks), another transaction that wants to modify the same data (requiring exclusive locks) must wait until the first transaction completes and releases its locks.
  • RCSI: Reduces blocking because readers do not block writers and vice versa. Transactions read the last committed version of the data available at the time their transaction started, allowing updates to proceed without waiting for readers to finish.
Concurrency
  • RC: Provides a consistent view of the database by ensuring that only committed data is read. However, it can suffer from concurrency issues due to blocking.
  • RCSI: It also ensures that only committed data is read but improves concurrency by allowing multiple transactions to access the same data simultaneously without blocking or using row versioning.
TempDB Usage
  • RC: It does not use versioning, so there is no additional storage overhead in tempdb for version stores.
  • RCSI: Uses row versioning, which requires storage in tempdb for the versioned rows. This can lead to increased disk space usage and potentially affect tempdb performance.
Impact on Application Behavior
  • RC: Applications that are sensitive to blocking and concurrency issues may experience delays, especially under heavy load.
  • RCSI: This can significantly improve the performance of read-intensive applications by reducing locking contention. However, applications must be designed to handle the possibility of reading slightly stale data (the last committed state at the start of the transaction), which is usually acceptable for many business requirements.
Configuration
  • RC: Enabled by default and does not require any special configuration at the database level.
  • RCSI: Must be explicitly enabled at the database level using the ALTER DATABASE command. It’s a global setting that affects all transactions in the database.

In the next article, we will discuss the Differences Between Snapshot Isolation and Read Committed Snapshot in SQL Server with examples. In this article, I explain the Read Committed Snapshot Isolation Level in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, questions, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *