SQL Server deadlock identification (Experience Sharing)

Many people have encountered this situation. When the website reaches a certain access volume, the database will become a bottleneck, and a deadlock may occur, leading to blocking.

Some people think that this may be the limit of hardware, so they want to increase hardware devices. I personally think the culprit of the problem may be the SQL script with low performance, which causes a deadlock and blocking.

If you and I share the same view, we can work together to find out the source of the problem.

Case 1.

One day I was told that our bookstore website could not be accessed. I immediately checked and found that two IIS servers in bookstore showed service unavailable, if I conclude that the SQL Server database has a deadlock.

To check whether a deadlock has occurred, check the sysprocess table of the master database to see if any process blocks other processes. The statement is as follows:

Select * from Master .. sysprocesses where blocked> 0

Soon I found that a blocked = 51 blocked a lot of processes (view the blocked column visible), which is indeed consistent with my judgment; in order to further find a deadlock statement, the following statements are used:

DBCC inputbuffer (51 );

The result is as follows:

Eventtype parameters eventinfo

------------------------------------------------

RPC event 0 p_book_content; 1

We can see from the above that it is caused by the p_book_content process. But in this process, multiple tables are operated at the same time. What is the problem with the statement?

Next we will further locate the deadlock location:

Sp_lock

The result is as follows (most data is omitted)

Spid dbid objid indid type resource mode status

Bytes -------------------------------------------------------------------------------------------------

51 14 206623779 0 tab X wait

52 14 0 0 dB S Grant

53 14 0 0 dB S Grant

...

...

...

Now let's take a look at the spid = 51 line, mode = X indicates the exclusive lock, status = wait indicates waiting (blocking), dbid = 14 indicates the database ID, objid = 206623779 is the ID of the locked object. We can use the following functions to obtain the database and table:

Select db_name (@ dbid) ----- "book_db

Select object_name (@ objid) ------- "t_book

That is, the t_book table of the book_db database is locked. At this time, check the p_book_content stored procedure and find that only one statement operates on the t_book:

Update t_book set hitcount = hitcount + 1 where bookid = @ bookid

This statement is used to update the number of book clicks. Why does the above statement cause a deadlock? In my opinion, the most likely situation is that too many people access the table at the same time and too many update operations on the table, so I finally switched to another method instead of performing update operations on the t_book table in real time, instead, insert a record to an intermediate table for each access, and then use a job to update the book clicks every 10 minutes. After this improvement, the problem is finally solved.

Relevant information can be found on book online. Keywords: sp_lock, sysprocesses, DBCC inputbuffer, db_name (), object_name ()