Deadlock tracking experience

Recently, when I first moved to a new company, I suddenly experienced a large volume of interruptions in the production database at night and lasted for nearly an hour. In the event of an accident, I was not at the scene,
I missed the opportunity to directly obtain information. After that, my boss asked me to trace the cause, so the difficult troubleshooting process began.
At first, I thought it was caused by an exception in the running of a Database Job or becauseProgramWhich of the following birds writes a spam statement in it causes a large deadlock.
The trace information is obtained for local analysis. From the trace information collected, the database encountered a lock at 19:49:28, and the system cancel it, which is three consecutive times,
After that, most of the database connections are abort.
 

The preliminary estimate should be a deadlock. The first thought was due to the database update statement, so I checked whether there was a job running at the corresponding time in the agent,
The results do not match. Then, analyze whether the trace file contains long update, insert, or delete statements that run during the specified time period.
Discovery, sweat..., investigation long query, still not, crazy sweat...

No way to locate specific statements when analyzing the trace file (the trace file only captures records whose running time exceeds 2 seconds or reads more than 10000 ).
The problem is not that simple. It is impossible to find the murderer based on the trace file information, so I checked both the Windows Log and the database error log.
No exceptions are found. Isn't it an outstanding case... (I have not found any information, and I am worried that my job will not be guaranteed)

I want to come and ask some cool people, but there is no result. It seems that there is little hope to find out the problem through the existing materials on hand. I can only find a new path.
But it must be caused by a deadlock. It indicates that there must be inconsistent access or competition for resources in the database, so we can start with the deadlock and first clear the current number.
In the Database Error Log File, open the 1204 and 1222 trace marks and wait for the fish to hook up.

 DBCCErrorlog
DBCCTraceon (1204,1222,-1);
DBCCTracestatus
 

After collecting data for a few days, prepare to collect the network, copy error. log from the server to the local machine, open it with ue, read the line carefully, and find the following information:
Deadlock encountered... printing deadlock Information
It was a deadlock. I finally found the real culprit. I used ue to search for it. I don't know. I was shocked by a check and a bunch of deadlocks...
 

The deadlock information is as follows:
 

View the page information:

DBCC  Traceon (  3604  )
  DBCC  Page (  '  Xxxx  '  ,  1  ,  22664690  ,  3  ) With  Tableresults
  DBCC  Traceoff (  3604  ) 

The page information is normal.

Based on the information, the information of the input Buf, in turn, queries the trace file (the input buffer can only store 255 bytes, the information is not displayed completely, and can only be searched in turn ):

 Select     Top     10     *     From Fn_trace_gettable (  '  F: \ perfmon \ trace_xxxx_20110406a.trc  '  ,  Default  )
  Where  Textdata  Like     '  % @ Smessage varchar (8000), @ sinmaterecid varchar (8000), @ srefrecid varchar (8000) %  ' 

 

 Select    Top     10     *     From  Fn_trace_gettable (  '  F: \ perfmon \ trace_xxxx_20110406a.trc  '  ,  Default  )
  Where  Textdata Like     '  % @ P0 _ varchar (7), @ P1 _ datetime, @ P2 _ datetime, @ P3 _ varchar (7), @ P4 _ datetime %  ' 

 

It is found that the deadlock occurs on the xxx_main table. One is modification and the other is query. The data volume of this table is more than 700, and the other is perspiration .....
 

However, it is strange that the information displayed by the deadlock is some simple modifications (single record modifications) and query operations, so it will not cause so many deadlocks. Continue to the first step...

After tracing, we found that these two operations were frequent in the database (trace records for one day ):

  select     count   (  0  )   from   fn_trace_gettable (  '  F: \ perfmon \ trace_xxxx_20110406a.trc   ' ,   default  ) 
   where   textdata   like     '  % @ P0 _ varchar (7), @ P1 _ datetime, @ P2 _ datetime, @ P3 _ varchar (7 ), @ P4 _ datetime %   ' 

  select     count   (  0  )   from   fn_trace_gettable (  '  F: \ perfmon \ trace_xxxx_20110406a.trc   ' ,   default  ) 
   where   textdata   like     ' %@ smessage varchar (8000), @ sinmaterecid varchar (18), @ srefrecid varchar (8000) %   ' 

 

The number of deadlocks in a day is about 20, and there is only one record at the update, but why is there such a frequent deadlock? Besides the large data volume of the table
In addition to super abnormal queries (which is the main cause later found), is there any other reason?

Finally, I couldn't find the problem, and reflected the problem to Microsoft. After several days of communication with Microsoft, the final result was a database bug (it was lucky to vomit blood ),
Microsoft replied as follows:
Problem Analysis:
==========
Your database version is: Microsoft SQL Server 2005-9.00.4207.00 (Intel IA-64) Enterprise Edition, SP3 cu1
I checked the deadlock information you provided found that, as you said, your deadlock problem is caused by sql2005 bug, for details can refer to the document:

Solution:
================
You can install SP3 cu6 separately or install SP4 directly to solve this problem.
SP3 cu6: http://support.microsoft.com/kb/974648>
SP4: http://www.microsoft.com/downloads/en/details.aspx? Familyid = b953e84f-9307-405e-bceb-47bd345baece>
Your SQL Server is of the IA64 version, so select the correct installation file

------- Supplement ---

Although Microsoft has provided a possible reason (not yet reliable), it does not recommend patching for the boss based on the current system situation (which should be done during regular maintenance ).

If I want to propose an improvement solution and have the above tracing information, it is not difficult to come up with an improvement solution. So I mail it to the boss:

Based on this information, we can know:

1. The deadlock mainly occurs on the xxx_main table, which has more than 7 million data records;
2. It is mainly caused by a deadlock between an update statement and a SELECT statement. These two statements run frequently and query statements are very long (see the attachment. Such queries cannot use indexes );
3. The main problem lies in the complex query and table data volume (including the bug mentioned by Microsoft );
4. Currently, such deadlocks often occur in our databases.

To handle this situation, we recommend the following:

1. Whether the table information can be separated to reduce the data volume;
2. Can developers improve this query statement;
3. Whether the query can consider adding with (nolock), the update statement can add with (rowlock );
4. Others (partition tables and so on ).

Finally, we moved some of the data in the table, modified the query statement, and added with (nolock) to solve the problem (for Microsoft's solution, see the next decomposition)