Deadlocks ... Part 2

So, how do we prevent deadlocks?  Well, there are a number of different ways to go about doing it, so we'll only concentrate on a couple.

Small.  This may seem obvious, but it is one of the most overlooked items in the book.  The smaller the transaction (the fewer rows that have been updated, inserted or deleted) the less likely you are to trample on someone else.  The fewer locks you hold the better off you'll be.  One of the biggest problems in this regard is applications that update a row, even when it doesn't need to be updated.  The application follows a specific path and part of that path says that Row X in Table Y needs to have certain values.  The row is not checked to see if it has those values, it is just automatically updated.  Well, that placed a lock on that row even if the values remained the same.

Fast.  This may also seem obvious, but the shorter the amount of time you hold those locks the less likely you are to have a problem with deadlocks.  One of the classic problems here is that the application does something to the database right away, say updating a table indicating where the user is at in a process, and then does a lot of calculations or a lot of work and then does more updating at the end, interspersed with a smattering of updates / inserts / deletes.  The locks are held from the first update to the final commit and as that length of time increases, the more likely it is that deadlocks will occur.

Application Developer Guide.  OK, this one is a little esoteric, so let me explain.  One of the ways that deadlocks can happen is if one part of the application updates tables in this order - Table A, Table B, Table C - while another part of the application updates tables in the reverse order.  By explaining the order in which tables should be updated in the application's developer guide everyone on the project will know the order that they should be doing things.  Changing the order should be something that is discussed with the DBA prior to being implemented as changing the order may impact more than just deadlocks.

As usual, there are dozens of other things to take into account, but these are some of the items with the biggest payback.