Locating database deadlocks, and initial resolution of deadlock methods (striking)

1. At the moment I just succeeded in experimenting with SQL 2008:

To turn on the trace identity in the database:

DBCC TRACEON (1204,-1)
DBCC TRACEON (1222,-1)

Both trace flags write deadlocks to the error log, but 1204 is in text format and 1222 is saved in XML format.

When turned on, you can find the statement that triggered the deadlock by looking at the database log file.

1. Simulate a deadlock:

1 DBCCTRACEON (1204,-1)2 DBCCTRACEON (1222,-1)3 4 CREATE TABLELock1 (C1int default(0));5 CREATE TABLELock2 (C1int default(0));6 INSERT  intoLock1VALUES(1);7 INSERT  intoLock2VALUES(1);8 9 Open the following script to two Query Analyzer windows, respectively:Ten  One 1. A Begin Tran -   UpdateLock1SetC1=C1+1; -   WaitForDelay'00:01:00'; the   SELECT *  fromLock2 - Rollback Tran; -  - 2. + Begin Tran -   UpdateLock2SetC1=C1+1; +   WaitForDelay'00:01:00'; A   SELECT *  fromLock1 at Rollback Tran;

After the execution is complete, the details of the deadlock will appear a moment later:

At this point we look at the database log:

The deadlock is there, and the database log has it. It's all set. Start analysis with deadlock instructions and logs:

Dead Lock Popular Description:

At the time of the deadlock, the SQL statement that the process executes for 54 and the SQL statement of the other process generate a deadlock, but the system chooses a lightweight process as the victim, killing the process, but because it is the SQL statement of the other process that caused the deadlock. So you must first find the SQL statement for another process, and the deadlock resolution is just around the corner.

Combined with the current deadlock description, we know that the process is 54 of this is not the cause of the deadlock, then the log file inside the process is 53 of this SQL statement induced deadlock.

1 SQL statement executed by Process 53:2 3 Begin Tran4   UpdateLock1SetC1=C1+1;5   WaitForDelay'00:01:00';6   SELECT *  fromLock27 Rollback Tran;

The remedy for the crux:

Combine with the information on the net, use with (NOLOCK) to solve. With (NOLOCK) is only valid for the deadlock caused by select, and you can use this method if you are not very concerned about the possible dirty reads, otherwise you will have to find another way.

Specific as follows:

　　

Begin Tran  Update Set C1=C1+1;   WaitFor ' 00:01:00 ' ;   SELECT *  from whth (NOLOCK) Rollback Tran;

At this point in the SQL script that will impersonate the deadlock, you will notice that there is no deadlock as follows:

The above views on SQL is purely my humble opinion, but also hope to be a catalyst. There is said the wrong place, but also hope that you give the younger brother to say a bit.

The final explanation:

Insufficient: This way can only wait for the deadlock to take place and begin to solve. Strategically subordinate to the post-development.

Locating database deadlocks, and initial resolution of deadlock methods (striking)