Select with NOLOCK is trying to get a shared lock
-
I have a select-query using the WITH(NOLOCK) hint to ignore locks. The problem is that when the query is executed it get timed out because it's waiting for a shared page lock on the table in the select. I thought NOLOCK should prevent trying to get shared locks.
Are there any cases where the NOLOCK is not having effect or some setting in SQL Server that affect this?
-
Avalin (5/18/2016)
I have a select-query using the WITH(NOLOCK) hint to ignore locks. The problem is that when the query is executed it get timed out because it's waiting for a shared page lock on the table in the select. I thought NOLOCK should prevent trying to get shared locks.Are there any cases where the NOLOCK is not having effect or some setting in SQL Server that affect this?
First, what method are you using to confirm the selects in question truely are acquiring shared read locks?
Also, keep in ming that these NOLOCK queries will still be blocked by a schema modification (SCH-M) locks. For example, some ETL processes will drop or disable indexes on a table prior to performing a bulk insert, and then re-create the indexes afterward. I'm not sure, but this can perhaps also be caused by table partition switching, which again would be related to a data load process.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Avalin (5/18/2016)
I have a select-query using the WITH(NOLOCK) hint to ignore locks. The problem is that when the query is executed it get timed out because it's waiting for a shared page lock on the table in the select. I thought NOLOCK should prevent trying to get shared locks.Are there any cases where the NOLOCK is not having effect or some setting in SQL Server that affect this?
Nolock ignores the locks of others but still takes shared locks.
😎
-
Eirikur Eiriksson (5/18/2016)
Nolock ignores the locks of others but still takes shared locks.No it doesn't.
Nolock can ignore locks of others because it doesn't take shared locks. If it did take shared locks, it would not be able to ignore other locks (a shared lock can't be taken if there's an incompatible lock).
It still takes schema stability locks, still takes exclusive locks, but readuncommitted/nolock does not take shared locks.
And some extended events to show that.
Without nolock:
With nolock
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (5/18/2016)
Eirikur Eiriksson (5/18/2016)
Nolock ignores the locks of others but still takes shared locks.No it doesn't.
Nolock can ignore locks of others because it doesn't take shared locks. If it did take shared locks, it would not be able to ignore other locks (a shared lock can't be taken if there's an incompatible lock).
It still takes schema stability locks, still takes exclusive locks, but readuncommitted/nolock does not take shared locks.
And some extended events to show that.
Without nolock:
With nolock
Correct me if I am wrong, the schema lock is to prevent schema changes to the table(s) involved in the query.
-
Lynn Pettis (5/18/2016)
Correct me if I am wrong, the schema lock is to prevent schema changes to the table(s) involved in the query.Yup. Sch-S = Schema Stability lock. The only thing that blocks/is blocked by is a Schema Modification lock (Sch-M), which is taken when changing the table structure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (5/18/2016)
Eirikur Eiriksson (5/18/2016)
Nolock ignores the locks of others but still takes shared locks.No it doesn't.
Thanks again Gail for the correction :Whistling:
😎
-
I use sys.processes to see what the blocking was about. I also get the error message lock request time out period exceeded and can see the timeout in the profiler so I'm pretty sure it try to take a shared lock.
I also found out that the problem only occur when running this query from an application on one database only. If i run it in the SSMS I don't get the problem.
-
Could be that there's some other statements being passed from the app and something else is waiting for a lock, because with the nolock hint applied to a table, SQL doesn't take shared locks on that table. If it needs exclusive locks it'll still take those, it'll still take the schema locks, but no shared locks.
Investigate and see exactly what lock is being waited for (sysprocesses has been deprecated for over 10 years, so rather use the DMVs, they give a lot more info). Guessing as to what's happening is a good way to waste a lot of time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Years later - if someone stumbles over this: WITH(NOLOCK) can get locks for non-SELECT statements, for example, it will get locks if it is part of an UPDATE statement. Especially, shared locks will be issued for checking keys of foreign key constraints.
But in the original question (...select-query using the WITH(NOLOCK)...), it wont get shared locks.
- harald_m_mueller wrote:
Years later - if someone stumbles over this: WITH(NOLOCK) can get locks for non-SELECT statements, for example, it will get locks if it is part of an UPDATE statement. Especially, shared locks will be issued for checking keys of foreign key constraints.
But in the original question (...select-query using the WITH(NOLOCK)...), it wont get shared locks.
No one should be using NOLOCK with UPDATE statements. This has been in MS documentation for several years now:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Of course (and thank you for the link).
It's like the people comparing things via their hash code - has been on the books for decades ... but still.
-
Just in case anyone else stumbles upon this, I would check your TSQL and make sure that you actually are using the WITH NOLOCK syntax. Emphasis on the WITH keyword.
I was recently working with a client that was seeing frequent deadlocking in a high volume OLTP environment. When we went to investigate the input buffers of the blocking resources we saw that a SELECT statement using NOLOCK was taking a S lock resource. At first this profoundly confused me because, as everyone stated above, a SELECT statement with NOLOCK specified should never take a shared lock... unless the query optimizer mistakenly interprets the NOLOCK hint as a table alias that is.
Example:
SELECT [col1]
FROM [database].[dbo].[table1] NOLOCK
WHERE [col2] = 'condition'In this example, a shared lock still gets taken because NOLOCK is specified without ( ). This was the case for my client.
Technically, adding the ( ) should prevent this depending on your version:
SELECT [col1]
FROM [database].[dbo].[table1] (NOLOCK)
WHERE [col2] = 'condition'But support for the use of NOLOCK without the WITH keyword is deprecated and will be remove in a future version. Your TSQL should ideally look like this:
SELECT [col1]
FROM [database].[dbo].[table1] WITH (NOLOCK)
WHERE [col2] = 'condition'
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply