Fixing locking and blocking issues in SQL Server - Part 2 - RCSI

Fixing locking and blocking issues in SQL Server - Part 2 - RCSI

This is part 2 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue

In part 1, I talked about pseudo locking and blocking issues that come from having queries running too long. I said that before you start fixing any locking and blocking issues, that you first need to make sure your queries are running fast. Today I want to discuss RCSI.

Lock escalation issues

Some queries, however, just need to run for a long time.

[caption id=“attachment_4892” align=“alignnone” width=“297”]Awesome image by JC Gellidon Awesome image by JC Gellidon[/caption]

Most of the time, I find this with reporting queries. They’re just doing a lot of work.

The problem is that even in the default isolation mode (read committed), SQL Server takes shared locks. When it gets to 5000 of these locks, it tries to escalate to a table lock. (Strictly, it might be a partition lock but that’s a topic for another day)

And that’s where the fun begins. Many people have had the experience of working with large transaction tables; someone’s running a report down the end of the table and someone else is trying to modify the beginning of the table and gets blocked. Suddenly, you can’t even insert current data.

No the reaction that people had to this in SQL Server 2000 days, was to just put NOLOCK on the queries. But that’s not a great solution. You are basically giving up consistency for performance. It can lead to scenarios that are incorrect, and hard to explain.

Ever since SQL Server 2005, so many of these issues would be better fixed by using snapshot isolation level. Changing that in a session means changing the application, so for many customers, that’s not helpful.

One possible solution

Instead of changing the application to use snapshot isolation level, SQL Server provides a database option called read-committed snapshot isolation (RSCI) that changes any read-committed queries into read-committed-snapshot queries i.e. it automagically applies snapshot isolation to read-committed queries, but just for the duration of the queries.

I’ve lost count of how many sites I’ve been to where just applying that database option fixed an enormous number of blocking issues.

What it means is that when you go to read data, and someone else is modifying it in a transaction, you see the data as it was before the transaction started, instead of waiting. Generally, that’s awesome.

So why isn’t it already on?

If it’s such a great option, you might be wondering why it’s not already turned on. Basically, that’s for backwards compatibility. Some applications (I’ve seen very few of these) depend upon the old behavior. I’d suggest that these would be pretty badly written applications.

When we start new database applications today, we turn on RCSI for the database before doing anything else.

The other reason that it’s not a default value is that it is going to slightly increase the impact on tempdb. Generally, we’ve not found that to be an issue.

In Part 3 of this series of posts, we’ll move on to trickier situations.

2019-04-25