You can resolve cycle deadlocks by killing the cheapest process, the one that acquired its lock last or locked for the shortest amount of time. You also can set a deadlock priority high or low, which will alter the server's selection of a deadlock victim. Even if you decide to live with the deadlock condition, you can still control which process the server picks as the deadlock victim. For example, if you determine that the deadlock occurs between a transaction processing operation and an operation servicing a reporting application, you can set the report application's deadlock priority to low so that the server kills it instead of the transaction process.

Note: Altering the server code to trap for the error is not an option, because once the server detects the deadlock it terminates the transaction, which short-circuits your error-detection code. The retry must happen at the application.

While debugging cycle deadlocks is straightforward, conversion deadlocks are not as simple, because with them, locks can accumulate and become unmanageable. One way to plan for deadlocks is to adopt your own serialization convention to help the server do the right thing:

• Pick an order for things to happen and enforce that order everywhere. Alphabetical order by table name will do. This means that the server can serialize access to inserts, updates, and deletes in a universally observed order.
• Have your applications test for error 1205 and retry the transaction again.
• Code procedures so that the data modification comes before a select statement. In other words, do your update first, then select.

For example, despite looking fine, the following procedure deadlocked because of contention for the row that holds the nexid value:

Begin Transaction
SELECT @Next_Number = NextID_Next_num
FROM NextID
WHERE (NextID_Id_name = @FieldName)
UPDATE NextID SET NextID_Next_num = (@Next_Number + 1)
WHERE (NextID_Id_name = @FieldName)
Commit Transaction
RETURN @Next_Number

Here is a different approach, in which the update comes before the select:

Begin Transaction
UPDATE NextID SET NextID_Next_num = NextID_Next_num + 1
WHERE (NextID_Id_name = @FieldName)
SELECT @Next_Number = NextID_Next_num
FROM NextID
WHERE (NextID_Id_name = @FieldName)
Commit Transaction

Once you've set an order for your server to approach your data modifications, all you have left to do is fix a deadlock. Since the server picks its victim, you are limited to retrying your transaction. You can do so by coding a retry loop in your application.