Sql-server – How to prove NOLOCK is the source of deadlocking problems

nolocksql server

I'm not trying to start a windows/mac type discussion.

Personally, I don't need any convincing that NOLOCK is not a good idea as a reflexive practice. It seems when you're developing everything should be purposeful not reactionary (/amen)

So… programmer-in-charge insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a stored procedure without nolock hints on every table.

Don't want to be that guy who comes in and tells everyone a core belief is all wrong without something to back it up.

Just looking at the comment sessions under the various blog posts, sending a link may not be enough. Long-held beliefs etc… Some people aren't convinced it's a problem. See: Comments section under every nolock blog post I've read.

Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine whether NOLOCKs are the source?

It's been suggested looking at XML from traces etc, but this won't explicitly state that the deadlocks are causing the problem, will it? I've never seen error messages that straight forward. is that true?

How else could these deadlocks be pinned on this?

DDL statements like CREATE would be a clue. Is there any output that I could point to or some piece of data I could find that would help corroborate my theory before I raise the alarm?

Or am I running trace flags or extended events to identify what's running when the deadlocks occur and then deducing from DDL statements?

Looking at all the different ways data can get messed up with nolock hints, it seems like a hard problem to decisively pin down.

Best Answer

The architect-in-charge how insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a sproc without nolock hints on every table.

Sorry to hear that. This is pretty well universally regarded as an anti-pattern among expert SQL Server practitioners, but there may be nothing you can do to change the facts on the ground if the practice is truly ingrained, and based on one person's sincerely and deeply-held beliefs.

The question says that "sending a link may not be enough", but it's unclear what you want instead. We could write the most compelling argument in the world in an answer here, and you would still be left "sending a link" to it. Ultimately, no one here can know which set of arguments will be successful in your specific situation (if any).

Nevertheless, the following cover most points that some people find persuasive enough to change a previously common practice:

Bad habits : Putting NOLOCK everywhere by Aaron Bertrand
SQL Server NOLOCK Hint & other poor ideas By David Lean (Microsoft)
The Read Uncommitted Isolation Level by me
Justify NOT using the (nolock) hint in every query (related Q & A here)

Even so, you may not be able to 'win' this battle. I have worked in an environment that did this, understood the risks and reasons not to do it, but continued with it anyway. These were bright, logical people, but in the end it was an environment I could not be happy working in.

Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine that NOLOCKs are the source.

The more usual pattern (perhaps more prevalent in the past) in that NOLOCK hints are introduced in an attempt to reduce the incidence of deadlocks. This can 'work' in that reducing the number of shared locks taken naturally reduces the chances of incompatible locks being taken, but it is not a good solution to the underlying problem, and comes with all the caveats noted in the previous section.

Nevertheless, NOLOCK hints can introduce new ways to deadlock. Using read uncommitted isolation can remove a transient blocking condition (resolved when the contended resource becomes available) into an unresolvable deadlock (where neither waiter can make progress) simply because the contention now happens at a different point, where e.g. write operations in a different order overlap. Dave Ballantyne has an example here:

Increasing deadlocks with NOLOCK

For more general advice about dealing with deadlocks, I recommend the following as a starting point:

The Difficulty with Deadlocks by Jeremiah Peschka

You should also familiarise yourself with the documentation:

Deadlocking (and all three sub-topics there)

Other useful resources:

SQL Server Deadlocks by Example by Gail Shaw
Deadlock troubleshooting by Bart Duncan

Related Solutions

Sql-server – Can someone explain why select with nolock will query a potion of updated data

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks).

Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent dirty reads.

In the example in the linked answer the SELECT query is not blocked when it encounters a modified row so reading partial updates is quite likely.

It can also happen at default read committed isolation level too though that a SELECT reads some rows with the "before" value and others with the "after" value. It is just needed to engineer a situation where

Select query reads value of row R1 and releases its S lock
Update query updates R2 and takes an X lock
Select query tries to read R2 and is blocked.
Update query updates R1 and takes an X lock.
Update transaction commits thus releasing its locks and allowing the Select to read R2

This type of situation might arise for example if the SELECT and UPDATE are using different indexes to locate the rows of interest.

Example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y AS -X UNIQUE,
Name varchar(10),
Filler char(4000) DEFAULT 'X'
)


INSERT INTO T (Name)
SELECT TOP 2500 'A'
FROM master..spt_values

Now in one query window run

DECLARE @Sum int

SELECT 'SET @@ROWCOUNT' WHERE 1=0

WHILE (@@ROWCOUNT = 0)
SELECT @Sum = SUM(LEN(Name))
FROM T 
WHERE Y IN (-1, -2500)
HAVING SUM(LEN(Name)) = 3

This will run in an infinite loop. In another run

UPDATE T 
SET Name=CASE WHEN Name = 'A' THEN 'AA' ELSE 'A' END

This will likely stop the loop in the other query (try again if not) meaning that it must have read either A,AA or AA,A

SQL Server – How to Prove T-SQL is Bad for Performance

There is dynamic SQL, so no cache plans, meaning plans generated every time

Not necessarily true. Dynamic SQL can (and does) use cached plans just as well as static SQL. For dynamic search conditions resolving to dynamic SQL is oft the right answer. See Dynamic Search Conditions in T-SQL for more details.

There is an INSERT SELECT pattern, so table locking is more likely to be an issue.

Not necessarily true, specially with a @tempTable

have re-written some of the Stored Procedures in this way instead

Using multiple OR conditions like that is an anti-pattern. You are forcing the query optimizer to come up with a plan that works for any value of all those parameters. Usually the only solution is a scan, ignoring any index. The original code was better.

Can anyone offer ... any better ways of proving my suspicions?

Yes. Measure. Use a methodology like Waits and Queues. Don't relly on your intuition. Find the bottlenecks and address them accordingly.

Best Answer

Related Solutions

Sql-server – Can someone explain why select with nolock will query a potion of updated data

SQL Server – How to Prove T-SQL is Bad for Performance

Related Question