How to avoid deadlocks

What is deadlock and how to avoid deadlock?
Thread a requires resource X, thread B requires resource y, and both parties have the resources required by the other party. This situation is called a deadlock or the deadly embrace ).

In concurrent programming, deadlock (deadlock) is a very common logical error. By using the correct programming method, deadlock is not difficult to avoid.

Four Conditions for deadlock

------------------------------

In computer science, the following four conditions are usually introduced. These four conditions are indispensable, or a deadlock cannot occur as long as any of them is damaged. Let's review these four conditions:

1. Mutual Exclusion: A resource can only be allocated to one thread at a time;
2. Hold and wait: When the requested resource is occupied and execution is blocked, the resource owner not only does not need to release the resource, but also can continue to request more resources;
3. No preemption: the mutex resources obtained by the execution thread cannot be forcibly deprived. In other words, only the resource owner can release the resource;
4. Loop wait (circular wait): A number of threads obtain mutually exclusive resources in different order to form a loop wait. imagine in a ring chain composed of multiple threads, each thread is waiting for the next thread to release its resources.

Conditions for deadlock relief

It is not hard to see that the second, third, and fourth conditions of the deadlock are easier to eliminate. By introducing the transaction mechanism, you can usually eliminate the second and third conditions by treating all lock operations as transactions. Once locks are started, all operations can be rolled back, at the same time, the lock manager is used to detect deadlocks and deprive resources (rollback transactions ). This practice sometimes leads to a higher sales margin and requires many changes to the locking mode.

The fourth condition is a relatively easy and inexpensive solution. Specifically, the locking sequence must be consistent. Specifically, we artificially specify a direction attribute similar to the "Water Level" for the lock. Regardless of whether any lock is held, all lock operations of the thread must be performed in a consistent order from low to high (or from high to low) and in a system, only one sequence is allowed.

Please note that the locking sequence does not cause deadlocks. That is to say, although it looks a little weird to perform the lock operation in the order of lock a, lock B, put a, and put B, as long as everyone locks the lock in the order of first A and then B, this will not cause deadlocks.

Solution:

1. When using a transaction, try to shorten the logical processing process of the transaction and commit or roll back the transaction as soon as possible; (refine the processing logic and roll back or commit after a logic is executed, then execute other logic until the transaction is submitted)
2. Set the deadlock timeout parameter to a reasonable range, for example, 3 minutes to 10 minutes. If the timeout period is exceeded, the operation is automatically abandoned to avoid process suspension;
3. Optimize the program to check and avoid deadlock;
4. All scripts and SP should be carefully tested before the version is correct.
5. All SP should have error handling (via @ error)
6. do not modify the default transaction level of SQL Server. Force lock not recommended

In addition, refer to the solution:

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.

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.

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.

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.

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.

How to avoid deadlocks