deadlocks（死鎖）

所謂死鎖: 是指兩個或兩個以上的程式在執行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去.此時稱系統處於死鎖狀態或系統產生了死鎖,這些永遠在互相等待的程式稱為死鎖程式.

由於資源佔用是互斥的，當某個程式提出申請資源後，使得有關程式在無外力協助下，永遠分配不到必需的資源而無法繼續執行，這就產生了一種特殊現象死鎖。

一種情形，此時執行程式中兩個或多個執行緒發生永久堵塞（等待），每個執行緒都在等待被其他執行緒佔用並堵塞了的資源。例如，如果執行緒A鎖住了記錄1並等待記錄2，而執行緒B鎖住了記錄2並等待記錄1，這樣兩個執行緒就發生了死鎖現象。

計算機系統中,如果系統的資源分配策略不當，更常見的可能是程式設計師寫的程式有錯誤等，則會導致程式因競爭資源不當而產生死鎖的現象。

產生死鎖的原因主要是：

（1）因為系統資源不足。

（2）程式執行推進的順序不合適。

（3）資源分配不當等。

如果系統資源充足，程式的資源請求都能夠得到滿足，死鎖出現的可能性就很低，否則就會因爭奪有限的資源而陷入死鎖。其次，程式執行推進順序與速度不同，也可能產生死鎖

產生死鎖的四個必要條件：

（1）互斥條件：一個資源每次只能被一個程式使用。

（2）請求與保持條件：一個程式因請求資源而阻塞時，對已獲得的資源保持不放。

（3）不剝奪條件:程式已獲得的資源，在末使用完之前，不能強行剝奪。

（4）迴圈等待條件:若干程式之間形成一種頭尾相接的迴圈等待資源關係。這四個條件是死鎖的必要條件，只要系統發生死鎖，這些條件必然成立，而只要上述條件之一不滿足，就不會發生死鎖。

死鎖的解決：預防和解除

1。死鎖的預防：1）一次封鎖法：就是要求每個事務必須一次將所有要使用的資料全部加鎖，否則就不能繼續執行。但是出現這樣的問題：因為一次給全部資料加鎖，從而降低系統的併發度，同時資料庫中資料是不斷變化的，原來不要求封鎖的資料在執行過程中可能會變成封鎖物件。2）順序封鎖法，預先對資料物件規定一個封鎖順序，所有事務都按這個順序實行封鎖，問題：資料庫中資料物件多，且不斷的變化，維護困難，成本高；事務的封鎖請求可以隨著事務的執行而動態的決定。

2。檢測死鎖

企業管理器－>可以在[管理]->[當前活動] 裡看到阻塞資訊(有時SQL Server企業管理器會因為鎖太多而沒有響應).

設定跟蹤1204:

USE MASTER
DBCC TRACEON (1204,-1)

顯示當前啟用的所有跟蹤標記的狀態:

DBCC TRACESTATUS(-1)

取消跟蹤1204:

DBCC TRACEOFF (1204,-1)

在設定跟蹤1204後,會在資料庫的日誌檔案裡顯示SQL Server資料庫死鎖時一些資訊。但那些資訊很難看懂,需要對照SQL Server聯機叢書仔細來看。根據PAG鎖要找到相關資料庫表的方法:

DBCC TRACEON (3604)
DBCC PAGE (db_id,file_id,page_no)
DBCC TRACEOFF (3604)

可以使用以下儲存過程來檢測，就可以查出引起死鎖的程式和SQL語句。

寫了一個系統儲存過程sp_who_lock.sql。程式碼如下：

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_who_lock]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_who_lock]
GO
/**//********************************************************
// 說明 : 檢視資料庫裡阻塞和死鎖情況
********************************************************/
use master
go
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry     int,
@intRowcount             int,
@intCountProperties         int,
@intCounter             int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses
where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到臨時表的記錄數
select     @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if    @intCountProperties=0
select '現在沒有阻塞和死鎖資訊' as message
-- 迴圈開始
while @intCounter <= @intCountProperties
begin
-- 取第一條記錄
select     @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起資料庫死鎖的是: '+ CAST(@bl AS VARCHAR(10))
+ '程式號,其執行的SQL語法如下'
else
select '程式號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '程式號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前程式執行的SQL語法如下'
DBCC INPUTBUFFER (@bl )
end
-- 迴圈指標下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end

需要的時候直接呼叫:

sp_who_lock

就可以查出引起死鎖的程式和SQL語句.

我們還可以設定鎖的超時時間(單位是毫秒), 來縮短死鎖可能影響的時間範圍:

例如:

use master
seelct @@lock_timeout
set lock_timeout 900000
-- 15分鐘
seelct @@lock_timeout

*********************************************************************************************************

常看到死鎖的問題,一般都是KILL程式,但如果不查出引起死鎖的原因,死鎖會時常發生
可以透過查詢引起死鎖的的操作,就可以方便的解決死鎖,現將日常解決問題的方法總結,也許對大家有幫助

1\死鎖發生時,透過如下語法,查詢出引起死鎖的操作

use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select '引起資料庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '程式號,其執行的SQL語法如下'
else
select '程式號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '程式號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前程式執行的SQL語法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur

exec sp_who2
2\查詢程式/資料庫,此t_sql語法在什麼地方使用
3\分析找到的,並解決問題

EG：

/*
-------------------------------------------------------
引起資料庫死鎖的是: 71程式號,其執行的SQL語法如下

EventType Parameters EventInfo
-------------- ---------- ------------------------------------------------
Language Event 0
select * from test
insert test values(1,2)

（所影響的行數為 1 行）

DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

------------------------------------------------------------------------------
程式號SPID：64被程式號SPID：71阻塞,其當前程式執行的SQL語法如下

EventType Parameters EventInfo
-------------- ---------- ------------------------------------------------
Language Event 0
select * from test
insert test values(1,2)

（所影響的行數為 1 行）

DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

------------------------------------------------------------------------------
程式號SPID：65被程式號SPID：64阻塞,其當前程式執行的SQL語法如下

EventType Parameters EventInfo
-------------- ---------- --------------------------------------------------------------------------------------------------
Language Event 0 begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit

（所影響的行數為 1 行）

DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

------------------------------------------------------------------------------
程式號SPID：73被程式號SPID：64阻塞,其當前程式執行的SQL語法如下

EventType Parameters EventInfo
-------------- ---------- --------------------------------------------------------------------------------------------------
Language Event 0 begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit

（所影響的行數為 1 行）

DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。
*/

***************************************************************************************************

解決方法：

　　讓使用者（即先讀後寫型別的操作),在select 時就是用Update lock

　　語法如下：

select * from table1 with(updlock) where ....

[zt] sql server 死鎖總結

相關文章