May 9, 2013 at 9:09 pm
I'm looking for some help clarifying what effect NOLOCK will have on the SPROCS we run on our SQL 2008 server please.
1. We are not committing data via transactions or rolling it back. Just simple inserts/updates/deletes.
a. If a user inserts, deletes or updates data, and then SELECTS from the table using NOLOCK, will that change be immediately available?
b. If not, how does one tell that the change has been applied, or force it to be applied?
c. If yes, will the change be consistently available to all subsequent SELECTS using NOLOCK?
2. Is this true: If people are selecting from a table with NOLOCK, others could (sequentially) insert, update or delete at the same time - the reads may pick up some or all of the changes. It will be faster all round as no one has to wait (assuming only one person is changing the data and the rest are reading it).
3. Do any of these answers change for SQL2008R2 or SQL2012?
Many thanks
Nick
May 10, 2013 at 2:53 am
nick.welham (5/9/2013)
I'm looking for some help clarifying what effect NOLOCK will have on the SPROCS we run on our SQL 2008 server please.
Most of the answers to your questions are "Maybe", "It depends" and "Don't use NOLOCK unless you don't care about data consistency".
Have a read through these links for more information on NOLOCK: -
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock
May 10, 2013 at 3:01 am
nick.welham (5/9/2013)
1. We are not committing data via transactions or rolling it back. Just simple inserts/updates/deletes.
If you have inserts, updates and deletes then you have transactions that can get rolled back due to errors
a. If a user inserts, deletes or updates data, and then SELECTS from the table using NOLOCK, will that change be immediately available?
Yes, but that's also the case in all other isolation levels. A user will always see changes that they have made if they query after those changes. Other users will see the changes as soon as the transaction commits (slightly different in the snapshot isolation levels).
2. Is this true: If people are selecting from a table with NOLOCK, others could (sequentially) insert, update or delete at the same time - the reads may pick up some or all of the changes.
Yes.
It will be faster all round as no one has to wait (assuming only one person is changing the data and the rest are reading it).
No. Nolock is not a performance tuning technique. It is not an optimisation method. It is a way of telling SQL Server to ignore one of the ANSI tenants of relational databases (isolation) and return data that may be incorrect without waiting for other sessions to finish what they are doing.
It's fine for when you don't need accuracy. Dashboards, graphs, anything where slightly off is acceptable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2013 at 5:35 am
Gilamonster, Cadavre, thank you for your replies.
GilaMonster - could you expand on your replies about committing data please:
a. If a user inserts, deletes or updates data, and then SELECTS from the table using NOLOCK, will that change be immediately available?
Yes, but that's also the case in all other recovery models. A user will always see changes that they have made if they query after those changes. Other users will see the changes as soon as the transaction commits (slightly different in the snapshot isolation levels).
I'm interested in when a second user would be able to see that data, or the same user in a later SPROC. You mentioned recovery models - we are using Simple Recovery. I did wonder if we were using a Full Recovery model, that would change things. We are also using Merge Replication for some of the tables - does that have an effect?
It will be faster all round as no one has to wait (assuming only one person is changing the data and the rest are reading it).
No. Nolock is not a performance tuning technique. It is not an optimisation method. It is a way of telling SQL Server to ignore one of the ANSI tenants of relational databases (isolation) and return data that may be incorrect without waiting for other sessions to finish what they are doing.
It's fine for when you don't need accuracy. Dashboards, graphs, anything where slightly off is acceptable.
I was interested in your reply about whether NOLOCK counts as performance tuning. My tables contain data on many customers and I know that only one sales person is working on a single customer's data at one time, probably each day. If my queries are kept to the customer level, then I am providing my own isolation - correct?
I take your point about reporting on that data, however, that circles back to my query above about when is that data available!
May 10, 2013 at 6:55 am
nick.welham (5/10/2013)
I'm interested in when a second user would be able to see that data, or the same user in a later SPROC.
The same user, immediately, always. A user can always see data changes made in their own sessions. Other users, once the transaction that did the data modification commits. If you're not using explicit transactions, that means as soon as the statement completes.
You mentioned recovery models - we are using Simple Recovery. I did wonder if we were using a Full Recovery model, that would change things. We are also using Merge Replication for some of the tables - does that have an effect?
Apologies, I meant "isolation level", not recovery model. The recovery model has no effect.
I was interested in your reply about whether NOLOCK counts as performance tuning. My tables contain data on many customers and I know that only one sales person is working on a single customer's data at one time, probably each day. If my queries are kept to the customer level, then I am providing my own isolation - correct?
Not really. Unless you have some process that absolutely enforces that no one else may ever see or change the customer's data, all you have is preferred usage, not isolation. Still doesn't prevent the nolock problems that I indicated.
Nolock is NOT a performance tuning technique, it is a method for exchanging concurrency for correctness, it can result in incorrect data. If you use it, you are telling SQL Server that you don't mind if the results are only mostly correct (and that's not dirty reads I mean)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2013 at 7:44 am
I think you have to ask yourself what you are gaining by using NOLOCK in this case. I see nothing. NOLOCK reads data that has been changed but not committed. So, if you are implicitly committing all data, why would you use NOLOCK? If something is locking the data that needs to be read somewhere else, the time is so small that it will be inconsequential. NOLOCK is useful on a busy system that use large explicit transactions in a query that doesn't care about 100 or so incorrect records because it is looking at thousands or millions. Even then, you have to evaluate why you need NOLOCK, you don't evaluate why you don't need it. You assume you don't need it.
I will sometimes use NOLOCK in troubleshooting only in the sense that if it performs better than without, it means something is not completing its transaction fast enough for my acceptability. It tells me that I need to tune something else. I do not use it directly in a query to make it run faster. NOTE: It has also been documented that NOLOCK can result in errors in certain cases http://support.microsoft.com/kb/308886
Jared
CE - Microsoft
May 10, 2013 at 7:58 am
... NOLOCK reads data that has been changed but not committed...
NOLOCK is kind of dice - it may turn any side.
It can read some data which not yet committed, it can fail to return data which were committed long ago, it can return the same data twice and so on...
May 10, 2013 at 8:03 am
Eugene Elutin (5/10/2013)
... NOLOCK reads data that has been changed but not committed...
NOLOCK is kind of dice - it may turn any side.
It can read some data which not yet committed, it can fail to return data which were committed long ago, it can return the same data twice and so on...
Good point... Let me rephrase. NOLOCK reads data whether committed or not. Not understanding the "fail to return data which were committed long ago."
Jared
CE - Microsoft
May 10, 2013 at 8:09 am
SQLKnowItAll (5/10/2013)
Eugene Elutin (5/10/2013)
... NOLOCK reads data that has been changed but not committed...
NOLOCK is kind of dice - it may turn any side.
It can read some data which not yet committed, it can fail to return data which were committed long ago, it can return the same data twice and so on...
Good point... Let me rephrase. NOLOCK reads data whether committed or not. Not understanding the "fail to return data which were committed long ago."
Yeah, that is hidden "feature" of NOLOCK.
You can start from here:
May 10, 2013 at 8:34 am
After reading through all of your advice and the links, I've decided to remove the NOLOCK statements from everything other than reports looking at historical data (and then only when the server is busy). When I started looking at this, it seemed like NOLOCK was a feature which one could use if careful. However, with more knowledge, NOLOCK does not appear to have predictable effects/results. Not so good for a database!
I appreciate everyone's time in helping me understand this - thank you!
All the best
Nick
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply