Deadlocks...I hates the little buggers! Just spent the day trying to fix an 'issue' with a site I developed for a client. This is slightly annoying as it's a 'theoretical' issue. Essentially, the client has chosen to do something that I never normally do, load test the edit pages of the site. Unfortunately, this site has an odd editing system - it uses the concept of a 'common' tables to hold all items no matter what type of item they are (so it holds all the common properties, with the distinct properties being held in different tables). This design gives me a whole lot of flexibility - and lets me use a bunch of common controls to represent the objects on display, for security etc... However, it does have a big drawback - to actually edit some items means making multiple updates to the same table on one Use-Case - it also has a couple of indexed views on that same table. This has led me to where I currently find myself, if we run more than say 10 concurrent users (so, creating 10 new items at exactly the same instant, then trying to pull that same data out of the DB) I get this after about 200 or so repetitions:

Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Well, I've been working on this all day, optimising queries, creating new indexes, killing triggers, caching data in page, the lot...as a result I have a rotten headache and have basically lost all objectivity - I have no idea if I've really improved matters or not!

Also doesn't help that I don't actually know the target number of users I'm trying to optimise for! Incidentally, I have calculated that based on common usage patterns, 10 concurrent edits would roughly equal ~1000 concurrent 'view' users or around 144000 visitors a day...umm..yes, that's probably 100+ times more than this site gets at peak...

Oh, should say, if anyone's been having problems with the site today, this is to blame - I've been load testing like a mad thing and my poor server's feeling the pain...