SQL Server 阻塞排除的 2 方法

標籤：blog   http   io   ar   資料   sp   2014   問題   c   

背景知識：

            是什麼造成了阻塞？

            從鎖的觀點來看、可訪問對象前一定要對對象加鎖不管你是讀還是寫，如果使用者A以經持有對象，說明A以在對象上加鎖，如果這時B

            也想訪問這個對象、它也要對對象加鎖。重點來了如果A使用者加的是排它鎖，B使用者可以做的就是乖乖等A用完再說。B乖乖等這個問題

            就是阻塞，是因為這兩把鎖不可以共存才造成了阻塞。

情境再現：

            為在再現情境我們要開三個通向資料的串連。就是說要開啟三個managerment studio 或是開三個sqlcmd。我用ssms(SQL Server Managerment Studio)為例

            先為情境做點準備，代碼如下。

            use Studio;
            go

            create table TestTable (ID int not null,String nchar(10));
            go

            declare @i as int =1;
            while @i<10
            begin
            insert into TestTable(ID,String) values(@i,REPLICATE(cast(@i as nchar(1)),10));
            set @i = @i +1;
            end;
            go

            select T.ID,T.String from TestTable as T;

            go

            

           串連A運行如下代碼：

                                    begin tran
                                    update dbo.TestTable
                                    set String = ‘AAA‘
                                    where ID=1;
                                    go --可以看到A會一直持有第一行上的排它鎖，直到事務被commit 或 rollback!

           串連B運行如下代碼：                             

                                    update dbo.TestTable
                                    set String = ‘BBB‘
                                    where ID=1; 

                                    go -- 因A以在ID=1這一行上有排它鎖，所以B只可以乖乖的等，這就造成了阻塞。可以看出B等了2分多鐘了。

                                    

           C串連用來解除阻塞，運行代碼馬上就講。

方法 1、 從鎖入手

                        第一步、看誰在等待資源，以確定誰被阻塞、運行如下代碼

                                 select
                                 tra.request_session_id as [SessionID],-- 會話名
                                 DB_NAME(tra.resource_database_id) as [DBName],-- 資料庫名
                                 tra.resource_type as [resourceType],-- 資源類型
                                 tra.resource_associated_entity_id as [ResourceID],--資源ID
                                 tra.request_status as [Status]  --狀態
                                 from sys.dm_tran_locks as tra
                                 where tra.request_session_id>50;
                                 go

                                 

                                 -- 中可以看出有一行是wait 說明它在等別人，釋放資源。

                                 所以 54 是在等別人，它等誰呢？從圖上可以看出 51 持有54在等待對象的排它鎖，說明54在等51。

                      第二步、定位等的是哪個資源：

                                                           運行如下代碼

                                                           select OBJECT_NAME(565577053);--565577053來自於圖上的倒數第二行。
                                                           go

                               

 

 

 

 

 

 

 

 

 

          

SQL Server 阻塞排除的 2 方法