Deadlocks ... Part 1

Deadlocks are an interesting condition within the database.  In very simplistic terms, it occurs when two people are both trying to change data that the other person has already changed.  For instance, Person A has already updated Row 1 in Table 1 and now wants to update Row 2 in Table 2.  However, Person B has already updated Row 2 in Table 2 and is trying to update Row 1 in Table 1.  As you can see, unless someone gives in they could sit there all day.  The database is the arbiter in this case and makes a decision as to which person is going to get the error message when their transaction is canceled.

Now, you may have heard the phrase "Deadlocks are a natural part of application processing and are not a large concern."  While I do not advocate violence, the person who says this should be slapped in order to knock some sense into them.  Deadlocks are not a natural par tof an application.  I previously worked on a web-based system that had, as regularly peaked at over 650 simultaneous users, with over 250 of those being "hard core" users. This is 10 times the size of any application we have currently running.  If we got a single deadlock during the day we had to investigate why the deadlock occurred and determine if the deadlock could be prevented.  We would go for weeks, or even months without a deadlock, but when one occurred we dropped everything and investigated the problem.

Why do we get deadlocks in an application?  Sometimes it is because in one part of an application we update Table 1 and then Table 2, but in other parts we update Table 2 and then Table 1.  This is a disaster waiting to happen.  In other circumstances we have background tasks running that are not properly tuned and they try to update too much at once before committing their data.  This is also another disaster.

There are a number of surprisingly simple and, to most people, obvious strategies to use that will eliminate deadlocks to the "rare" occurrence that they should be.  We'll discuss those tomorrow.