How to avoid deadlocks in SQL Server

four necessary conditions of a deadlock

1. Mutually exclusive conditions (Mutual exclusion): Resources cannot be shared and can only be used by one process.

2. Request and retention conditions (Hold and wait): a process that has already been resourced can request a new resource again.

3. Non-deprivation (No pre-emption): Resources that have been allocated cannot be forcibly deprived from the corresponding process.

4. Cyclic wait condition (circular waiting): Several processes in the system form a loop in which each process waits for resources that are being occupied by adjacent processes.

　　Second, to avoid deadlock

1. Access objects in the same order

If all concurrent transactions access the object in the same order, the likelihood of a deadlock is reduced. For example, if two concurrent transactions obtain a lock on the Supplier table and then obtain a lock on the part table, another transaction is blocked on the Supplier table until one of the transactions completes. After the first transaction commits or rolls back, the second transaction continues. Deadlock does not occur. Using stored procedures for all data modifications can standardize the order in which objects are accessed.

2, avoid the user interaction in the transaction

Avoid writing transactions that contain user interaction, because batches that run without user interaction are much faster than users manually respond to queries, such as prompts to reply to application request parameters. For example, if a transaction is waiting for a user to enter, and the user goes to lunch or even goes home for the weekend, the user suspends the transaction and makes it impossible to complete. This reduces system throughput because any locks held by a transaction are released only when the transaction commits or rolls back. Even if a deadlock is not present, other transactions that access the same resource are blocked waiting for the transaction to complete.

3, keep the transaction short and in a batch process

Deadlocks usually occur when multiple long-running transactions are executed concurrently in the same database. The longer the transaction is run, the longer it will hold the exclusive lock or update the lock, blocking other activities and possibly causing deadlocks.

Keep transactions in a batch, you can minimize the network traffic round-trip to the transaction, reduce the possible delay in completing the transaction, and release the lock.

4. Use low isolation Level

Determines whether a transaction can run at a lower isolation level. Performing a commit read allows the transaction to read data that has been read (unmodified) by another transaction without waiting for the first transaction to complete. The use of lower isolation levels (for example, commit reading) without high isolation levels (such as serializable reading) reduces the time to hold shared locks, thereby reducing lock contention.

5. Use bound connection

Use a bound connection to enable two or more connections that are open by the same application to cooperate with one another. Any locks acquired by a secondary connection can be held in the same way as locks obtained by the primary connection, and vice versa, so that they do not block each other

6. Select statement Plus with (NOLOCK) hint

By default, the SELECT statement adds an S lock (shared lock) to the queried resource. The S lock is incompatible with the X lock (exclusive lock), but with the with (NoLock), select does not lock the queried resource (or add sch-s lock, Sch-s lock can be compatible with any lock); This can be the two SQL that can access the same resource concurrently. Of course, this method is appropriate for resolving read and write concurrency deadlocks, but with NoLock may cause dirty reads.