Haripriya Naidu gets optimistic:
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.
I do wish that this were the default for new databases in SQL Server, like it is in Azure SQL Database.
H/T Brent Ozar’s newsletter for pointing me in Haripriya’s direction.
“I do wish that this were the default for new databases in SQL Server,”
It is if you change the Model Database
But unless you have a high-contention DB, Locking is much faster, so, use judiciously
You’re certainly correct that you can make it the default. But I mean the default for new installations, as in many companies, there isn’t someone like you who knows what they’re doing and just accepts the defaults without knowing there’s another option. Then, developers experience locking problems and decide to do the NOLOCK shuffle because NOLOCK makes things faster. Then they finally bring in someone with database experience, who now has to fight upstream to convince the risk-averse company that RCSI is a better option, that it won’t cause all kinds of data problems (ironic, considering the use of NOLOCK everywhere), etc. And then they have to clean up the NOLOCK mess before RCSI even benefits them.
Skip all of those steps and jump straight to RCSI. I can understand that, with slow tempdb, read committed can be better, but I think reasonably fast storage plus the inevitability of blocking chains on any non-trivial database makes RCSI a good default. There can be scenarios where RCSI isn’t a good choice, so it’s good to have the alternative of going back to pessimistic locking, but on non-trivial databases, blocking chains are a way of life. And for trivial databases, it doesn’t really matter much either way.