Deadlock in the database

When talking about deadlocks in data, we have already encountered these deadlocks in the exam. Today, I would like to make a summary of database deadlocks!

I. First, let's look at several definitions:
1. deadlock: a deadlock occurs when two or more processes compete for resources during execution. If there is no external force, they will not be able to proceed. It is said that the system is in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlock processes. Because resource usage is mutually exclusive, after a process applies for resources, the relevant process will never be allocated with necessary resources and cannot continue to run without external assistance, this produces a special deadlock.

2. database is a warehouse that organizes, stores, and manages data according to the data structure.

3. Conditions for deadlock

• Mutex: A process schedules the resources allocated to it. That is, a resource is only occupied by one process within a period of time. If there are other processes requesting resources at this time, the requester can only wait until the processes occupying the resources are released after use.
• Request and retention conditions: the process has maintained at least one resource, but has made new resource requests. The resource has been occupied by other processes. At this time, the request process is blocked, however, you do not need to release other resources you have obtained.
• Non-deprivation condition: resources obtained by a process cannot be deprived before they are used up. They can only be released by themselves when they are used up.
• Loop wait condition: When a deadlock occurs, there must be a process-a loop chain of resources, that is, a process set {P0, P1, P2 ,···, p0 in PN} is waiting for resources occupied by P1; P1 is waiting for resources occupied by P2 ,......, PN is waiting for resources occupied by P0.

4. Basic methods for handling deadlocks 1) prevent deadlocks.

This is a relatively simple and intuitive method of pre-prevention. The method is to set certain conditions to destroy one or more of the four conditions necessary to generate a deadlock to prevent deadlock. Deadlock Prevention is an easy-to-implement method and has been widely used. However, due to the strict restrictions imposed, system resource utilization and system throughput may be reduced.

2) avoid deadlocks.

This method is also a pre-prevention policy, but it does not need to take various restrictions in advance to damage the four necessary conditions for deadlock, but in the process of dynamic resource allocation, some method is used to prevent the system from entering an insecure state, so as to avoid deadlocks.

3) detect deadlocks.

This method does not have to take any restrictive measures in advance, nor does it have to check whether the system has entered the unsafe zone. This method allows the system to issue a life-and-death lock during operation. However, through the detection mechanism set up by the system, the deadlock can be detected in a timely manner, and the process and resources related to the deadlock can be accurately identified, and then appropriate measures can be taken, clear deadlocks from the system.

4) Remove the deadlock.

This is a complementary measure to the Deadlock Detection. When a dead or dead lock is detected in the system, the process must be freed from the deadlock state. A common implementation method is to undo or suspend some processes to recycle some resources, and then allocate these resources to the blocked processes so that they are ready to continue running. Deadlock Detection and removal measures may make the system obtain better resource utilization and throughput, but the implementation is also the most difficult.
2. What is the deadlock in the database!
1. SQL Server Lock Mechanism

All SQL Server activities produce locks. The smaller the lock unit, the more concurrent processing capability the higher the management lock overhead. It is difficult for SQL Server to find a balance between concurrency and performance.
SQL Server has the following types of conveniences:
1. Shared lock
Used for read-only operations (select) to lock shared resources. The shared lock does not stop other users from reading, but prevents other users from writing and modifying.
2. Update the lock
An update lock is an intent lock. An update lock occurs when a transaction has requested a shared lock and tries to request an exclusive lock. For example, when two transactions use a shared lock on several rows of data and try to obtain an exclusive lock to perform the update operation, a deadlock occurs: they are waiting for the other party to release the shared lock and implement the exclusive lock. Update locks are designed to obtain update locks only for one thing to prevent this situation.
3. exclusive lock)
Only one exclusive lock can be used on one resource at a time, and all other locks including shared contraction are blocked. Write is an exclusive lock, which effectively prevents 'dirty read'
4. Intention lock
Use intent locks before using shared and exclusive locks. View intent locks at the table level to determine whether a transaction can obtain a shared lock or an exclusive lock. This improves the system performance and does not need to be checked by the parent or row.
5. Plan lock
Sch-M, Sch-s. Sch-M is used for database structure change and sch-S is used for query compilation. These two locks do not block any transaction locks, including exclusive locks.

