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!
I’ve never used Postgres, so will defer to you on that one.