Database General Deadlock handling method

1. Simulate a deadlock first:

1 CREATE TABLELock1 (C1int default(0));2 CREATE TABLELock2 (C1int default(0));3 INSERT  intoLock1VALUES(1);4 INSERT  intoLock2VALUES(1);5 6 --put the following code into the two Query Analyzer windows, respectively:7 Analyze a window:8 Begin Tran9   UpdateLock1SetC1=C1+1;Ten   WaitForDelay'00:01:00'; One   SELECT *  fromLock2 A Rollback Tran; - Analysis Two window: - Begin Tran the   UpdateLock2SetC1=C1+1; -   WaitForDelay'00:01:00'; -   SELECT *  fromLock1 - Rollback Tran;

A deadlock prompt will appear after execution:

The transaction (Process ID 54) is deadlocked on the lock resource with another process and has been selected as the deadlock victim. Please rerun the transaction.

If you turn on database monitoring, you will see the deadlock graph visually, and the deadlock graph described by the database monitor can be used to derive which SQL statement is causing the deadlock.

At this point the SQL statement that caused the deadlock is found at the time of the query: SELECT * from Lock2 with (NOLOCK) (plus this) can avoid deadlocks. Note that this approach causes (dirty reads) that if the system business does not require a high level of dirty reads, this method can be used to resolve deadlocks.

Database General Deadlock handling method