sqlserver之定位死鎖(經驗分享)

很多人都遇到過這樣的情況，當網站達到一定的通路量，資料庫就會成為瓶頸，有可能發生死鎖，進而引起阻塞。

有人認為這可能就是硬體的極限了，于是想辦法增加硬體裝置。而我本人認為問題的元兇可能是性能不高的sql腳本，引起了死鎖，進而導緻阻塞。

如果你和我有相同的看法，那我們就一起想辦法找出問題的源頭。

案例1.

某一天我被告知，我們的書城網站不能通路了，我馬上檢視，發現書城的有兩台iis伺服器均顯示service unavailable，如果我斷定是sqlserver資料庫發生了死鎖。

要知道是否發生了死鎖，當然要看master庫的sysprocess表,看看是否有什麼程序堵住了别的程序,語句如下:

Select * from master..sysprocesses where blocked > 0

很快我發現，有一個blocked = 51 堵住了很多程序(檢視blocked列可見)，果然和我的判斷吻合；為了進一步找出發生死鎖的語句，我用到的如下的語句

dbcc inputbuffer(51);

結果如下：

EventType      Parameters            EventInfo           

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

RPC Event      0                          p_Book_content;1

從上面就可以看出是p_Book_content 這個過程引起的阻塞，但是這個過程裡面同時對多個表進行了操作，到底是那個語句出了問題呢？

下面我們再來進一步定位死鎖的位置：

Sp_lock

結果如下（大部分資料略）

Spid        dbid        objid          indid      type  resource  mode      status

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

51          14          206623779    0        TAB                X            WAIT

52          14          0            0        DB                S            GRANT

53          14          0            0        DB                S            GRANT

。。。

現在我們來看看spid = 51 這行, mode = X 表示排它鎖, status = WAIT表示正在等待（即阻塞了），dbid = 14 是資料庫的id，objid = 206623779 是被鎖的對象id,我們可以通過下列函數得到資料庫和表:

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

select object_name(@objid) -------》 t_book

即book_db庫的t_book表被鎖住了,這時候再回投仔細檢查 p_Book_content 存儲過程，發現隻有一個語句對t_book進行了操作:

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

這個語句的作用是更新書本的點選次數，為什麼上面這個語句會引起死鎖呢？我認為最可能的情況應該是同時通路的人過多，同時對表進行過多的update操作引起的，是以最終改用别的方式，不再實時對t_book表進行update操作，而是每次通路都先insert一條記錄到一個中間表中，然後再用一個作業，每隔10分鐘定時更新書本的點選次數，如此改進之後，此問題終于圓滿解決了。

相關資料在book online可以找到, 關鍵字: sp_lock , sysprocesses , dbcc inputbuffer , db_name(), object_name()