January 5, 2010 at 4:50 pm
This definitely got me by surprise. Yesterday we encountered some deadlocks between a query that uses NOLOCK and a regular update statement without hints. Per the deadlock graph, the locks for both these queries were escalated to PAGE LOCK that resulted in the deadlock. XDL is attached. Due to upload restrictions, I've renamed to .txt; rename file to "Sample deadlock with NOLOCK.xdl"
the issue is resolved for now. I ran a index rebuild on the participating tables since the scan density was less than 10%. We have not had an instance of deadlock since.
But I'm perplexed why a NOLOCK would still participate in LOCK unless CTE is enforcing it. Any guidance is much appreciated.
Thanks
January 5, 2010 at 5:07 pm
Keep in mind that hints are just that, hints. They tell the optimizer what it should do, but the optimizer still has the ability to choose what it thinks is best.
January 5, 2010 at 5:20 pm
As John said, using the NOLOCK will not guarantee that a deadlock does not occur. It is a strong suggestion to the query optimizer - but may not be discarded if the optimizer feels it unnecessary. Rather than use the nolock, it would be better to tune the queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 6, 2010 at 2:20 am
The answer's simple. The deadlock is against the Demand table, and the subqueries that query against demand do not have the nolock hint.
I would not suggest you add it. I would strongly suggest you remove all of the nolock hints and have a look at your indexing to reduce locks. See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
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
January 6, 2010 at 2:23 am
John Rowan (1/5/2010)
Keep in mind that hints are just that, hints.
CirquedeSQLeil (1/5/2010)
It is a strong suggestion to the query optimizer - but may not be discarded if the optimizer feels it unnecessary.
Unfortunatly, not true. The term 'hint' is a very bad one. With a couple of exceptions that I've heard about (but never seen), a hint is a directive, an instruction that the optimiser cannot ignore and must follow. If you say WITH NOLOCK, then no shared locks will be taken and exclusive locks will not be honoured.
The only exceptions with nolock is that exclusive and schema stability locks will still be taken (nolock only affects shared locks) and schema modification locks will still be honoured.
It's because the optimiser cannot ignore hints that it's possible to get errors like these.
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
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
January 6, 2010 at 10:00 am
Thanks Gail. Once again you have taught me something.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 6, 2010 at 10:05 am
CirquedeSQLeil (1/6/2010)
Thanks Gail. Once again you have taught me something.
I agree, this is news to me. I'll have to admit that I've only found the need to use a locking hint once in my whole career, but I've always had the understanding that the optimizer could still escalate the lock and ignore the hint if needed. The key word here is 'understanding' because it's not something that I just thought might be the case, it's something that I knew (or I guess just thought I knew) for sure!
Gail,
Has this changed with SQL Server 2005 and up or has this always been the case with locking hints?
January 6, 2010 at 10:43 am
Thanks Gail .. I missed the subqueries ... i feel stupid .. 🙂
January 6, 2010 at 11:09 am
John Rowan (1/6/2010)
but I've always had the understanding that the optimizer could still escalate the lock and ignore the hint if needed.
Escalate as in shared to exclusive or escalate as in row to table?
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
January 6, 2010 at 11:17 am
Both. I always believed that the optimizer could escalte the lock type or scope as needed regardless of hints. In retrospect, that would mean that the optimizer would have to have some sort of threshold or cut-off ponit to where it decides to scrap the hints and go for it's own best decision.
January 6, 2010 at 12:06 pm
Firstly, Nolock is not in the same category as UPDLOCK or XLOCK. UPDLOCK and XLOCK are locking mode hints and you can think of these hints as specifying the minimum locking mode you want rather than the exact one that must be used.
Nolock is an isolation level hint. It says to use the read uncommitted isolation level for portions of the query. Hence it is in the category with READCOMMITTED, REPEATEABLEREAD, HOLDLOCK and SERIALIZABLE (and the snapshot isolation hints).
If one of those is specified, that's the isolation level that will be used and SQL won't escalate the isolation level, no more than it will when you use the SET TRANSACTION ISOLATION LEVEL statement
As a couple examples
Locking mode
DELETE FROM dbo.BigTable WITH (UPDLOCK) WHERE id = 56
This takes an exclusive lock. The specified lock is too low for the operation and so a higher level one is used.
SELECT * FROM dbo.BigTable WITH (UPDLOCK) WHERE id = 56
This takes an update lock. The select would usually take a shared, but the hint specified a higher level and so the higher lock level is used
Isolation level
UPDATE dbo.BigTable WITH (NOLOCK)
SET SomeColumn = SomeColumn + ' '
This throws an error
Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.
As for the levels, row, page and table, I believe those are directives and will not be overridden. I'll test sometime.
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
January 6, 2010 at 12:30 pm
Ah, that makes sense. I guess my problem was in lumping the NOLOCK hint in with the locking hints. To be honest, I know that it is an isolation level hint, but for some reason, maybe the word LOCK in the name, I thought of it behaving like the locking hints do.
Thanks for the clarification!
January 7, 2010 at 5:43 pm
Although not applicable to this scenario, you should also be aware that locks on objects are not the only thing that can cause a deadlock. For example, you could have a deadlock due to memory resources.
January 8, 2010 at 2:18 pm
We had a situation similar to this. Our queries had NOLOCK hints and we were still experiencing deadlocks. We resolved it by placing indexes on the foreign keys. I read an article (on this site, I think) that explains the reasoning behind it, but am unable to find it at the moment.
Hope this helps though.
Michael
July 8, 2016 at 8:14 am
I have an update statement that uses With NOLOCK. Its been working fine for months. We have some yearly reports that another team member is running. Is it possible that if the queries for the reports don't have nolock that they could be causing the deadlock issue?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply