Symmetri Developer Blog

I’ve been wrestling with deadlock problems in SQL Server 2005 over the past week. I have a web application that is being hit by hundreds of users an hour, and a windows service that runs every 20 minutes or so. Both the website and the service run queries that update the same table. Above a certain threshold of traffic, I noticed that the service kept getting deadlock issues - SQL Server kicked off the service’s query as the deadlock victim. At first I didn’t think too much of it and I added some code to catch the exception and retry the query. But as time went on, more and more deadlocks began to happen. It takes 5 seconds for SQL Server’s deadlock manager to check for a deadlock, so that was 5 seconds that was being wasted to retry every query - and sometimes it has to be retried multiple times. It quickly became clear that this was unacceptable.

First thing I tried was to add locking table hints to the SQL queries I was using - WITH (ROWLOCK) to all UPDATE and SELECT queries. What that does is to suggest to SQL Server that it use row-level locking, rather than locking by page or locking the entire table. But that didn’t help too much. Generally, you might expect that it wouldn’t, since I believe (?) that row-level locking is the default anyway - SQL Server tries to lock the row alone, and only escalates the lock to a table level lock if it has to - for example, if it has to update a clustered index based on the insert.

I was getting slightly desperate by this stage, so I even tried converting my SELECT statements to use WITH (NOLOCK) - which basically means ignoring locking altogether and doing dirty reads. But no luck - still deadlocks going on. Even though I was updating different rows, SQL Server was finding itself getting into deadlocks. There wasn’t much going on from the index side of things, so I figured that wasn’t it. No triggers on the table - something that you would expect could cause deadlock issues if designed badly.

This isn’t such a great story, actually, because in the end I never did really find out what was going on. The site was live, and I needed a surefire solution immediately - so I ended up rewriting the database to take half the data out into another table to reduce contention - and I simplified and flattened the data structure to get rid of unnecessary foreign keys and (subsequently) JOINs. As soon as I did that - at the cost of one night’s sleep, as I needed to migrate the data when users weren’t signing up - deadlock problems disappeared.

I probably could have tracked it down if I had access to the database server to run profiler and other tools. But, as is typical of hosting scenarios, I did not have that kind of access. If my timeline had allowed it, I would have sucked down the database onto a local site, reproduced the problems and really been able to tease out the sequence of events that was causing deadlock. But, in the end, reducing contention is always a good solution anyway, if it can be done.