Ken Lee-263418 (1/1/2012)
... Maybe I'm just frustrated with some deadlock investigations where neither transaction was doing anything wrong, and if both had asked a few milliseconds apart, both would have succeeded, but instead one of them died in deadlock. Then tracing through the constraints, trying to figure out exactly how it happened and coming to the conclusion there isn't any way to stop it from happening.Then sometimes, you can solve the problem by setting up locking hints to cause locking in the right order. Just, why do you have to jump through all these hoops, to solve a problem produced by SQL logic?
I have been here too (with SQL Server as it happens) and the reason is not IMO (1) SQL logic or (2) embedded constraints. In my case it was perverse behaviour of the implementation - in particular the query optimiser recycling query plans in a case where 2 instances of the query with different sargs had run at the same time (previously) and produced inverse (of each other) lock escalation strategies ... then voila a deadlock when reused at a later date!
What made it even more perverse was "in testing" (ie. without 20K sessions seen in production) the 2nd "bad" query plan never got created (and therefore not left in cache) so showplan just played out sweetness to the unskilled reader. Unfortunately the vendor was not much interested and there was not much room to move on a 2 table join.