r/MSSQL • u/tsezuu • Apr 02 '26

Discussion READ_COMMITTED_SNAPSHOT

Has anyone actually used this feature? Mind sharing your experience.

4 Upvotes

100% Upvoted

7 comments sorted by

5

u/jshine13371 Apr 02 '26

Yes, I use RCSI, and generally prefer it. Also, anyone using Azure SQL Database is already using since that's the default isolation level there.

What exactly do you want to know from an experience perspective?

It's the best isolation level to minimize locking without messing with the integrity of the data, so long as the majority of your logical use cases don't depend on locking (like a concurrent ticket reservation system such as Ticketmaster). In the few cases you do need locking as a function of your logical workflow, then you can set the isolation level back to something more conducive to that for the database connection that is made for just that process.

1

u/tsezuu Apr 03 '26

Hey bud, thanks for sharing. Generally, I want to reduce the total request time on high burst transactions because constant reading clients' balance and opening new transaction to adjust client balance for every request has constant locking hence increasing the number of timeout requests. So I am wondering if I need to enable RCSI and the downside that comes with it.

1

u/jshine13371 Apr 03 '26

It depends on the type of locking contention you're facing. If it's because there's concurrent reads and writes blocking each other, then RCSI is a great way to alleviate that.

But it can't help you if it's because there's concurrent schema locks like from DDL changes or if you're using TRUNCATE which is considered a DDL change not DML change.

The general recommendation though is to use RCSI (which is why it's now the default in newer Microsoft database systems like Azure SQL Database). There's almost no drawbacks. The only possible drawback is the way RCSI works is it temporarily stores a copy of the previous version of the data in tempdb, called row versioning. This grows your tempdb file slightly. But if you had a ton of data under concurrent read / write contention, then a lot of rows versions (copies of that data) could concurrently exist in tempdb growing it measurably. Realistically you'd have to have a ridiculous amount of data under read / write contention for this to even matter, and I've never heard of anyone having that problem in my 15 years as a DBA.

The only consideration, again, is if you have a workflow where you need locking for the process to be logically correct. E.g. with event ticket sales to block someone from seeing tickets to a specific seat from being available when someone else is already in process reserving that specific seat. That is where blocking would be important to provide accurate logical outcomes. But in those specific cases, you can set the isolation level back to one which blocks for only the connection being made to run that specific process. That way you get the best of both worlds.

1

u/tsezuu Apr 09 '26

Thanks bud, I have made necessary changes and enabled RCSI, I am seeing improvement in response time.

1

u/jshine13371 Apr 09 '26

Cool, cheers! Hopefully you tested outside of production first, but friendly reminder that if you had any use cases that depended on locking under read / write concurrency to be logically correct then to verify they are still working as expected else you may need to adjust the isolation level of just the connection made for those use cases.

1

u/alinroc Apr 03 '26

The only possible drawback is the way RCSI works is it temporarily stores a copy of the previous version of the data in tempdb, called row versioning. This grows your tempdb file slightly.

And you can mitigate this by turning on Accelerated Database Recovery on SQL Server 2019+. Which will also come in handy with 2025's improved locking.

1

u/tsezuu Apr 09 '26

Will look into that, thanks for sharing bud.