Hi All,
Want to know what are the practical implication we might run into when we have NOLOCK hints defined inside the stored procs and RCSI enabled at database level. Any short working example to demonstrate the why we need to stop using NOLOCK when RCSI is turned on? and some say, if you really want use nolock, better to go SET TRANSACTION LEVEL READ UNCOMMITTED, whats the difference?
Regards,
Sam
October 19, 2023 at 10:52 am
Do not use it - period.
see https://www.brentozar.com/archive/2021/11/nolock-is-bad-and-you-probably-shouldnt-use-it/
if you truly have to use it and the business has signed off on allowing potential bad data then use the Transaction level.
NOLOCK vs SET TRANSACTION LEVEL READ UNCOMMITTED
NOLOCK is done in EACH individual table in ALL SQL Statements on your code.
"SET TRANSACTION LEVEL READ UNCOMMITTED" is done either/both at the start of your code (e.g. at the top of your queries or your stored proc) or the code transaction definition
so the benefit is that with transaction level you need to define it on a single place, making it easier to remove/change when required.
final effect is the same - potential bad data.
as for using nolock when RCSI is in place - RCSI should be used with read committed isolation level (which is be default level) - and RCSI basically means that readers do not block writers and writers do not block readers - in a way it works like NOLOCK in that it is not blocked by the fact that someone is locking a row/page and instead reads the data that IS CURRENTLY on the database e.g. already committed.
nolock means that you will read ANY data even if not yet committed - or you will get missing/doubled data in some cases.
October 19, 2023 at 3:18 pm
NOLOCK makes sense in very limited cases. For example, for lookup tables, such as a table of state codes. There's zero reason to do locking when reading that table.
The big danger with NOLOCK is truly "dirty" data, such as data that was UPDATEd, you read it, then it gets rolled back (thus you read data that never really existed!). So, yes, be especially wary of using NOLOCK if you do a significant number of roll backs; most systems don't.
Since you should not be using on every SELECT / table, then you should not be using READ UNCOMMITTED, as that would make it apply to every SELECT and every table.
With RCSI on, NOLOCK should be used much less often, since you can get data from tempdb without any blocking anyway. NOLOCK would bypass RCSI.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
When you use a table hint you are overriding the default isolation on that table - so if you have RCSI enabled at the database level and use NOLOCK (read uncommitted) on the table you are telling SQL Server to ignore the default isolation and use read uncommitted for that table.
If you set the isolation level - then you are telling SQL Server to ignore the database default and use the isolation you specify instead. For example, using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of your procedure - then all tables accessed in that procedure will be accessed using read uncommitted.
If all of your code has NOLOCK - then enabling RCSI and not removing NOLOCK is just adding overhead for now useful purpose. SQL Server will be maintaining the version store - but it won't be used, at least not for any select statements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 20, 2023 at 5:37 am
If all of your code has NOLOCK - then enabling RCSI and not removing NOLOCK is just adding overhead for now useful purpose. SQL Server will be maintaining the version store - but it won't be used, at least not for any select statements.
Jeff,Uffff...
Is this true? RCSI is just overhead when NOLOCK is present? please confirm.
The vendor app code has NOLOCK all over the places for almost 10 years even before I joined this org(3 years back). now, RCSI is also enabled and we still see a lot of blocking. This could be one of the reasons I guess. I told the app team to remove the NOLOCK from the code but there were resistant as they have to remove in all parts of code and its time consuming. They are just looking for a magic button to remove all the blocking. Now, looks like it makes sense why they are getting blocking. Other problem is with the app, they open implicit txns and do a lot of work and never close the connection. That's why, I put this in this forum so that if I get full clarity on this behavior with a working example, then it will be easy to convince the app team.
Regards,
Sam
October 20, 2023 at 3:38 pm
The vendor app code has NOLOCK all over the places for almost 10 years even before I joined this org(3 years back). now, RCSI is also enabled and we still see a lot of blocking. This could be one of the reasons I guess. I told the app team to remove the NOLOCK from the code but there were resistant as they have to remove in all parts of code and its time consuming. They are just looking for a magic button to remove all the blocking. Now, looks like it makes sense why they are getting blocking. Other problem is with the app, they open implicit txns and do a lot of work and never close the connection. That's why, I put this in this forum so that if I get full clarity on this behavior with a working example, then it will be easy to convince the app team.
Combining RCSI and nolock is useless, nothing will be gained.
From your comment, the application has problems that are far beyond anything that can be done in SQL administratively. The code has to be fixed, there is no locking mechanism that can fix it.
October 20, 2023 at 4:26 pm
Jeffrey Williams wrote:If all of your code has NOLOCK - then enabling RCSI and not removing NOLOCK is just adding overhead for now useful purpose. SQL Server will be maintaining the version store - but it won't be used, at least not for any select statements.
Jeff,Uffff...
Is this true? RCSI is just overhead when NOLOCK is present? please confirm.
The vendor app code has NOLOCK all over the places for almost 10 years even before I joined this org(3 years back). now, RCSI is also enabled and we still see a lot of blocking. This could be one of the reasons I guess. I told the app team to remove the NOLOCK from the code but there were resistant as they have to remove in all parts of code and its time consuming. They are just looking for a magic button to remove all the blocking. Now, looks like it makes sense why they are getting blocking. Other problem is with the app, they open implicit txns and do a lot of work and never close the connection. That's why, I put this in this forum so that if I get full clarity on this behavior with a working example, then it will be easy to convince the app team.
Regards,
Sam
re: "there were resistant as they have to remove in all parts of code and its time consuming"
a global replace on their source code within Visual Studio... (THEY DO HAVE IT DON'T THEY!!!!!) takes 2 minutes to do - deployment is what take more time.
and nothing preventing them from doing it on the more common SP's and Views and go from there - it does not need to be a ALL or Nothing approach.
October 20, 2023 at 6:49 pm
They are just looking for a magic button to remove all the blocking.
Seriously? If there was such a magic button, don't you think MS would have provided it? Tell that to your devs.
I feel for you. I have fought this battle over and over with developers. And developers wonder why DBA's are so grumpy...
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply