點擊下載
本文文檔

當前位置：首頁 - 科技 - 知識百科 - 正文

SQLServer數(shù)據(jù)庫死鎖原因與解決辦法

來源：懂視網(wǎng) 責編：小采時間：2020-11-09 09:59:25

SQLServer數(shù)據(jù)庫死鎖原因與解決辦法

SQLServer數(shù)據(jù)庫死鎖原因與解決辦法:SQL Server數(shù)據(jù)庫死鎖，通俗的講就是兩個或多個trans，同時請求對方正在請求的某個實際應用對象，而導致雙方互相等待。簡單的例子如下： sql server死鎖表現(xiàn)一: 一個用戶A 訪問表A(鎖住了表A),然后又訪問表B 另一個用戶B 訪問表B(鎖住了表B),然后企

推薦度：

點擊下載本文 文檔為doc格式

導讀SQLServer數(shù)據(jù)庫死鎖原因與解決辦法:SQL Server數(shù)據(jù)庫死鎖，通俗的講就是兩個或多個trans，同時請求對方正在請求的某個實際應用對象，而導致雙方互相等待。簡單的例子如下： sql server死鎖表現(xiàn)一: 一個用戶A 訪問表A(鎖住了表A),然后又訪問表B 另一個用戶B 訪問表B(鎖住了表B),然后企

SQL Server數(shù)據(jù)庫死鎖，通俗的講就是兩個或多個trans，同時請求對方正在請求的某個實際應用對象，而導致雙方互相等待。簡單的例子如下：

sql server死鎖表現(xiàn)一:

　　一個用戶A 訪問表A(鎖住了表A),然后又訪問表B
　　另一個用戶B 訪問表B(鎖住了表B),然后企圖訪問表A
　　這時用戶A由于用戶B已經(jīng)鎖住表B，它必須等待用戶B釋放表B,才能繼續(xù)，好了他老人家就只好老老實實在這等了
　　同樣用戶B要等用戶A釋放表A才能繼續(xù)這就死鎖了
　　sql server死鎖解決方法：
　　這種死鎖是由于你的程序的BUG產(chǎn)生的，除了調(diào)整你的程序的邏輯別無他法
　　仔細分析你程序的邏輯，
　　 1：盡量避免同時鎖定兩個資源
　　 2: 必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相同的順序來鎖定資源.
　　
sql server死鎖表現(xiàn)二:

　　用戶A讀一條紀錄，然后修改該條紀錄
　　這是用戶B修改該條紀錄
　　這里用戶A的事務(wù)里鎖的性質(zhì)由共享鎖企圖上升到獨占鎖(for update),而用戶B里的獨占鎖由于A有共享鎖存在所

以必須等A釋
　　放掉共享鎖，而A由于B的獨占鎖而無法上升的獨占鎖也就不可能釋放共享鎖，于是出現(xiàn)了死鎖。
　　這種死鎖比較隱蔽，但其實在稍大點的項目中經(jīng)常發(fā)生。
　　sql server死鎖解決方法:
　　讓用戶A的事務(wù)（即先讀后寫類型的操作),在select 時就是用Update lock
語法如下：
　　 select * from table1 with(updlock) where ....

sqlserver死鎖檢查工具

代碼如下

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

-- 找到臨時表的記錄數(shù)
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who

IF @@ERROR<>0 RETURN @@ERROR

if @intCountProperties=0
select '現(xiàn)在沒有阻塞和死鎖信息' as message

-- 循環(huán)開始
while @intCounter <= @intCountProperties
begin
-- 取第一條記錄
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起數(shù)據(jù)庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,其執(zhí)行的SQL語法如下'
else
select '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '進程號SPID：'+ CAST(@bl AS VARCHAR

(10)) +'阻塞,其當前進程執(zhí)行的SQL語法如下'
DBCC INPUTBUFFER (@bl )
end

-- 循環(huán)指針下移
set @intCounter = @intCounter + 1
end

drop table #tmp_lock_who

return 0
end

死鎖處理方法：

(1). 根據(jù)2中提供的sql，查看那個spid處于wait狀態(tài)，然后用kill spid來干掉(即破壞死鎖的第四個必要條件:循環(huán)

等待)；當然這只是一種臨時解決方案，我們總不能在遇到死鎖就在用戶的生產(chǎn)環(huán)境上排查死鎖、Kill sp，我們應該

