SQL Server死鎖總結(jié)

1、sql server死鎖總結(jié)1. 死鎖原理 按照操作系統(tǒng)中的定義：死鎖是指在一組進(jìn)程中的各個(gè)進(jìn)程均占有不會(huì)釋放的資源，但因相互申請(qǐng)被其他進(jìn)程所站用不會(huì)釋放的資源而處于的一種永遠(yuǎn)等待狀態(tài)。 死鎖的四個(gè)須要條件： 互斥條件(mutual clusion)：資源不能被分享，只能由一個(gè)進(jìn)程用法。 哀求與保持條件(hold and wait)：已經(jīng)得到資源的進(jìn)程可以再次申請(qǐng)新的資源。 非剝奪條件(no pre-emption)：已經(jīng)分配的資源不能從相應(yīng)的進(jìn)程中被強(qiáng)制地剝奪。 循環(huán)等待條件(circular wait)：系統(tǒng)中若干進(jìn)程組成環(huán)路，該環(huán)路中每個(gè)進(jìn)程都在等待相鄰進(jìn)程正占用的資源。 對(duì)應(yīng)到sql

2、server中，當(dāng)在兩個(gè)或多個(gè)任務(wù)中，假如每個(gè)任務(wù)鎖定了其他任務(wù)試圖鎖定的資源，此時(shí)會(huì)造成這些任務(wù)永遠(yuǎn)堵塞，從而浮現(xiàn)死鎖；這些資源可能是：?jiǎn)涡?rid，堆中的單行)、索引中的鍵(key，行鎖)、頁(yè)(pag，8kb)、區(qū)結(jié)構(gòu)(ext，延續(xù)的8頁(yè))、堆或b樹(hobt) 、表(tab，包括數(shù)據(jù)和索引)、文件(file，數(shù)據(jù)庫(kù)文件)、應(yīng)用程序?qū)Ｓ觅Y源(app)、元數(shù)據(jù)(metadata)、分配單元(alloion_unit)、囫圇數(shù)據(jù)庫(kù)(db)。一個(gè)死鎖示例如下圖所示： 解釋：t1、t2表示兩個(gè)任務(wù)；r1和r2表示兩個(gè)資源；由資源指向任務(wù)的箭頭(如r1- t1，r2- t2)表示該資源被改任務(wù)所持有

3、；由任務(wù)指向資源的箭頭(如t1- s2，t2- s1)表示該任務(wù)正在哀求對(duì)應(yīng)目標(biāo)資源； 其滿足上面死鎖的四個(gè)須要條件： (1).互斥：資源s1和s2不能被分享，同一時(shí)光只能由一個(gè)任務(wù)用法； (2).哀求與保持條件：t1持有s1的同時(shí)，哀求s2；t2持有s2的同時(shí)哀求s1； (3).非剝奪條件：t1無(wú)法從t2上剝奪s2，t2也無(wú)法從t1上剝奪s1； (4).循環(huán)等待條件：上圖中的箭頭構(gòu)成環(huán)路，存在循環(huán)等待。 2. 死鎖排查 (1). 用法sql server的系統(tǒng)存儲(chǔ)過(guò)程sp_和sp_lock，可以查看當(dāng)前數(shù)據(jù)庫(kù)中的鎖狀況；進(jìn)而按照objectid(objid)(sql server 2005)

4、/ object_name(objid)(sql server 2000)可以查看哪個(gè)資源被鎖，用dbcc ld(blk)，可以查看最后一條發(fā)生給sql server的sql語(yǔ)句； create table who(sp int, ecid int, us nvarchar(50), loginname nvarchar(50), hostname nvarchar(50), blk int, dbname nvarchar(50), cmd nvarchar(50), request_id int); create table lock(spid int, dpid int, objid i

5、nt, indld int, type nvarchar(20), resource nvarchar(50), mode nvarchar(10), status nvarchar(10) ); insert into who exec sp_who active -看哪個(gè)引起的堵塞，blk insert into lock exec sp_lock -看鎖住了那個(gè)資源id，objid declare dbname nvarchar(20); set dbname='nameofdatabase' select who.* from who where dbname=dbna

6、me select lock.* from lock join who on who.spid=lock.spid and dbname=dbname; -最后發(fā)送到sql server的語(yǔ)句 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 fro

