How to avoid deadlocks in sqlserver

I. Four Conditions for deadlock 1. Mutualexclusion: resources cannot be shared and can only be used by one process. 2. Request and retention condition (Holdandwait): processes that have obtained resources can apply for new resources again. 3. Nopre-emption: allocated resources cannot be forcibly stripped from the corresponding process.

I. Four Conditions for deadlock 1. Mutual exclusion: resources cannot be shared and can only be used by one process. 2. Hold and wait: processes that have obtained resources can apply for new resources again. 3. No pre-emption: allocated resources cannot be forcibly stripped from the corresponding process.

I. Four Conditions for deadlock

1. Mutual exclusion: resources cannot be shared and can only be used by one process.

2. Hold and wait: processes that have obtained resources can apply for new resources again.

3. No pre-emption: allocated resources cannot be forcibly deprived from the corresponding process.

4. Loop wait condition (Circular wait): several processes in the system form a loop, in which each process is waiting for resources occupied by adjacent processes.

Ii. avoid deadlocks

1. access objects in the same order

If all concurrent transactions access objects in the same order, the possibility of deadlock will be reduced. For example, if two concurrent transactions obtain the lock on the Supplier table and then obtain the lock on the Part table, the other transaction is blocked on the Supplier table before one transaction is completed. After the first transaction is committed or rolled back, the second transaction continues. No deadlock occurs. The stored procedure is used to modify all the data to standardize the order of objects to be accessed.

2. Avoid user interaction in transactions

Avoid writing transactions that contain user interaction, because running batch processing without user interaction is much faster than manually responding to queries, for example, responding to application request parameter prompts. For example, if the transaction is waiting for user input, and the user goes to lunch or even goes home for a weekend, the user suspends the transaction so that it cannot be completed. This will reduce the system throughput, because any lock held by the transaction will be released only when the transaction is committed or rolled back. Even if no deadlock occurs, other transactions accessing the same resource will be blocked, waiting for the transaction to complete.

3. Keep the transaction brief and in a batch

A life-and-death lock is often used to concurrently execute multiple transactions that require long running in the same database. The longer the transaction runs, the longer it takes to hold the exclusive lock or update the lock, blocking other activities and possibly causing a deadlock.

Keeping transactions in one batch can minimize the number of network communications to and from the transaction, reduce the possible latency of completing the transaction and release the lock.

4. Low isolation level

Determine whether the transaction can run at a lower isolation level. The committed read operation allows the transaction to read the data that has been read (not modified) by another transaction without waiting for the completion of the first transaction. Using a lower isolation level (for example, commit read) instead of a higher isolation level (for example, serializable read) can shorten the time for holding shared locks, thus reducing lock contention.

5. Bind a connection

Bind the connection so that two or more connections opened by the same application can cooperate with each other. Any lock obtained by the secondary connection can be held as the lock obtained by the primary connection, and vice versa, so it will not block each other.

6. the prompt "With (NoLock)" is added to the SELECT statement.

By default, the SELECT statement will apply the S lock (shared lock) to the queried resources. The S lock is incompatible With the X lock (exclusive lock). However, after the With (NoLock) is added, the SELECT statement does not lock the queried resources (or the Sch-S lock is applied, and the Sch-S lock can be compatible with any lock). The two SQL statements can concurrently access the same resource. Of course, this method is suitable for solving read and write concurrency deadlocks, but adding With (NoLock) may cause dirty read.