- process A has a lock on table A and is waiting to update it with information from table B
- process B has a lock on table B and is waiting to update it with information from table A
since neither process will release its lock until the other processes releases its lock, we have ourselves a deadlock. most of the time SQL server "resolves" this issue itself because it has a thread dedicated to its lock manager and someone much smarter than myself came up with an algorithm that allows the lock manager to detect this and kill one of the processes. i have no clue how it chooses which process is the victim of its kill statement, but, from my experience, 99% of the time it is does make this decision and it does kill one of the processes. i've not yet had this happen in SQL 2005, but i can count on one hand the number of times when a deadlock occurred in SQL 2000 and SQL left the decision up to me. in all of these cases, the decision for me was simple - i opened up the SQL activity monitor, scrolled to the right, and saw that a whole bunch of locked processes listed the same process in the 'Blocked By' column. by no means should you be cavalier and just kill this single process before you know what it is, but it's a good place to start. i figured out what the process was and had my client log on to the server hosting the application and simply close it.
back to the other 99% of the time SQL Server resolved the deadlock automagically. this is still very bad. some application was trying to perform some operation on a table or its data and SQL Server flat out squashed it before it could finish. there are certainly ways design a database to minimize the chance of this, but no matter how badass you think your 5NF database is (because there is no way you are badass enough to reach 6NF), you are going to experience a deadlock at some point. i recommend you read the following article to better understand what is going on, and how to track down resolve the problem:
.jpg)
.jpg)
No comments:
Post a Comment