7、m 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 who; drop table lock; (2). 用法 sql server pror 分析死鎖: 將 deadlock graph 大事類添加到跟蹤。此大事類用法死鎖

8、涉及到的進(jìn)程和對(duì)象的 xml 數(shù)據(jù)填充跟蹤中的 textdata 數(shù)據(jù)列。sql server 大事探查器 可以將 xml 文檔提取到死鎖 xml (.xdl) 文件中，以后可在 sql server management studio 中查看該文件。 3. 避開死鎖 上面1中列出了死鎖的四個(gè)須要條件，我們只要想方法破其中的隨意一個(gè)或多個(gè)條件，就可以避開死鎖發(fā)生，普通有以下幾種辦法(from sql server 2005聯(lián)機(jī)叢書)： (1).按同一挨次拜訪對(duì)象。(注：避開浮現(xiàn)循環(huán)) (2).避開事務(wù)中的用戶交互。(注：削減持有資源的時(shí)光，較少鎖競(jìng)爭(zhēng)) (3).保持事務(wù)簡(jiǎn)短并處于一個(gè)批處理中。

9、(注：同(2)，削減持有資源的時(shí)光) (4).用法較低的隔離級(jí)別。(注：用法較低的隔離級(jí)別（例如已提交讀）比用法較高的隔離級(jí)別（例如可序列化）持有分享鎖的時(shí)光更短，削減鎖競(jìng)爭(zhēng)) (5).用法基于行版本控制的隔離級(jí)別：2005中支持快照事務(wù)隔離和指定read_committed隔離級(jí)別的事務(wù)用法行版本控制，可以將讀與寫操作之間發(fā)生的死鎖幾率降至最低： set allow_snapshot_isolation on -事務(wù)可以指定 snapshot 事務(wù)隔離級(jí)別; set read_committed_snapshot on -指定 read_committed 隔離級(jí)別的事務(wù)將用法行版本控制而不

