sqlserver 中死結相關問題

標籤：sqlserver 死結

死結定義：

所謂死結就是兩個線程或多個線程在擁有一部分資源的同時還需要擁有其他資源，

但是其他資源被其他線程佔有，每個線程為了獲得其他線程佔有的資源都處於一個相互等待

的狀態，這個時候如果沒有外界力量破壞這種相互等待的狀態或是某個(些)線程自動放棄已經佔有的資源，

那麼所有的線程都無法完成任務，這個時候系統處於一個僵死狀態。這就是所謂的死結。

sqlserver自身有個鎖監視器(Lock monitor),一旦發現死結，SqlServer會kill掉一個其中一個線程，

使另外一個(些)繼續完成任務。該死結監視器是以一個後台線程的形式存在的，

可以通過系統檢視表sys.dm_exec_requests查看

select * from sys.dm_exec_requests

where session_id  < 50

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR2kfSa3k_AAEpAtlKuu0876.jpg" title="1.jpg" alt="wKiom1RR2kfSa3k_AAEpAtlKuu0876.jpg" />

但是僅僅依靠SqlServer自身的死結監視器來解決死結是不行的，雖然SqlServer暴力解決了死結的問題

但是這種的暴力解決會影響系統的效能的，對於高並發的效能是不可取的。

接下來來瞭解下死結的常見形式，弄清楚產生死結的原因，然後再通過重寫sql或是建立索引或是修改商務邏輯

來解決死結問題。

建立測試表和資料

create table testklup

(

clskey int not null,

nlskey int not null,

cont1  int not null,

cont2  char(3000)

)

create unique clustered index inx_cls on testklup(clskey)

create unique nonclustered index inx_nlcs  on testklup(nlskey) include(cont1)

insert into testklup select 1,1,100,‘aaa‘

insert into testklup select 2,2,200,‘bbb‘

insert into testklup select 3,3,300,‘ccc‘

死結的幾種表現形式

1.資料行的爭用

會話一：

begin tran  

select * from dbo.testklup with(updlock) 

where clskey=1

 

waitfor delay ‘00:00:10‘

update dbo.testklup 

set cont1=200 

where clskey=2

會話二：

begin tran  

select * from dbo.testklup with(updlock) 

where clskey=2

 

waitfor delay ‘00:00:10‘

update dbo.testklup 

set cont1=200 

where clskey=1

先執行會話一，然後執行會話二 ，會出現如下死結相關資訊

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4D/78/wKiom1RR2n-z-KrQAAFuGARrciE721.jpg" title="2.jpg" alt="wKiom1RR2n-z-KrQAAFuGARrciE721.jpg" />

產生死結的原因：

會話一先對clskey=1的資料行添加了一個更新鎖定,然後它想要對

clskey=2的資料行添加排它鎖，但是這個時候clskey=2已被會話二添加了

共用鎖定，會話二還想對clskey=1 對添加排它鎖，但是clskey=1已經被會話一添加了 共用鎖定.

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3LHDoJMHAAFwBM-pgMU671.jpg" title="4.jpg" alt="wKiom1RR3LHDoJMHAAFwBM-pgMU671.jpg" />

解決方案：例一種的產生死結的原因很簡單就是沒有按照一個固定的順序訪問資料庫中的對象，

解決此類死結問題就需要修改商務邏輯或是sql語句了，按照一個固定的順序訪問資料庫中的對象

2.索引值的爭用。這種爭用我理解了很久才弄明白

會話一：

 declare @i int

set @i=100

while 1=1

 begin 

  update testklup  set [email protected] 

  where clskey=1

  set @[email protected]+1

 end

執行計畫如下：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3dbzxdd2AAHcd5klERk738.jpg" title="5.jpg" alt="wKiom1RR3dbzxdd2AAHcd5klERk738.jpg" />

會話二：

declare @cont2 char(3000)

while 1=1

begin

    select @cont2=cont2 from testklup  where nlskey=1

end

執行計畫如下：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3hajKlIBAAHGR8RCUBw045.jpg" title="6.jpg" alt="wKiom1RR3hajKlIBAAHGR8RCUBw045.jpg" />

先執行會話一再執行會話二 ，結果如下:

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3RjDwj8-AAFjHjLG6yI498.jpg" title="3.jpg" alt="wKiom1RR3RjDwj8-AAFjHjLG6yI498.jpg" />

產生死結的原因：

會話一根據主鍵clskey=1找到資料行後在資料行添加了一個更新鎖定(U)，找到資料後，準備修改資料，

更新鎖定轉換為排它鎖(X),在更新完表資料後再去更新非叢集索引中的列(cont1)

(create unique nonclustered index inx_nlcs  on testklup(nlskey) include(cont1)) ,

所以會話一會對非叢集索引添加排它鎖，完成資料的更新。

再看會話二,會話二走非叢集索引獲得目標資料行的主鍵ID，那麼會話二就會對非叢集索引添加共用鎖定，

當會話二獲得主鍵ID後還要通過叢集索引獲得列cont2的值，那麼回話二就會對主鍵行添加共用鎖定

再看下回話一和會話二訪問的資料，其實都是同一條資料，

當會話一對非叢集索引添加排它鎖，會話二已經對非叢集索引添加了共用鎖定了，因此會話一等待會話二釋放共用鎖定

當會話二對叢集索引添加共用鎖定時，會話一已經對叢集索引添加了排它鎖，會話二等待會話一釋放排它鎖

這個時候就死結了。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4D/79/wKioL1RR4Fnh5c3oAAFwBM-pgMU980.jpg" title="4.jpg" alt="wKioL1RR4Fnh5c3oAAFwBM-pgMU980.jpg" />

解決方案：

方案一：不讓會話二訪問叢集索引

那麼需要修改非聚集create unique nonclustered index inx_nlcs  on testklup(nlskey) include(cont2,cont1)

方案二:不讓會話一更新非叢集索引

依然需要修改非叢集索引create unique nonclustered index inx_nlcs  on testklup(nlskey)

相關參考連結：

http://www.cnblogs.com/shanksgao/p/3904662.html

本文出自 “SQLServer MySQL” 部落格，謝絕轉載！

sqlserver 中死結相關問題