Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead, because fewer transactions are:
To help minimize deadlocks:
If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. For example, if two concurrent transactions obtain a lock on the Supplier table, and then the Part table, one transaction blocks on the Supplier table until the other transaction completes. After the first transaction commits or rolls back, the second continues. No deadlock occurs. Using stored procedures for all data modifications can standardize the order of accessing objects.
Avoid writing transactions that include user interaction because the speed of batches running without user intervention is much faster than the speed a user can manually respond to queries, such as replying to a prompt for a parameter requested by an application. For example, if a transaction is waiting for user input, and the user goes to lunch, or even home for the weekend, the user holds up the transaction from completing. This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked, waiting for the transaction to complete.
A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.
Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.
Determine whether a transaction can run at a lower isolation level, for example read committed rather than serializable. Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level such as serializable, thereby reducing locking contention.
Using bound connections, two or more connections opened by the same application can cooperate. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa, and therefore do not block each other.