10、是鎖定。默認(rèn)狀況下(沒有開啟此選項(xiàng)，沒有加with nolock提醒)，select語(yǔ)句會(huì)對(duì)哀求的資源加s鎖(分享鎖)；而開啟了此選項(xiàng)后，select不會(huì)對(duì)哀求的資源加s鎖。 注重：設(shè)置 read_committed_snapshot 選項(xiàng)時(shí)，數(shù)據(jù)庫(kù)中只允許存在執(zhí)行 alter database 的銜接。在 alter database 完成之前，數(shù)據(jù)庫(kù)中決不能有其他打開的銜接。數(shù)據(jù)庫(kù)不必一定要處于單用戶模式中。 (6).用法綁定銜接。(注：綁定會(huì)話有利于在同一臺(tái)服務(wù)器上的多個(gè)會(huì)話之間協(xié)調(diào)操作。綁定會(huì)話允許一個(gè)或多個(gè)會(huì)話分享相同的事務(wù)和鎖(但每個(gè)回話保留其自己的事務(wù)隔離級(jí)別)，并可以用法同一數(shù)

11、據(jù)，而不會(huì)有鎖矛盾?？梢詮耐粋€(gè)應(yīng)用程序內(nèi)的多個(gè)會(huì)話中創(chuàng)建綁定會(huì)話，也可以從包含不同會(huì)話的多個(gè)應(yīng)用程序中創(chuàng)建綁定會(huì)話。在一個(gè)會(huì)話中開啟事務(wù)(begin an)后，調(diào)用exec sp_getbindtoken token out;來(lái)取得token，然后傳入另一個(gè)會(huì)話并執(zhí)行exec sp_bindsession token來(lái)舉行綁定(最后的示例中演示了綁定銜接)。 4. 死鎖處理辦法： (1). 按照2中提供的sql，查看那個(gè)spid處于wait狀態(tài)，然后用 spid來(lái)干掉(即破壞死鎖的第四個(gè)須要條件:循環(huán)等待)；固然這只是一種暫時(shí)解決計(jì)劃，我們總不能在碰到死鎖就在用戶的生產(chǎn)環(huán)境上排查死鎖、kil

12、l sp，我們應(yīng)當(dāng)考慮如何去避開死鎖。 (2). 用法set lock_timeout out_period(單位為毫秒)來(lái)設(shè)定鎖哀求超時(shí)。默認(rèn)狀況下，數(shù)據(jù)庫(kù)沒有超時(shí)期限(timeout_period 為-1，可以用select lock_timeout來(lái)查看該 ，即無(wú)限期等待)。當(dāng)哀求鎖超過(guò)timeout_period時(shí)，將返回錯(cuò)誤。timeout_period 為0時(shí)表示根本不等待，一碰到鎖就返回消息。設(shè)置鎖哀求超時(shí)，破環(huán)了死鎖的其次個(gè)須要條件(哀求與保持條件)。 服務(wù)器: 消息 1222，級(jí)別 16，狀態(tài) 50，行 1 已超過(guò)了鎖哀求超時(shí)時(shí)段。 (3). sql server內(nèi)部有一個(gè)鎖

13、監(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)行回滾開銷最小的事務(wù)的會(huì)話作為死鎖犧牲品，返回1205 錯(cuò)誤，回滾死鎖犧牲品的事務(wù)并釋放該事務(wù)持有的全部鎖，使其他線程的事務(wù)可以哀求資源并繼續(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); ins

14、ert into lock1 values(1); insert into lock2 values(1); (2). 開兩個(gè)查詢窗口，分離執(zhí)行下面兩段sql -query 1 begin tran up lock1 set c1=c1 waitfor delay '00:01:00' select * from lock2 rollback tran; -query 2 begin tran update lock2 set c1=c1 waitfor delay '00:01:00' select * from lock1 rollback tran; 上面

15、的sql中有一句waitfor delay '00:01:00'，用于等待1分鐘，以便利查看鎖的狀況。 (3). 查看鎖狀況 在執(zhí)行上面的waitfor語(yǔ)句期間，執(zhí)行其次節(jié)中提供的語(yǔ)句來(lái)查看鎖信息： query1中，持有l(wèi)ock1中第一行(表中惟獨(dú)一行數(shù)據(jù))的行排他鎖(rid:x)，并持有該行所在頁(yè)的意向更新鎖(pag:ix)、該表的意向更新鎖(tab:ix)；query2中，持有l(wèi)ock2中第一行(表中惟獨(dú)一行數(shù)據(jù))的行排他鎖(rid:x)，并持有該行所在頁(yè)的意向更新鎖(pag:ix)、該表的意向更新鎖(tab:ix)； 執(zhí)行完waitfor，query1查詢lock2，哀求

16、在資源上加s鎖，但該行已經(jīng)被query2加上了x鎖；query2查詢lock1，哀求在資源上加s鎖，但該行已經(jīng)被query1加上了x鎖；于是兩個(gè)查詢持有資源并互不相讓，構(gòu)成死鎖。 (4). 解決方法 a). sql server自動(dòng)挑選一條sql作死鎖犧牲品：運(yùn)行完上面的兩個(gè)查詢后，我們會(huì)發(fā)覺有一條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). 按同一挨次拜訪對(duì)象：顛倒隨

17、意一條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 -lock2:rid:x rollback tran; 固然這樣修改也是有代價(jià)的，這會(huì)導(dǎo)致第一條sql執(zhí)行完畢之前，其次條sql向來(lái)處于堵塞狀態(tài)。單獨(dú)執(zhí)行query1或query2需要約1分鐘，但假如開頭執(zhí)行query1時(shí)，馬上同時(shí)執(zhí)行query2，則query2需要2分鐘才干執(zhí)行完；這種按挨次哀求資

18、源從一定程度上降低了并發(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ā)地拜訪同一資源。固然，此辦法適合解決讀與寫并發(fā)死鎖的狀況，但加with(nolock)可能會(huì)導(dǎo)致臟讀。 select * from lock2 with(nolock) select * from lock1 with(nolock) d). 用法較低的隔離級(jí)別。sql se

19、rver 2000支持四種事務(wù)處理隔離級(jí)別(til)，分離為：read uncommitted、read committed、repeatable read、serializable；sql server 2005中增強(qiáng)了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鎖，

20、而不會(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)哀求鎖超過(guò)設(shè)定的timeout_period時(shí)光后，就會(huì)終止當(dāng)前sql的執(zhí)行，犧牲自己，成全別人。 f). 用法基于行版本控制的隔離級(jí)別(sql server 2005支持)：開啟下面的選項(xiàng)后，select不會(huì)對(duì)哀求的資源加s鎖，不加鎖或者加sch-s鎖，從而將讀與寫操作之間發(fā)生

