SQLTeam.com | Weblogs | Forums

READ_COMMITTED_SNAPSHOT and WITH(NOLOCK) at the same time


#1

Hi All,
We've high transaction databases which many writes and reads occurs at the same time.
Also some poorly designs leads us to deadlocks. To avoid those also made another design mistake... using WITH(NOLOCK). As written all over the internet that doesn't solve our problems and leads to other ones.
So what we have here is, we hundreds of thousands of WITH(NOLOCK) hints written inside views, functions, procedures, executables, php files... So it's nearly impossible (or lets say takes months) to get rid of them. I was reading about RCSI for a couple of days and it was used happily by HightTransactionDB Owners and seems like side effects are less then WITH(NOLOCK). And here comes the question:

What happens if I use WITH(NOLOCK) hint (which we can't change the query codes, takes months) with the RCSI?
Does normal behavior or WITH(NOLOCK) hint continues or it's just ignored because DB is RCSI now?

Thanks for your helps.

Note: SQL Server 2016


#2

As I understand it, RCSI applies only when you are in READ_COMMITTED isolation level. Since NOLOCK would place that table into READ_UNCOMMITTED isolation level, I believe it would be different. Specifically, NOLOCK could see mods that occurred after the start of the transaction, whereas RCSI would not.