By: Jared Westover | Updated: 2024-07-18 | Comments | Related: > Locking and Blocking
Problem
Without locking and blocking, disk-based tables would be in chaos. But are you suffering from excessive blocking? Users may gripe about a slow application or reports taking forever. If so, enabling a specific database option might speed up your queries. However, with everything good, there is a downside.
Solution
This article explores how enabling READ_COMMITTED_SNAPSHOT on your database might ease excessive blocking. First, we'll review an example of locking and blocking in a busy environment under the default Read Committed isolation level. Next, we'll look at how enabling a row-versioning isolation level reduces the number of blocked readers. By the end of this article, you'll be ready to test this feature in your environment today.
Locking and Blocking
Does your database suffer from excessive blocking? People sometimes confuse blocking with deadlocks, and I usually clarify the distinction. Blocking happens when one session holds a lock on an object (page, key, table), and another asks for a conflicting lock and must wait. On the other hand, as Greg Robidoux writes, a deadlock occurs when two processes try to update the same record or set of records, but the processing is done in a different order. This forces SQL Server to select one of the processes as a deadlock victim and roll back the statements.
Now, back to blocking. Imagine being at a grocery store checkout, and the person in front of you has a problem with their payment. That's how blocking works. They swear there is money in the account, but the card is declined. They block you until they figure out the problem, or you move all your groceries to another checkout.
By default, SQL Server uses Read Committed as the transaction isolation level, ensuring a session reads committed data. That's right, SQL is a pessimist at heart. When a query reads data, it acquires a shared lock on an object. People try to get around this using Read Uncommitted or the NOLOCK hint. You usually don't want your transactions running under Read Uncommitted. Also, the NOLOCK hint comes with its own issues.
Blocking in Action
Let's step through an example of locking and blocking in action. With the code below, I'll create a table, insert some values, and start a transaction in one session without a rollback or commit.
-- mssqltips.com
USE master;
IF DATABASEPROPERTYEX('BlockingDemo', 'Version') IS NOT NULL
BEGIN
ALTER DATABASE BlockingDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE BlockingDemo;
END;
GO
CREATE DATABASE BlockingDemo
ALTER DATABASE BlockingDemo SET RECOVERY SIMPLE;
GO
USE BlockingDemo;
GO
DROP TABLE IF EXISTS dbo.BlockingProblems;
CREATE TABLE dbo.BlockingProblems
(
Id INT,
FavoritePerson NVARCHAR(100)
);
GO
INSERT INTO dbo.BlockingProblems
(
Id,
FavoritePerson
)
VALUES
(1, 'Mom'),
(2, 'Dad'),
(3, 'Brother'),
(4, 'Sister'),
(5, 'Ant'),
(6, 'Uncle');
GO
BEGIN TRANSACTION;
-- Oh no, I spelled Aunt wrong!
UPDATE dbo.BlockingProblems
SET FavoritePerson = 'Aunt'
WHERE FavoritePerson = 'Ant';
Next, I'll perform a SELECT statement on my BlockingProblems table in another session. Notice the predicate in the WHERE clause differs from the other one I'm modifying in the first session.
-- mssqltips.com
-- Session #2
SELECT Id,
FavoritePerson
FROM dbo.BlockingProblems
WHERE FavoritePerson = 'Mom';
Since I'm using the default isolation level of Read Committed, my second query needs a shared lock on the object.
I'll open another session (#3) and execute Adam Machanic's sp_whoisactive to view the active locks.
-- mssqltips.com -- Session #3 EXECUTE sp_whoisactive @get_locks = 1;
The screenshot below illustrates that the first session holds an X (exclusive) lock on the RID or row ID. However, the lock blocking my second session is the IX (intent exclusive) on the object, a.k.a. table BlockingProblems.
If we create a nonclustered index on the FavoritePerson column, we end up in the same boat. This behavior might be how you want your database to function, where writers block readers or pessimistic concurrency control. On the other hand, if you would rather allow the reader to read the current committed value, there's an option for that.
Before moving on, let's roll back our first session, where the update took place. After the rollback, notice that the query in our second session completes quickly.
-- mssqltips.com -- Session #1 ROLLBACK TRANSACTION;
Enabling READ_COMMITTED_SNAPSHOT
Microsoft released READ_COMMITTED_SNAPSHOT (RCSI) with SQL Server 2005. It's a type of optimistic concurrency control that uses row-versioning. A major benefit of RCSI is that it doesn't require readers to obtain shared locks. This differs from NOLOCK in that NOLOCK reads dirty pages that have not been committed. On the other hand, RCSI reads the original committed value when multiple sessions are involved.
By default, SQL Server disables RCSI at the database level. However, an ALTER DATABASE turns it on. For our example, let's execute the code below.
-- mssqltips.com ALTER DATABASE BlockingDemo SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE; GO
Enabling RCSI can be tricky, so I'm using the WITH ROLLBACK IMMEDIATE option. This option rolls back any open transactions, so don't run it in a production system without thinking about the ramifications. Also, if you have long-running transactions, they may take a while.
Do I need to enable SNAPSHOT isolation as well? The answer is no. RCSI and SNAPSHOT isolation are different. One core difference is that SNAPSHOT isolation provides transaction-level read consistency, while RCSI gives it at the statement level. Additionally, once you enable RCSI, you don't need to change your code to see it in action. This might be good or bad, depending on your environment.
Since we enabled RCSI, let's reproduce the situation above.
-- mssqltips.com -- Session #1 BEGIN TRANSACTION; -- Oh no, I spelled Aunt wrong! UPDATE dbo.BlockingProblems SET FavoritePerson = 'Aunt' WHERE FavoritePerson = 'Ant';
Over in our second session, let's perform the SELECT statement.
-- mssqltips.com
-- Session #2
SELECT Id,
FavoritePerson
FROM dbo.BlockingProblems WHERE FavoritePerson = 'Mom';
Results:
Id FavoritePerson ---- ----------------- 1 Mom
The second session was not blocked and returned our results.
Downside
The most notable downside is the potential to read invalid or outdated data. In the example above, we changed the spelling of a word for record 5. What if someone queries the original table to retrieve record 5? Can you guess what value shows up?
-- mssqltips.com
-- Session #2
SELECT Id,
FavoritePerson
FROM dbo.BlockingProblems
WHERE Id = 5;
Results:
Id FavoritePerson ------- ---------------- 5 Ant
That's right, SQL returns the original record with the misspelling. Even with RCSI enabled, you can enforce shared locks at the table level using the hint READCOMMITTEDLOCK.
-- mssqltips.com
SELECT Id,
FavoritePerson
FROM dbo.BlockingProblems (READCOMMITTEDLOCK)
WHERE Id = 5;
The statement above is blocked while waiting for a shared lock, as if we never enabled RCSI.
Clean Up
Before we end, let's remove the open transaction and drop the demo database.
-- mssqltips.com
ROLLBACK TRANSACTION;
GO
USE master;
IF DATABASEPROPERTYEX('BlockingDemo', 'Version') IS NOT NULL
BEGIN
ALTER DATABASE BlockingDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE BlockingDemo;
END;
GO
Summary
My aim wasn't to say you should always enable RCSI. Think of it as another tool in your toolbox. When your environment functions as a hybrid OLTP and reporting system, it may benefit from an optimistic locking model. If you adopt RCSI, test aggressively before moving it into production. When T-SQL transactions rely on shared locks, you might explore enabling snapshot isolation. Finally, review the Microsoft documentation before taking the plunge.
In the comments, please share your experience with RCSI. I would also love to hear what's keeping you from enabling it.
Key Points
- Are you planning to move to Azure with a SQL database or a managed instance? Consider testing RCSI on your current databases. Remember, RCSI is enabled by default in Azure.
- By default, SQL Server uses the pessimistic isolation level of Read Committed. One of the primary benefits of Read Committed is that readers must acquire a shared lock before reading, meaning you read committed data.
- Enabling RCSI on a database removes the requirement for readers to acquire a shared lock. This can reduce the amount of blocking in a system. However, it could also lead to reading outdated data. Even with RCSI enabled, writers can still block writers.
Next Steps
- On BrentOzar.com, Kendra Little wrote an excellent guide to enabling RCSI titled Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide. I highly recommend checking it out.
- Are you trying to determine the difference between RCSI and snapshot isolation? Then check out Sergey Gigoyan's article, READ_COMMITTED_SNAPSHOT and SNAPSHOT_ISOLATION levels in SQL Server.
- Kendra Little wrote an article to help you decide between RCSI and snapshot isolation titled, HOW TO CHOOSE BETWEEN RCSI AND SNAPSHOT ISOLATION LEVELS.
About the author
Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-07-18
