

Deadlocked - Anon84
http://www.codinghorror.com/blog/archives/001166.html

======
biohacker42
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.

------
ajross
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.

------
kogir
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.

------
fendale
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!

~~~
KiwiNige
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.

~~~
gnaritas
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.

~~~
fendale
Its even turned on in MySQL if you are using innodb tables too!

------
drewr
Why didn't he put that on stackoverflow?

------
trezor
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.

