When two transactions become deadlocked, they will be able to determine that this condition exists. The first transaction to detect a deadlock condition is terminated. By terminating one of the transactions, the locks are released and the remaining transaction can now complete.
If you are receiving deadlocks, then your only course to resolve these are through better programming techniques. There are two common actions to perform.
- Make sure that your application performs DML operations on tables in the same order. Having one section of your application code perform DML on three tables in one order while another section of your application code performing DML on these same tables in a different order can introduce a deadlock condition if two users simultaneously execute the different sections of code.
- Make sure that you are committing as often as possible. When you commit (or roll back) your transaction, you release the locks that transaction held.
This was first published in May 2005