21、的死鎖幾率降至最低；而且不會(huì)發(fā)生臟讀。啊 set allow_snapshot_isolation on set read_committed_snapshot on g). 用法綁定銜接(用法辦法見下一個(gè)示例。) 5.2 程序死鎖(sql堵塞) 看一個(gè)例子：一個(gè)典型的數(shù)據(jù)庫(kù)操作事務(wù)死鎖分析，根據(jù)我自己的理解，我覺得這應(yīng)當(dāng)算是c程序中浮現(xiàn)死鎖，而不是數(shù)據(jù)庫(kù)中的死鎖；下面的代碼模擬了該文中對(duì)數(shù)據(jù)庫(kù)的操作過(guò)程： /略去的無(wú)關(guān)的code sqlconnection conn = new sqlconnection(connectionstring); conn.open(); sqltransact

22、ion tran = conn.begintransaction(); string sql1 = update lock1 set c1=c1 1 string sql2 = select * from lock1 exeenonquery(tran, sql1); /用法事務(wù):事務(wù)中l(wèi)ock了table executenonquery(null, sql2); /新開一個(gè)connection來(lái)讀取table public static void executenonquery(sqltransaction tran, string sql) sqlcommand cmd = new sql

23、command(sql); if (tran != null) cmd.connection = tran.connection; cmd.transaction = tran; cmd.executenonquery(); ee 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)題： 代

24、碼從上往下執(zhí)行，會(huì)話1持有了表lock1的x鎖，且事務(wù)沒有結(jié)束，回話1就向來(lái)持有x鎖不釋放；而會(huì)話2執(zhí)行select操作，哀求在表lock1上加s鎖，但s鎖與x鎖是不兼容的，所以回話2的被堵塞等待，不在等待中，就在等待中獲得資源，就在等待中超時(shí)。從中我們可以看到，里面并沒有浮現(xiàn)死鎖，而只是select操作被堵塞了。也正由于不是數(shù)據(jù)庫(kù)死鎖，所以sql server的鎖監(jiān)視器無(wú)法檢測(cè)到死鎖。 我們?cè)購(gòu)腸程序的角度來(lái)看該問(wèn)題： c程序持有了表lock1上的x鎖，同時(shí)開了另一個(gè)sqlconnection還想在該表上哀求一把s鎖，圖中已經(jīng)構(gòu)成了環(huán)路；太貪心了，結(jié)果自己把自己給鎖死了。 雖然這不是一個(gè)數(shù)據(jù)

25、庫(kù)死鎖，但卻是由于數(shù)據(jù)庫(kù)資源而導(dǎo)致的死鎖，上例中提到的解決死鎖的辦法在這里也基本適用，主要是避開讀操作被堵塞，解決辦法如下： a). 把select放在update語(yǔ)句前：select不在事務(wù)中，且執(zhí)行完畢會(huì)釋放s鎖； b). 把select也放加入到事務(wù)中：executenonquery(tran, sql2); c). select加with(nolock)提醒：可能產(chǎn)生臟讀； d). 降低事務(wù)隔離級(jí)別：select語(yǔ)句前加set transaction isolation level read uncommitted；同上，可能產(chǎn)生臟讀； e). 用法基于行版本控制的隔離級(jí)別（同上例）。

26、 g). 用法綁定銜接：取得事務(wù)所在會(huì)話的token，然后傳入新開的connection中；執(zhí)行exec sp_bindsession token后綁定了銜接，最后執(zhí)行exec sp_bindsession null;來(lái)取消綁定；最后需要注重的四點(diǎn)是： (1). 用法了綁定銜接的多個(gè)connection分享同一個(gè)事務(wù)和相同的鎖，但各自保留自己的事務(wù)隔離級(jí)別； (2). 假如在sql3字符串的“exec sp_bindsession null”換成“commit tran”或者“rollback tran”，則會(huì)提交囫圇事務(wù)，最后一行c代碼tran.commit()就可以不用執(zhí)行了(執(zhí)行會(huì)報(bào)錯(cuò)，由于事務(wù)已經(jīng)結(jié)束了-,-)。 (3). 開啟事務(wù)(begin tran)后，才可以調(diào)用exec sp_getbindtoken token out來(lái)取得token；假如不想再新開的connection中結(jié)束掉原有的事務(wù)，則在這個(gè)connection close之前，必需執(zhí)行“exec sp_bindsess