a companion discussion area for blog.codinghorror.com



But in practice adding nolock to queries that you absolutely know are simple, straightforward read-only affairs never seems to lead to problems.
With nolock / read uncommitted / dirty reads, data may be out of date at the time you read it, but it’s never wrong or garbled or corrupted in a way that will crash you

That hasn’t been true in our experience: although infrequent, we do get the following error:

[SqlException] Could not continue scan with NOLOCK due to data movement.

It’s an Exception - the query fails and the user sees an error. So NOLOCK can still break your read query and give the user no result rather than just an out-of-date one.

NOLOCK may still be useful - but it’s not guaranteed to work every time…


Views are just stored SQL. Given that a view will not change a deadlock situation.



Can you please show us some query plans so we can offer meaningful advice? SQL server maintains a cache of the plans for all parameterized queries by default.

I’d give instructions, but I’m not at my computer right now.

I’ve run into some hairy stuff at Loopt, and might be able to at least explain why it’s happening, if I can’t offer a real solution. I view NOLOCK as a hack and successfully eliminated it from all our DB code.


Well, Microsoft is based on Sybase, which I know used to default to dirty reads (this means that for instance, your bank statement could show a transaction as having occurred, but the balance from before the transaction occurred). It sounds like MS went and bandaided this behavior, with the results you see.

Try something that has always done the right thing, such as Oracle or Postgress. Reads never block (and thus never deadlock).


Seriously you don’t know why you need a lock when reading a database which conflicts with an update??? And you are blogging about it???

Say you are taking orders for the dining philosophers. Let’s say philosopher B wants what philosopher B is having - you need to ensure that philosopher A’s order is complete before philosopher B gets to read it.


Hey Jeff, in response to

I get that, but I don’t understand why simple read SELECTS need to lock at all. I mean, maybe if that database was storing my bank account balance, in which case transactional orders are hugely important.

You should consider two items:

Firstly, Read locks are much looser than other lock types (e.g.: Update), and won’t usually cause blocking, let alone deadlocks. Several comments have suggested good articles on locking, and I’d recommend you read SQL Server Books Online (a misnomer, as you can install them locally.)

Secondly, if you read dirty data, the risk you run is of reading the entirely wrong row. For example, if your select reads an index to find your row, then the update changes the location of the rows (e.g.: due to a page split or an update to the clustered index), when your select goes to read the actual data row, it’s either no longer there, or a different row altogether! How would it appear to your users if every time someone else added a comment on your site, the comments they retrieved came from other conversation threads? That’s what Read locks prevent; they make your Read atomic, so that while following indexes, retrieving pages, and performing joins, you don’t have invalid data creeping in (or valid data creeping out) of your results.

As to the root of your problem, I would suggest that either you are creating explicit transactions that you didn’t tell us about, or that you need to turn off implicit transactions for LINQ. And while I always enjoy reading your blog, I’m afraid the old A poor workman blames his tools quote is rather appropriate here. If you really are getting deadlocks with simple SQL statements and no transactions, then your code is going to perform terribly as usage scales up. No amount of (NOLOCK) or Read Uncommitted will fix bad code.

Always entertaining, and often enlightening. Thank Jeff, and good luck!


Ronald Pottol:
Try something that has always done the right thing, such as Oracle or Postgress. Reads never block (and thus never deadlock).

Oracle accomplishes this with snapshotting, which SQL Server has supported for a few years. It’s off by default because it’s expensive both in terms of both CPU and Memory. Essentially, your RDMS is managing multiple versions of the Truth, which is what we try to normalize away from.

And there are ways to get deadlocks with reads Oracle, depending on your transactions and locking levels. I know, I’ve done it. :frowning: I’ve never used Postgres, so will defer to you on that one.


The reason for the deadlock in this case is straightforward, if my obvious-seeming assumptions are correct.

I’ll bet that the rendered page shows the # of answers followed by the answers themselves. Any non-sloppy developer would try to make the numbers match. The obvious thing to do is put the SELECT of the question in the same transaction as the SELECT of the answers. That’s the source of the problem.

Suppose I post an answer to a question that you are currently loading. You SELECT the parent [Post], taking a shared lock on the row. I INSERT my answer, taking an exclusive lock on the new child row. I try to update the number of answers to the question, but you have a lock, so I’m blocking on you. You start SELECTing all child [Posts], get to my new answer and block because I have an exclusive lock. You wouldn’t want to read a record that may get rolled back. That wouldn’t be stale data, it would be non-existent data! So, you’re blocking on me. Now we have a deadlock. I’ve assumed the [Posts] table is a tree. The problem still exists if it’s not.

MVCC would fix this particular problem. You’ve never see my INSERTed, but uncommitted record, so your SELECT would finish, you’d release the shared lock on the question, and then I’d UPDATE it and commit, making my new record visible at the same instant that my UPDATEd answer count takes effect.

Stale data isn’t so bad in certain specific cases, but using non-existent data (that got rolled back) is just amateurish. READ UNCOMMITTED is for suckers. Maybe your SELECTs don’t need to take shared locks, but, for heaven’s sake, don’t ignore the exclusive write locks. Or switch to MVCC. I don’t know how much of this is possible on SqlServer. It was news to me that it doesn’t always use MVCC.


