Originally posted on 2/27/2012 on the Valence Developer Blog by Craig Dunk.
I like collecting bugs (as in software defects) or at least stories about tracking bugs. With bugs, my favorites are always subtle and hard to reproduce but involve a one line change (As a result I have a lot of bug stories related to threading).
This week I spent a number of hours tracking down an interesting defect. Basically, we had some new API calls that were spontaneously failing in our QA environment. After turning up the logging and seeking through I identified some exceptions that were thrown periodically and roughly corresponding to the times the web calls were failing. They included the following:
System.Data.SqlClient.SqlException: Transaction (Process ID 123) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
Happily, we have a great (and award winning!) database developer on a nearby team. He had planned on setting up MSSql traces with me, but, was actually able to identify the silver bullet by inspection and based on that message was able to identify a stored procedure and gave me a lesson on concurrency.
He writes:
Concurrency is kind of hard to get right in this case.
I think we want
SELECT @id = TableId
FROM TableName WITH (HOLDLOCK)
WHERE Key = @myKey
Instead of
SELECT @id = TableId
FROM TableName
WHERE Key = @myKey
This makes a great bug story for collecting as it really was a one line change and took some time (for me) to locate and additionally I now have a really useful resource in the form of Michael's Mythbuster Post. What I like about the post (besides the Mythbuster theme) is that it is systematic, comprehensive and gives you the tools to reproduce and "try this at home". So if you have arrived to this page because you too are debugging a deadlock exception you will want to check out the above post.