SQL Server數(shù)據(jù)庫(kù)查詢操作時(shí)的死鎖總結(jié)
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
1. 死鎖原理 根據(jù)操作系統(tǒng)中的定義：死鎖是指在一組進(jìn)程中的各個(gè)進(jìn)程均占有不會(huì)釋放的資源，但因互相申請(qǐng)被其他進(jìn)程所站用不會(huì)釋放的資源而處于的一種永久等待狀態(tài)。 死鎖的四個(gè)必要條件： 對(duì)應(yīng)到SQL Server中，當(dāng)在兩個(gè)或多個(gè)任務(wù)中，如果每個(gè)任務(wù)鎖定了其他任務(wù)試圖鎖定的資源，此時(shí)會(huì)造成這些任務(wù)永久阻塞，從而出現(xiàn)死鎖；這些資源可能是：?jiǎn)涡?/span>(RID，堆中的單行)、索引中的鍵(KEY，行鎖)、頁(yè)(PAG，8KB)、區(qū)結(jié)構(gòu)(EXT，連續(xù)的8頁(yè))、堆或B樹(shù)(HOBT) 、表(TAB，包括數(shù)據(jù)和索引)、文件(File，數(shù)據(jù)庫(kù)文件)、應(yīng)用程序?qū)Ｓ觅Y源(APP)、元數(shù)據(jù)(METADATA)、分配單元(Allocation_Unit)、整個(gè)數(shù)據(jù)庫(kù)(DB)。一個(gè)死鎖示例如下圖所示：
說(shuō)明：T1、T2表示兩個(gè)任務(wù)；R1和R2表示兩個(gè)資源；由資源指向任務(wù)的箭頭(如R1->T1，R2->T2)表示該資源被改任務(wù)所持有；由任務(wù)指向資源的箭頭(如T1->S2，T2->S1)表示該任務(wù)正在請(qǐng)求對(duì)應(yīng)目標(biāo)資源；
2. 死鎖排查 (1). 使用SQL Server的系統(tǒng)存儲(chǔ)過(guò)程sp_who和sp_lock，可以查看當(dāng)前數(shù)據(jù)庫(kù)中的鎖情況；進(jìn)而根據(jù)objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪個(gè)資源被鎖，用dbcc ld(@blk)，可以查看最后一條發(fā)生給SQL Server的Sql語(yǔ)句； create Table #Who(spid int, ecid int, status nvarchar(50), loginname nvarchar(50), hostname nvarchar(50), blk int, dbname nvarchar(50), cmd nvarchar(50), request_ID int );
dpid int, objid int, indld int, [Type] nvarchar(20), Resource nvarchar(50), Mode nvarchar(10), Status nvarchar(10) );
exec sp_who active --看哪個(gè)引起的阻塞，blk insert INTO #Lock exec sp_lock --看鎖住了那個(gè)資源id，objid SET @DBName='NameOfDataBase'
select #Lock.* from #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName;
DECLARE crsr Cursor FOR select blk from #Who where dbname=@DBName AND blk<>0; DECLARE @blk int; open crsr; FETCH NEXT from crsr INTO @blk; WHILE (@@FETCH_STATUS = 0)BEGIN; dbcc inputbuffer(@blk); FETCH NEXT from crsr INTO @blk; END; close crsr; DEALLOCATE crsr;
select #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName from #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName where objid<>0;
drop Table #Lock;
3. 避免死鎖 上面1中列出了死鎖的四個(gè)必要條件，我們只要想辦法破其中的任意一個(gè)或多個(gè)條件，就可以避免死鎖發(fā)生，一般有以下幾種方法(from Sql Server 2005聯(lián)機(jī)叢書(shū))：
4. 死鎖處理方法： (1). 根據(jù)2中提供的sql，查看那個(gè)spid處于wait狀態(tài)，然后用kill spid來(lái)干掉(即破壞死鎖的第四個(gè)必要條件:循環(huán)等待)；當(dāng)然這只是一種臨時(shí)解決方案，我們總不能在遇到死鎖就在用戶的生產(chǎn)環(huán)境上排查死鎖、Kill sp，我們應(yīng)該考慮如何去避免死鎖。 (2). 使用SET LOCK_TIMEOUT timeout_period(單位為毫秒)來(lái)設(shè)定鎖請(qǐng)求超時(shí)。默認(rèn)情況下，數(shù)據(jù)庫(kù)沒(méi)有超時(shí)期限(timeout_period值為-1，可以用select @@LOCK_TIMEOUT來(lái)查看該值，即無(wú)限期等待)。當(dāng)請(qǐng)求鎖超過(guò)timeout_period時(shí)，將返回錯(cuò)誤。timeout_period值為0時(shí)表示根本不等待，一遇到鎖就返回消息。設(shè)置鎖請(qǐng)求超時(shí)，破環(huán)了死鎖的第二個(gè)必要條件(請(qǐng)求與保持條件)。 服務(wù)器: 消息 1222，級(jí)別 16，狀態(tài) 50，行 1 已超過(guò)了鎖請(qǐng)求超時(shí)時(shí)段。 (3). SQL Server內(nèi)部有一個(gè)鎖監(jiān)視器線程執(zhí)行死鎖檢查，鎖監(jiān)視器對(duì)特定線程啟動(dòng)死鎖搜索時(shí)，會(huì)標(biāo)識(shí)線程正在等待的資源；然后查找特定資源的所有者，并遞歸地繼續(xù)執(zhí)行對(duì)那些線程的死鎖搜索，直到找到一個(gè)構(gòu)成死鎖條件的循環(huán)。檢測(cè)到死鎖后，數(shù)據(jù)庫(kù)引擎 選擇運(yùn)行回滾開(kāi)銷最小的事務(wù)的會(huì)話作為死鎖犧牲品，返回1205 錯(cuò)誤，回滾死鎖犧牲品的事務(wù)并釋放該事務(wù)持有的所有鎖，使其他線程的事務(wù)可以請(qǐng)求資源并繼續(xù)運(yùn)行
5. 兩個(gè)死鎖示例及解決方法 5.1 SQL死鎖 (1). 測(cè)試用的基礎(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). 開(kāi)兩個(gè)查詢窗口，分別執(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分鐘，以方便查看鎖的情況。 (3). 查看鎖情況 在執(zhí)行上面的WaitFor語(yǔ)句期間，執(zhí)行第二節(jié)中提供的語(yǔ)句來(lái)查看鎖信息：
Query1中，持有Lock1中第一行(表中只有一行數(shù)據(jù))的行排他鎖(RID:X)，并持有該行所在頁(yè)的意向更新鎖(PAG:IX)、該表的意向更新鎖(TAB:IX)；Query2中，持有Lock2中第一行(表中只有一行數(shù)據(jù))的行排他鎖(RID:X)，并持有該行所在頁(yè)的意向更新鎖(PAG:IX)、該表的意向更新鎖(TAB:IX)； 執(zhí)行完Waitfor，Query1查詢Lock2，請(qǐng)求在資源上加S鎖，但該行已經(jīng)被Query2加上了X鎖；Query2查詢Lock1，請(qǐng)求在資源上加S鎖，但該行已經(jīng)被Query1加上了X鎖；于是兩個(gè)查詢持有資源并互不相讓，構(gòu)成死鎖。 (4). 解決辦法 a). SQL Server自動(dòng)選擇一條SQL作死鎖犧牲品：運(yùn)行完上面的兩個(gè)查詢后，我們會(huì)發(fā)現(xiàn)有一條SQL能正常執(zhí)行完畢，而另一個(gè)SQL則報(bào)如下錯(cuò)誤： 服務(wù)器: 消息 1205，級(jí)別 13，狀態(tài) 50，行 1 事務(wù)（進(jìn)程 ID xx）與另一個(gè)進(jìn)程已被死鎖在 lock 資源上，且該事務(wù)已被選作死鎖犧牲品。請(qǐng)重新運(yùn)行該事務(wù)。 這就是上面第四節(jié)中介紹的鎖監(jiān)視器干活了。 b). 按同一順序訪問(wèn)對(duì)象：顛倒任意一條SQL中的update與select語(yǔ)句的順序。例如修改第二條SQL成如下： --Query2 Begin Tran select * from Lock1--在Lock1上申請(qǐng)S鎖 WaitFor Delay '00:01:00'; update Lock2 Set C1=C1+1;--Lock2:RID:X Rollback Tran; 當(dāng)然這樣修改也是有代價(jià)的，這會(huì)導(dǎo)致第一條SQL執(zhí)行完畢之前，第二條SQL一直處于阻塞狀態(tài)。單獨(dú)執(zhí)行Query1或Query2需要約1分鐘，但如果開(kāi)始執(zhí)行Query1時(shí)，馬上同時(shí)執(zhí)行Query2，則Query2需要2分鐘才能執(zhí)行完；這種按順序請(qǐng)求資源從一定程度上降低了并發(fā)性。 c). select語(yǔ)句加With(NoLock)提示：默認(rèn)情況下select語(yǔ)句會(huì)對(duì)查詢到的資源加S鎖(共享鎖)，S鎖與X鎖(排他鎖)不兼容；但加上With(NoLock)后，select不對(duì)查詢到的資源加鎖(或者加Sch-S鎖，Sch-S鎖可以與任何鎖兼容)；從而可以是這兩條SQL可以并發(fā)地訪問(wèn)同一資源。當(dāng)然，此方法適合解決讀與寫(xiě)并發(fā)死鎖的情況，但加With(NoLock)可能會(huì)導(dǎo)致臟讀。 select * from Lock2 WITH(NOLock) select * from Lock1 WITH(NOLock) d). 使用較低的隔離級(jí)別。SQL Server 2000支持四種事務(wù)處理隔離級(jí)別(TIL)，分別為：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE；SQL Server 2005中增加了SNAPSHOT TIL。默認(rèn)情況下，SQL Server使用READ COMMITTED TIL，我們可以在上面的兩條SQL前都加上一句SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED，來(lái)降低TIL以避免死鎖；事實(shí)上，運(yùn)行在READ UNCOMMITTED TIL的事務(wù)，其中的select語(yǔ)句不對(duì)結(jié)果資源加鎖或加Sch-S鎖，而不會(huì)加S鎖；但還有一點(diǎn)需要注意的是：READ UNCOMMITTED TIL允許臟讀，雖然加上了降低TIL的語(yǔ)句后，上面兩條SQL在執(zhí)行過(guò)程中不會(huì)報(bào)錯(cuò)，但執(zhí)行結(jié)果是一個(gè)返回1，一個(gè)返回2，即讀到了臟數(shù)據(jù)，也許這并不是我們所期望的。 e). 在SQL前加SET LOCK_TIMEOUT timeout_period，當(dāng)請(qǐng)求鎖超過(guò)設(shè)定的timeout_period時(shí)間后，就會(huì)終止當(dāng)前SQL的執(zhí)行，犧牲自己，成全別人。 f). 使用基于行版本控制的隔離級(jí)別(SQL Server 2005支持)：開(kāi)啟下面的選項(xiàng)后，select不會(huì)對(duì)請(qǐng)求的資源加S鎖，不加鎖或者加Sch-S鎖，從而將讀與寫(xiě)操作之間發(fā)生的死鎖幾率降至最低；而且不會(huì)發(fā)生臟讀。 SET ALLOW_SNAPSHOT_ISOLATION ON SET READ_COMMITTED_SNAPSHOT ON g). 使用綁定連接(使用方法見(jiàn)下一個(gè)示例。)
5.2 程序死鎖(SQL阻塞) 看一個(gè)例子：一個(gè)典型的數(shù)據(jù)庫(kù)操作事務(wù)死鎖分析，按照我自己的理解，我覺(jué)得這應(yīng)該算是C#程序中出現(xiàn)死鎖，而不是數(shù)據(jù)庫(kù)中的死鎖；下面的代碼模擬了該文中對(duì)數(shù)據(jù)庫(kù)的操作過(guò)程： //略去的無(wú)關(guān)的code SqlConnection conn = new SqlConnection(connectionString); conn.Open(); SqlTransaction tran = conn.BeginTransaction(); string sql1 = "update Lock1 SET C1=C1+1"; string sql2 = "select * from Lock1"; executeNonQuery(tran, sql1); //使用事務(wù):事務(wù)中Lock了Table executeNonQuery(null, sql2); //新開(kāi)一個(gè)connection來(lái)讀取Table
{ SqlCommand cmd = new SqlCommand(sql); if (tran != null) { cmd.Connection = tran.Connection; cmd.Transaction = tran; cmd.executeNonQuery(); } else { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); cmd.Connection = conn; cmd.executeNonQuery(); } } } 執(zhí)行到executeNonQuery(null, sql2)時(shí)拋出SQL執(zhí)行超時(shí)的異常，下圖從數(shù)據(jù)庫(kù)的角度來(lái)看該問(wèn)題： 代碼從上往下執(zhí)行，會(huì)話1持有了表Lock1的X鎖，且事務(wù)沒(méi)有結(jié)束，回話1就一直持有X鎖不釋放；而會(huì)話2執(zhí)行select操作，請(qǐng)求在表Lock1上加S鎖，但S鎖與X鎖是不兼容的，所以回話2的被阻塞等待，不在等待中，就在等待中獲得資源，就在等待中超時(shí)。。。從中我們可以看到，里面并沒(méi)有出現(xiàn)死鎖，而只是select操作被阻塞了。也正因?yàn)椴皇菙?shù)據(jù)庫(kù)死鎖，所以SQL Server的鎖監(jiān)視器無(wú)法檢測(cè)到死鎖。 我們?cè)購(gòu)?/span>C#程序的角度來(lái)看該問(wèn)題：
C#程序持有了表Lock1上的X鎖，同時(shí)開(kāi)了另一個(gè)SqlConnection還想在該表上請(qǐng)求一把S鎖，圖中已經(jīng)構(gòu)成了環(huán)路；太貪心了，結(jié)果自己把自己給鎖死了。。。 雖然這不是一個(gè)數(shù)據(jù)庫(kù)死鎖，但卻是因?yàn)閿?shù)據(jù)庫(kù)資源而導(dǎo)致的死鎖，上例中提到的解決死鎖的方法在這里也基本適用，主要是避免讀操作被阻塞，解決方法如下： a). 把select放在update語(yǔ)句前：select不在事務(wù)中，且執(zhí)行完畢會(huì)釋放S鎖； tran = connection.BeginTransaction(); string sql1 = "update Lock1 SET C1=C1+1"; executeNonQuery(tran, sql1); //使用事務(wù):事務(wù)中Lock了測(cè)試表Lock1 string sql2 = @"DECLARE @Token varchar(255); exec sp_getbindtoken @Token out;select @Token;"; string token = executeScalar(tran, sql2).ToString(); string sql3 = "exec sp_bindsession @Token;update Lock1 SET C1=C1+1;exec sp_bindsession null;"; SqlParameter parameter = new SqlParameter("@Token", SqlDbType.VarChar); parameter.Value = token; executeNonQuery(null, sql3, parameter); //新開(kāi)一個(gè)connection來(lái)操作測(cè)試表Lock1 tran.Commit(); 附：鎖兼容性(from SQL Server 2005 聯(lián)機(jī)叢書(shū)) 鎖兼容性控制多個(gè)事務(wù)能否同時(shí)獲取同一資源上的鎖。如果資源已被另一事務(wù)鎖定，則僅當(dāng)請(qǐng)求鎖的模式與現(xiàn)有鎖的模式相兼容時(shí)，才會(huì)授予新的鎖請(qǐng)求。如果請(qǐng)求鎖的模式與現(xiàn)有鎖的模式不兼容，則請(qǐng)求新鎖的事務(wù)將等待釋放現(xiàn)有鎖或等待鎖超時(shí)間隔過(guò)期。
該文章在 2023/11/28 10:56:27 編輯過(guò) |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
