Dead Lock)

Dead Lock)

 

A deadlock is a situation where processes block each other and a block loop is formed to prevent unblocks. A deadlock must occur between two or more processes. For two processes, process a blocks process B, and process B blocks process. if a deadlock occurs between three processes, process a blocks process B, process B blocks Process C, and process C blocks process. when SQL Server is dead, it will try to terminate a process. Of course, if the system does not stop a process or tries to stop a failed process, the deadlock process will remain deadlocked, unless manual intervention ends a process. If not specified, the system suspends the last process by default, because this is the best operation for the transaction to roll back. However, from SQL Server 2005, you can set the deadlock_priority (-10 ~ 10), 2008 supports the 21 values, but SQL 2005 only supports low and normal values. The system selects the process with the lowest priority to terminate. A. Open an SQL window (Window 1) and execute the following SQL statements.

 Begin Tran;

 

Update DBO. Products

Set unitprice = unitprice + 1.00

Where productid = 2; B. Open an SQL window (window 2) and execute the following SQL statement

Begin Tran;

 

Update [DBO]. [Order Details]

Set unitprice = unitprice + 1.00

Where productid = 2; C. Return to window 1 and execute

Select orderid, productid, unitprice

From [DBO]. [Order Details]

Where productid = 2;

 

Commit Tran;

Window 1 is blocked by window 2

D. Return to window 2 and execute

Select productid, unitprice

From DBO. Products

Where productid = 2;

Commit Tran; error detected in window 2: MSG 1205, Level 13, State 51, line 1 transaction (process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction. transaction execution is successful in window 1. How can we avoid and reduce deadlocks? 1. Obviously, the longer the transaction execution time, the more locks it will use, and the larger the chance of deadlock. Therefore, the shorter the transaction execution time, the better. Remove statements that are not logically the same transaction from the transaction as much as possible. 2. Some deadlocks are caused by incorrect resource access sequence. For example, if the deadlocks in the preceding example are all placed before the update statements, no deadlocks will occur. Therefore, the SELECT statement should be placed before update without affecting the transaction logic. the above example shows a logical deadlock because both processes attempt to access unified resources and perform different operations. However, some deadlocks are not logical deadlocks, but are not well planned indexes (queries are used or filtered ). for example, if two statements in window 2 are filtering product 5, and product 2 is being processed in window 1, while window 2 is processing product 5, it does not seem that a deadlock should occur, because there is no logical conflict. However, if productid is not set as an index, the system needs to scan (scan to lock) the entire product table to filter the product 5. In this case, a deadlock still occurs when product 2 is scanned. Therefore, good index planning can avoid this type of logical deadlock during concurrency.