Jeff, you appear to have forgotten transactions are independent. SQL Server will, by default, do its utmost to preserve transaction independence (anything less is simply unacceptable.) What this means is that the world is frozen when the transaction begins, looking around at this frozen world nothing will change exept what you move.

Working without locks is okay so long as you know what it means. Working at read uncomitted means that if you go begin transaction, select from table, select again from same table’ the second select may have different results than the first. Whether this is a problem is up to you; but SQL Server has to assume (by default) it might be so by default it is more cautious.

It’s not ‘silly’ that SQL Server treats your website as a banking operation; personally I would expect nothing less. Correctness is a vastly more important than performance. Only the developer can trade off correctness for performance; and it is rarely needed. I suspect you’d be the first complain about Microsoft’s apparent shoddy coding when running the ‘count of comments’ query and the ‘text of comments query’ in the same transaction came up with different numbers!

As to why select locks records; if it didn’t then the world wouldn’t frozen. Your program logic may behave differently if the changes were visible (there’s now an extra comment and you have a 'no more comments more than 500 rule or something) or, as I mentioned, you do a second query and it gets a different result.


mgb said, It’s always a little disturbing to see a well-known coder ask a dumb question, but come on, database locks? This is very basic stuff!

Shut up. Seriously. What a techy-snob! You’d get negative 10,000 on stackoverflow.com. It’s because of folks like you that others have a hard time asking questions. You should know better. Tool.


And there are ways to get deadlocks with reads Oracle, depending on your transactions and
locking levels. I know, I’ve done it. :frowning: I’ve never used Postgres, so will defer to you on that

I’m pretty sure this is impossible. With Oracle, writes cannot block reads, and reads cannot block writes.

In my experience with Oracle, almost all deadlocks are deletes in a parent table were a child table has an unindexed foreign key to the parent (a full table lock is required in this case.) This is of course an easy thing to fix - just add an index.

a href=http://asktom.oracle.com/tkyte/unindex/index.htmlhttp://asktom.oracle.com/tkyte/unindex/index.html/a



Lot’s of chatter over a simple SQL keyword that folks should be aware of and using if they use SQL regularly.

This is kind of like saying I know the framework will garbage collect, but things work better when I dispose of my own trash.


I had a very similar problem - except I was reading a large amount of data from a few tables to create a report. And on another webpage, data was being inserted into the table, but the insertion was chosen as the deadlock victim.

I think a sql server deadlock support group is needed! lol


I know this isn’t stack overflow - but I thought I’d point to the correct answer in this thread.

Robert G points to the trace flags described in http://support.microsoft.com/kb/832524 that give you more than enough detail on the deadlock participants.

Having gone through several of these before, this deadlock information typically highlights an application call (or rather a pair of simultaneous calls) that are not as precise as they could be - very often causing unnecessary lock escalation.

Query hints or application-level retries are bandaid solutions to address the symptoms, not the problem.

Hope that helps,

  • John

  1. Why do you need an AnswerCount field? Just compute it and avoid the possibility that it contains the wrong value. My suggestion is to show your schema to a good data modeler. Focus on your app code that touches anything the modeler circles in red.

  2. The root cause is not using Oracle. If you are using Oracle and still get a deadlock, you have an unindexed foreign key and failed to heed the warnings in TOAD or OEM. I only need one hand to count the number of deadlocks I’ve encountered in a decade of using Oracle. All had obvious causes and were discovered early in development.

  3. Anybody recommending nolocks should be on an IT no-hire list. You are making bugs. Why not simply store all your data in ASCII flat files and get your wrong answer even faster?

  1. Anybody recommending nolocks should be on an IT no-hire list. You are making bugs. Why not simply store all your data in ASCII flat files and get your wrong answer even faster?

Funny you should bring that up. I was wondering if a DB was even needed in a design like this. Sure, you need a durable store but do you really need a full ACID guarantee? You could go a long way with a simple paxos implementation rather than a full DB.

insert argument about using a sledgehammer to drive a thumb tack here




from (http://msdn.microsoft.com/en-us/library/ms186736.aspx)
Which session is chosen as the deadlock victim depends on each session’s deadlock priority:

If both sessions have the same deadlock priority, the instance of SQL Server chooses the session that is less expensive to roll back as the deadlock victim. For example, if both sessions have set their deadlock priority to HIGH, the instance will choose as a victim the session it estimates is less costly to roll back.
If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.

And from (http://msdn.microsoft.com/en-us/library/ms178104.aspx)
Resources that can deadlock: Locks, worker threads, memory, parallel execution-related resources, and Multiple Active Result Sets (MARS)

There’s a plethora of msdn articles on minimising and preventing deadlocks.


@OracleGuy - you’re a zealot. The nolocks hint is there for a reason, not just some bandaid for idiots. The only reason for a ‘no-hire’ is if someone can’t see any possible danger of using nolock and prefers it ‘for speed’.

I’ll concede that if you’re using nolock often, then it’s a bad smell indicator that you’re doing something wrong.


first post!


I just went through this on a system. Row level locking for updates may also be worth looking into for your situation as well.