Read is a shared lock, write is an exclusive lock, and then update is an update lock. When the update lock is successful and the data is changed, the update lock is upgraded to the exclusive lock.

2. Let's take a look at how to avoid deadlocks.

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;
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. All SP servers must handle errors (via @ error)
4. Do not modify the default transaction level of SQL Server. Force lock not recommended
5. Optimize the program and check and avoid deadlock;
1) Reasonably arrange the table access sequence
2) Avoid user intervention in transactions as much as possible, and minimize the number of tasks processed by a transaction.
3) use dirty read technology. Dirty reads avoid lock conflicts because they do not lock the accessed table. In the Client/Server application environment, some transactions are often not allowed to read dirty data, but under specific conditions, we can use dirty read.
4) Time-Domain discretization for data access. The time-domain Discretization Method for Data Access refers to various control measures used in the Client/Server structure to control the access time periods for objects in the database or database. The following methods are used to reasonably arrange the execution time of background transactions and manage background transactions using workflows. When a workflow manages a task, it restricts the number of threads of the same type of tasks (usually one) to prevent excessive resource occupation. On the other hand, it rationally schedules the time series and times of running different tasks, avoid executing multiple background tasks at the same time, and avoid running background tasks at the front-end transaction peak hours.
5) data storage space discretization method. The data storage space discretization method is used to distribute data in a logical table to several discrete spaces, so as to improve the table access performance. The following methods are used: first, a large table is divided into several small tables by row or column. Second, it is divided by different user groups.
6) Use a minimum isolation level. Isolation level refers to the degree of multi-user transaction isolation to ensure the integrity and consistency of database data. sql92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency to a greater extent for better isolation, however, the chance of deadlocks due to conflicts between transactions is greatly increased, which greatly affects the system performance.
7) Use bound connections. Bound connections allows two or more transactions to connect to share transactions and locks, and any transaction connection must apply for locks as if another transaction wants to apply for locks, therefore, you can allow these transactions to share data without locking conflicts.
8) Consider using Optimistic Locking or giving the transaction an exclusive lock first.

Do you think there will be deadlocks in the next situation?
A. Select, update, selectb. Select, select, updatec. Select, update, updated. Update, select, and update. Which of the above four situations will lead to deadlocks and deadlocks? What is your reason?
3. Let's take a look at the deadlock problem below!

-- Transaction a first updates table 1 with a delay of 30 seconds, and then updates table 2; begin tranupdate Table1 set a = 'A' where B = 'b2 '; -- this will generate an exclusive row lock in Table1 until the transaction is completed. Waitfor delay '00: 00: 30'; -- enter the delayed update Table2 set D = 'd5 'where E = 'e1'; Commit tran -- B transaction first updates table 2, then, the table 1 is updated after a delay of 10 seconds. Begin tranupdate Table2 set D = 'd5 'where E = 'e1 '; -- this will generate an exclusive row lock in Table2, the lock waitfor delay '00: 00: 10' will not be released until the transaction is complete. -- enter the delayed update Table1 set a = 'A' where B = 'b2'; Commit tran

Transaction a first updates the table 1 table, then latencies of 30 seconds, and then updates the table 2; begin tranupdate Table1 set a = 'A' where B = 'b2 '; -- this will generate an exclusive row lock in Table1 until the transaction is completed. Waitfor delay '00: 00: 30'; -- enter the delayed update Table2 set D = 'd5 'where E = 'e1'; Commit tran -- B transaction first updates table 2, then, the table 1 is updated after a delay of 10 seconds. Begin tranupdate Table2 set D = 'd5 'where E = 'e1 '; -- this will generate an exclusive row lock in Table2, the lock waitfor delay '00: 00: 10' will not be released until the transaction is complete. -- enter the delayed update Table1 set a = 'A' where B = 'b2'; Commit tran
If the preceding two transactions are concurrently executed, both transactions A and B will wait for the other party to release the exclusive lock, thus forming a deadlock.