#DEADLOCK SQL CODE#
#DEADLOCK SQL PC#

Although these deadlocks were somewhat rare, they still occurred a few times a day, and I'm deeply uncomfortable with errors I don't fully understand. If you aren't eating - modifying data - then how can trivial super-fast reads be blocked on rare writes? We've had good results with SQL Server so far, but I found this behavior terribly disappointing.

What about retries? I find it hard to believe that little write would take so incredibly long that a read would have to wait more than a few milliseconds at most.

#DEADLOCK SQL CODE#

We went through our code with a fine tooth comb, and yep, we're barely writing anywhere except when users ask a question, edit something, or answer a question. As much as I want to believe Stack Overflow will be this massive, rip-roaring success, there just cannot be that many answers flowing through the system in beta. The particular SQL statement you see above only occurs when someone answers a question. Like most web apps, we're insanely read-heavy. I don't want to eat your food - I just want to look at it. How can a read be blocked by a write? What possible contention could there be from merely reading the data? It's as if one of the dining philosophers happened to glance over at another philosoper's plate, and the other philosopher, seeing this, screamed 'meal viewing deadlock!' and quickly covered his plate with his hands.This deadlock perplexed me, on a couple levels. WHERE = above SQL is simplified for the purpose of this post). The statement on the losing side varied, but in our case the losing deadlock statement was always a really innocuous database read, like so: SET = it detects a deadlock, SQL Server forces one of the deadlocking commands to lose - specifically the one that uses the least resources. I did that, and found there was always one particular SQL command involved: You can attach the profiler to catch the deadlock event and see the actual commands that are deadlocking.

#DEADLOCK SQL PC#

There are lots of scarce resources in a PC or server, but this deadlock is coming from our database, SQL Server 2005. Do it wrong, and you have a deadlock - everyone starves to death. Point being, you have two processes that both need access to scarce resources that the other controls, so some sort of locking is in order. How can you ensure all the philosophers can eat reliably without starving to death? In order to eat, a given philosopher needs to use both chopsticks. There is one chopstick between each philosopher, to their immediate right and left. The philosophers alternate their time between eating and thinking. In front of each philosopher is a large plate of rice. Rerun the transaction.ĭeadlocks are a classic computer science problem, often taught to computer science students as the Dining Philosophers puzzle.įive philosophers sit around a circular table. Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. One major surprise in the development of Stack Overflow was this recurring and unpredictable gem:

That's not to say everything has been copacetic. I hadn't contacted him in any way I myself only found out about his reaction third hand. Still, I was humbled by the way Robert picked this up so enthusiastically through the community. For what it's worth, Robert Scoble was enthused about Stack Overflow, though it did not make him cry. Joel Spolsky also seems to think it's going well, but he's one of the founders so he's clearly biased. That's because I've been busy building that Stack Overflow thing we talked about. You may have noticed that my posting frequency has declined over the last three weeks.

Deadlock sql