back
11 comments
I don't know exactly what Joel and Jeff are doing so I feel bad criticizing but...

Avoiding deadlocks, starvation, race conditions, etc is difficult. But a good hacker should be able to think his or her way through. A piece of paper helps. Eventually you should be able to convince yourself you've got the right solution and then just make sure you're using the right tools, like PostreSQL and FreeBSD ;-)

This whole no lock is no problem, the server we're using is too aggressive in locking, seems like a bit of voodoo programming.

What does it say that people like Atwood, who presumably would have no difficulty writing threadsafe synchronization code in a typical application, are stumped when trying to do the same thing in a database? Database server synchronization primitives are just awful, sorry. They're completely opaque, highly implementation-dependent, and the only way to see the innards and optimize what's really happening is to become an expert at the database implementation level.
Not once does he even mention looking at the query plan. SQL server will tell you exeactly what it's doing to run a query, and from that you can tell what's blocking.

My guess is the update is trying to update an index the select wants to use. Updates that span two pages can give rise to deadlocks like this, where the select has a read lock on one page, and the update has an x-lock on the other.

None of this is magical.

If I read this correctly, does this article say that an Update statement is blocking selects? Does SQL Server really work like this?

My expertise is in Oracle - readers don't block writers and writers don't block readers. Infact writers don't block other writers unless they are updating the same row. I think MySQL works the same way as Oracle in this regard.

I find it hard to imagine building a performing web application on top on a database in which writers block readers!

"... does this article say that an Update statement is blocking selects? Does SQL Server really work like this? ..."

I don't know.

One thing I do know is in the places I've seen SQLServer used this wasn't a problem. In StackOverflow #17~ http://itc.conversationsnetwork.org/shows/detail3792.html Atwood mentions using LINQ a lot, instead of raw SQL or code they have written. I'm wary of generated code from MS tools. I wonder if this might be the problem. You can read the transcript here. The podcast is pretty good. Spolsky the Morcombe to Atwoods, Wise ~ http://en.wikipedia.org/wiki/Morecambe_and_Wise

"... I'm guessing that MySQL, which grew up on web apps, is much less pessimistic out of the box than SQL Server ..."

That one sums it all up. But does it summarise the problem? SQLServer works well enough out of the box.

What is likely happening is that he's rapidly selecting out of the database immediately after inserting his record. I am guessing that he's trying insert a number of records at once. SQL does something weird with locking when you use an auto-incrementing integer for a primary key - it uses page locking for safety when creating records.

So if he's just inserted record #10, and is now trying to select it while simultaneously inserting record #11, there can be some problems with timing. It's pretty rare, but if he's running a program to blast a bunch of stuff in at once, I've seen it happen.

The other possibility is that he's inserting or updating foreign key requirements at the same time he's trying to select out that data; and he has the option turned on to enforce foreign key constraints. SQL will lock the dependent data while any updates or inserts occur to ensure the FKs are valid through the lifetime of the transaction execution on the foreign table.

SQL server now has row versioning like Oracle, so that writes won't block reads, and you don't have to read dirty data (you just read data how it was before the write started). I think this is not turned on by default because it has a performance hit. Also I can't remember if it's new to 2005 or 2008 versions and can't be bothered to look it up.
It's new to 2005, and though it's not turned on by default, it damn well should be as it is in Oracle and Postgres. Multi version concurrency control (i.e. writers not blocking readers) is old hat in the db field. Prior to 05, you just liberally sprinkled (nolock) on your selects and accepted that you may get a dirty read but that it was better than the db falling over with over aggressive locking.
Its even turned on in MySQL if you are using innodb tables too!
Why didn't he put that on stackoverflow?
Not sure if that's the case or not, but while he complains about SQL Server's default locking strategies, it doesn't seem like he has tried running his queries using any non-default ones.

In the end he goes with read comitted snapshot isolation level which I agree sounds like overkill and an unnecessary load on tempdb, but from reading his post, it seems he has honestly never heard of the SQL Server way of doing things trough setting and using regular transaction isolation levels, as opposed to the no lock hint he for MySQL and Oracle.

I might be wrong, but to me this honestly seems like a case of not knowing your tool and how to use it properly.