The formation and handling of deadlocks

First, the principle of deadlock

A, according to the definition in the operating system: A deadlock is a set of processes in which each process occupies a resource that is not freed, but is in a permanent state of waiting for each other to request resources that are not freed by other processes.

Ii. four necessary conditions for deadlocks:
A, mutex condition (Mutual exclusion): The resource cannot be shared and can only be used by one process.
B. Request and hold conditions (holds and wait): The process that has already received the resource can request a new resource again.
C, non-deprivation conditions (no pre-emption): The allocated resources cannot be forcibly deprived from the corresponding process.
D, cyclic wait condition (Circular wait): Several processes in the system make up loops in which each process waits for resources that are being consumed by neighboring processes.

Third, avoid deadlock

A, access the object in the same order.

b, avoid user interaction in the transaction.

c, keep the transaction short and in a batch.

D, use a lower isolation level.

Iv. Viewing the deadlock example

Open two query windows, execute the following two paragraphs of SQL respectively

BEGIN Tran
UPDATE a SET dd=dd+1

WaitFor Delay ' 00:01:00 ';
SELECT * from B

ROLLBACK Tran

BEGIN Tran
UPDATE B SET age=age+1
WaitFor Delay ' 00:01:00 ';
SELECT * from A
ROLLBACK Tran

V. Solutions for deadlock situations in the above statement

A, according to the above mentioned in the same order to access the object (Exchange Update and select execution order)

BEGIN Tran
SELECT * FROM A --Before the SELECT statement is placed, the UPDATE statement is placed after
WaitFor Delay ' 00:00:30 ';
UPDATE B SET age=age+1
ROLLBACK Tran

B, with the SELECT statement Plus with (NoLock), Plus with (NoLock) may cause dirty reads.

BEGIN Tran
UPDATE B SET age=age+1
WaitFor Delay ' 00:00:10 ';
The SELECT * from A with (NOLOCK)--select statement is prefixed with the with (NOLOCK), effectively avoiding deadlocks
ROLLBACK Tran

is also the statement in example one, with the addition of the WITH (NOLOCK)

BEGIN Tran
UPDATE a SET dd=dd+1

WaitFor Delay ' 00:00:10 ';
SELECT * from B with (NOLOCK)

ROLLBACK Tran

Results:

c, before the SQL statement with set LOCK_TIMEOUT, the data default Lock_timeout time is 10 minutes

BEGIN Tran
SET Lock_timeout 3000

UPDATE a SET dd=dd+1

--waitfor Delay ' 00:00:10 ';
SELECT * from B

ROLLBACK Tran

--The execution of the current SQL will be terminated in 3 seconds without affecting subsequent execution efficiency

The formation and handling of deadlocks