考慮如何去避免死鎖。

(2). 使用SET LOCK_TIMEOUT timeout_period(單位為毫秒)來設(shè)定鎖請求超時。默認情況下，數(shù)據(jù)庫沒有超時期限

代碼如下
(timeout_period值為-1，可以用SELECT @@LOCK_TIMEOUT來查看該值，即無限期等待)。當請求鎖超過timeout_period

時，將返回錯誤。timeout_period值為0時表示根本不等待，一遇到鎖就返回消息。設(shè)置鎖請求超時，破環(huán)了死鎖的第

二個必要條件(請求與保持條件)。

服務(wù)器: 消息 1222，級別 16，狀態(tài) 50，行 1
已超過了鎖請求超時時段。

(3). SQL Server內(nèi)部有一個鎖監(jiān)視器線程執(zhí)行死鎖檢查，鎖監(jiān)視器對特定線程啟動死鎖搜索時，會標識線程正在等待

的資源；然后查找特定資源的所有者，并遞歸地繼續(xù)執(zhí)行對那些線程的死鎖搜索，直到找到一個構(gòu)成死鎖條件的循環(huán)

。檢測到死鎖后，數(shù)據(jù)庫引擎選擇運行回滾開銷最小的事務(wù)的會話作為死鎖犧牲品，返回1205 錯誤，回滾死鎖犧牲

品的事務(wù)并釋放該事務(wù)持有的所有鎖，使其他線程的事務(wù)可以請求資源并繼續(xù)運行。

死鎖示例及解決方法
5.1 SQL死鎖
(1). 測試用的基礎(chǔ)數(shù)據(jù)：

代碼如下
CREATE TABLE Lock1(C1 int default(0)); CREATE TABLE Lock2(C1 int default(0)); INSERT INTO Lock1 VALUES(1); INSERT INTO Lock2 VALUES(1);

(2). 開兩個查詢窗口，分別執(zhí)行下面兩段sql

代碼如下

--Query 1
Begin Tran
Update Lock1 Set C1=C1+1;
WaitFor Delay '00:01:00';
SELECT * FROM Lock2
Rollback Tran;

--Query 2
Begin Tran
Update Lock2 Set C1=C1+1;
WaitFor Delay '00:01:00';
SELECT * FROM Lock1
Rollback Tran;

上面的SQL中有一句WaitFor Delay '00:01:00'，用于等待1分鐘，以方便查看鎖的情況。

解決辦法
a). SQL Server自動選擇一條SQL作死鎖犧牲品：運行完上面的兩個查詢后，我們會發(fā)現(xiàn)有一條SQL能正常執(zhí)行完畢，

而另一個SQL則報如下錯誤：

服務(wù)器: 消息 1205，級別 13，狀態(tài) 50，行 1
事務(wù)（進程 ID xx）與另一個進程已被死鎖在 lock 資源上，且該事務(wù)已被選作死鎖犧牲品。請重新運行該事務(wù)。
這就是上面第四節(jié)中介紹的鎖監(jiān)視器干活了。

b). 按同一順序訪問對象：顛倒任意一條SQL中的Update與SELECT語句的順序。例如修改第二條SQL成如下：

代碼如下
--Query2 Begin Tran SELECT * FROM Lock1--在Lock1上申請S鎖 WaitFor Delay '00:01:00'; Update Lock2 Set C1=C1+1;--Lock2:RID:X Rollback Tran;

當然這樣修改也是有代價的，這會導致第一條SQL執(zhí)行完畢之前，第二條SQL一直處于阻塞狀態(tài)。單獨執(zhí)行Query1或

Query2需要約1分鐘，但如果開始執(zhí)行Query1時，馬上同時執(zhí)行Query2，則Query2需要2分鐘才能執(zhí)行完；這種按順序

請求資源從一定程度上降低了并發(fā)性。

c). SELECT語句加With(NoLock)提示：默認情況下SELECT語句會對查詢到的資源加S鎖(共享鎖)，S鎖與X鎖(排他鎖)不

兼容；但加上With(NoLock)后，SELECT不對查詢到的資源加鎖(或者加Sch-S鎖，Sch-S鎖可以與任何鎖兼容)；從而可

以是這兩條SQL可以并發(fā)地訪問同一資源。當然，此方法適合解決讀與寫并發(fā)死鎖的情況，但加With(NoLock)可能會導

致臟讀。