SQLServer死鎖的分析、處理與預(yù)防

1、sql server死鎖的分析.處理與預(yù)防所謂“死鎖”，在操作系統(tǒng)的定義是：在一組進(jìn)程中的各個(gè)進(jìn)程均占有不會(huì)釋放的 資源，但因互相申請被其他進(jìn)程所站用不會(huì)釋放的資源而處于的一種永久等待狀態(tài)。定義比較抽象，下圖可以幫助你比較直觀的理解死鎖:出現(xiàn)死鎖需要滿足幾個(gè)必要條件：a）互斥：進(jìn)程獨(dú)占資源，資源不共享；b）請求與保持：已經(jīng)得到資源的進(jìn)程可以再次申請新資源；c）不剝奪：已分配的資源不能被其它進(jìn)程強(qiáng)制剝奪；d）環(huán)路等待：幾個(gè)進(jìn)程組成環(huán)路，都在相互等待正被占用的資源；對應(yīng)到 sql server 中，在 2 個(gè)或多個(gè)任務(wù)中（insert v update、delete、select、 alter t

2、able或tran事務(wù)等等），如果每個(gè)任務(wù)鎖定了其它任務(wù)想要鎖定的資源，會(huì)造 成這些任務(wù)永久阻塞，從而出現(xiàn)死鎖。這些資源可能是：單行數(shù)據(jù)（rid、heap堆中的 行）、索引中的鍵（key,行鎖）、頁（page, 8kb）、區(qū)（extent, 8個(gè)連續(xù)頁）、堆或b 樹、表（table,數(shù)據(jù)和索引）、文件（file,數(shù)據(jù)庫文件）、整個(gè)數(shù)據(jù)庫（database）。如果系統(tǒng)中的資源不足或者資源分配策略不當(dāng)，會(huì)導(dǎo)致因進(jìn)程間的資源爭用產(chǎn)生死鎖現(xiàn)象。但更多的可能是程序員的程序有問題。“鎖”有多種方式，如意向鎖、共享鎖、 排他鎖等等。鎖還有多種粒度，如行鎖、表鎖。了解了死鎖產(chǎn)生的原因，就可以最大可能的避免與預(yù)

3、防死鎖。只要上述4個(gè)必要條 件中有1個(gè)不滿足，就不會(huì)發(fā)生死鎖。所以，在系統(tǒng)設(shè)計(jì)、實(shí)現(xiàn)階段就可以在資源分配 與占用、資源訪問順序等方面采取必要措施。2、一個(gè)例子直面死鎖，來看一個(gè)例子：如圖1所示，新建一個(gè)查詢窗口，并利用事務(wù)的原子特 性和update語句的排他鎖特性把2個(gè)表中的記錄鎖住；如圖2所示，再次新建一個(gè)查 詢窗口，2條很簡單的sql語句長時(shí)間仍沒有執(zhí)行完成。1123 ；el78910 曰一根據(jù)事務(wù)的原子性實(shí)現(xiàn)4個(gè)必要條件中l(wèi)-請求和等待 begin tran申一update語句在數(shù)據(jù)行上加排他鎖一和苴它所有鎖不兼容-一實(shí)現(xiàn)4個(gè)必要條件中的.互斤-update tb_te_brandinf

4、ormation set iscompensateoupdate tbset (descriptionj=1d洶a|<278行受影響)2曰 begin tranupdate tb_te_sizeinformation sbt (deocription)-99update tb te brandlnfomation set iscoopensate 0|q«a)00 50 kt1)3、檢測與排查3.1通過profile工具看死鎖profile是sql server自帶的跟蹤分析工具，開啟profile來捕捉死鎖信息可以更直觀的看到相關(guān)信息。3.2通過系統(tǒng)存儲(chǔ)過程看死鎖sp_who

5、和sp_lock是sql server的2個(gè)系統(tǒng)存儲(chǔ)過程，可以用它們來查詢數(shù)據(jù)庫中的鎖情況。sp_who提供有關(guān)的數(shù)據(jù)庫實(shí)例中當(dāng)前用戶、會(huì)話和進(jìn)程的信息，如下圖, 我們看到spid二56的會(huì)話（update語句）被spid二54的會(huì)話阻塞。利ieod |statuslogranei hostnamemc|dbnamei eirequest jd25270steepssa0mastertask manager026510steepogliym-pcadmr»tratoruym-pc0masterawaiting commano"oj27530steecxngliym -pcx

6、adrumtratoruym-pc0masterawaiting command'oj28540ysleeprigliympcadmnsmorftisoended liym*pcadrninistratoruym-pcijympc0 梓jybgdbjybgdbawaiting commandupdate00 1ir0steeongliym poadmrwtratoruym-pc0jybgdbawaiting command031590runnableliym pcxadrrwwtrmoruym-pc0jybgdbselect"osp_lock提供有關(guān)鎖的信息，如下圖。我們可以

7、通過spid知道是哪個(gè)會(huì)話鎖住了資源，可以通過objld知道被鎖住的資源是什么。$pd | dm |objldindld | type |resourcemode | status |j_541100sgrant254114216667381(ia7dce72d522)xgrant354114216667381(edfa0f0cd530)xgrant454114216667381key(338075145b7e)x丄grant554118411442301(756a52cd3e14)xgrant654114216s67381(55198543b0a)xgrant754114216s67381(

8、c0917b7c0cid6)xgrant_8_54114216867381(d07b46a5b6c)xgrant954114216667381(7eb5fa4c759)xgrant1054 .114216s67381f7123a0b291e3)xgrant執(zhí)行如下sql腳本獲取被鎖資源和資源所屬的數(shù)據(jù)庫：select object_name(421666738) as lockedresource, db_name(11) asdbname;dbnamelockedresourcetb_te_sizelnformationjybgdb執(zhí)行如下腳本獲取鎖資源的會(huì)話正在執(zhí)行的sql腳本:dbcc

9、inputbuffer(54);eventtypeparameters eventinfclanguage event0-根據(jù)事務(wù)的原子性實(shí)現(xiàn)個(gè)必要條件屮-請求和等待begin tran -update語句在數(shù)據(jù)行上加排他鎖 -和其它所有鎖不兼容 -實(shí)現(xiàn)個(gè)必要條件中的：互斥 update tb_te_brandinformation set iscompensate=0 update tb_te_sizeinformation set description = f 14、處理方式4.1 sql server自動(dòng)處理“無為而治s當(dāng)數(shù)據(jù)庫產(chǎn)生死鎖時(shí)，sql server通過一個(gè)叫“鎖監(jiān)視器”的東

10、西 捕獲死鎖信息，并根據(jù)一定的規(guī)則自動(dòng)選擇一個(gè)sql作為鎖的犧牲品，并返回如下報(bào)錯(cuò)信息:服務(wù)器：消息1205,級(jí)別13,狀態(tài)50,行1事務(wù)（進(jìn)程id xx）與另一個(gè)進(jìn)程已被死鎖在 lock資源上，且該事務(wù)已被選作死鎖 犧牲品。請重新運(yùn)行該事務(wù)。如果你對數(shù)據(jù)庫還不夠了解，那建議你向其他有經(jīng)驗(yàn)的人求助，在此之前不要輕易 對數(shù)據(jù)庫進(jìn)行修改。4.2 kill 會(huì)話通過3. 2中提到的系統(tǒng)存儲(chǔ)過程可以獲取到與死鎖相關(guān)的信息。可以查詢其中是哪 個(gè)spid導(dǎo)致的死鎖，并使用kill spid的方法把它干掉。但是這只能是一種臨時(shí)的解決方 案，我們不可能一遇到死鎖就在用戶的生產(chǎn)環(huán)境里排查死鎖、kill spo同

11、樣的道理，也 不可能一遇到死鎖就重啟sql server服務(wù)，甚至重啟數(shù)據(jù)庫服務(wù)器。sql腳本：kill 54;-此處54即分析后得到的spid值43設(shè)定鎖請求超時(shí)默認(rèn)情況下，數(shù)據(jù)庫沒有鎖定超時(shí)期限。也就是說一個(gè)會(huì)話在申請新的資源時(shí)，如 果這個(gè)資源已經(jīng)被其它進(jìn)程鎖定，那么本會(huì)話會(huì)一直處于等待狀態(tài)。這樣無疑是有問題 的。我們可以通過sql命令來設(shè)定鎖請求超時(shí)。也可以訪問全局變量l0ck_time0ut來 查看這個(gè)值。set lock_timeout 20000;-單位是毫秒當(dāng)請求鎖超過設(shè)定時(shí)間時(shí)，sql server將返回錯(cuò)誤。我們的程序可以根據(jù)返回的錯(cuò) 誤來進(jìn)行響應(yīng)的處理，避免長時(shí)間的用戶等待

12、。服務(wù)器：消息1222,級(jí)別16,狀態(tài)50,行1 已超過了鎖請求超時(shí)時(shí)段。當(dāng)然，使用這種方式來處理所有的鎖請求是不合適的，也是不負(fù)責(zé)任的。在多數(shù)情況下是我們的程序的設(shè)計(jì)、實(shí)現(xiàn)的問題導(dǎo)致了死鎖。在處理過程中，我們既要治標(biāo)，更要治本。4.4修改程序在3. 2的最后，我們通過系統(tǒng)存儲(chǔ)過程和幾個(gè)命令找到了鎖定資源的sql命令。以 這次ll項(xiàng)目為例，我們發(fā)現(xiàn)是web管理系統(tǒng)上的一個(gè)統(tǒng)計(jì)報(bào)表(select)在執(zhí)行過程 中長時(shí)間的那一個(gè)生產(chǎn)信息表鎖定，導(dǎo)致現(xiàn)場各機(jī)臺(tái)上位機(jī)系統(tǒng)想要插入新的生產(chǎn)記錄 (insert)時(shí)長時(shí)間等待。所以在現(xiàn)場項(xiàng)目組每次重新啟動(dòng)sql server服務(wù)或者重啟 數(shù)據(jù)庫服務(wù)器2個(gè)小時(shí)

13、以后，這個(gè)問題依然重復(fù)出現(xiàn)。這個(gè)時(shí)候如果采用ki i i掉這個(gè)統(tǒng)計(jì)報(bào)表請求的方式處理，結(jié)果和重啟sql server 服務(wù)、重啟數(shù)據(jù)庫服務(wù)器沒有區(qū)別，2個(gè)小時(shí)問題依舊。如果采用設(shè)定鎖請求超時(shí)的方 式處理，那么這個(gè)統(tǒng)計(jì)報(bào)表每次執(zhí)行都不會(huì)獲得想要的結(jié)果，而且每次執(zhí)行也會(huì)鎖定一 定的時(shí)間導(dǎo)致現(xiàn)場上位機(jī)的等待。這次我們的處理措施是：1)暫時(shí)禁用了 web管理系統(tǒng)上的這個(gè)報(bào)表功能；2)重啟 t sql server服務(wù)；3)優(yōu)化報(bào)表的sql語句；4)啟用報(bào)表功能。之后的一段時(shí)間沒 有再次出現(xiàn)這樣的問題。通過對這個(gè)報(bào)表的性能優(yōu)化，這個(gè)問題算是解決的差不多了。但是經(jīng)過事后了解, 發(fā)現(xiàn)報(bào)表的性能問題并不在于開

14、發(fā)人員的疏忽或水平不夠。問題的根本在這個(gè)生產(chǎn)信息 表的設(shè)計(jì)有問題。在一個(gè)數(shù)據(jù)量達(dá)到1000w級(jí)的表中，我們采用char (10)來保存日期值, 雖然insert、updatex delete時(shí)沒有問題，但是在執(zhí)行select且這個(gè)日期值字段作為 過濾條件時(shí)發(fā)生性能問題是必然的。經(jīng)過測試，這個(gè)字段的數(shù)據(jù)類型改為datetime時(shí) 的執(zhí)行時(shí)間不到性能優(yōu)化后的10%o所以，不但是在開發(fā)階段，早在設(shè)計(jì)階段就已經(jīng)有了性能隱患。4.5升級(jí)硬件不贅述。5、如何預(yù)防首先要理解，在多并發(fā)的環(huán)境中死鎖是不可避免的，只能通過合理的數(shù)據(jù)庫設(shè)計(jì)、 良好的索引、適當(dāng)?shù)牟樵冋Z句以及隔離等級(jí)等措施盡量減少死鎖。最開始列出了死

15、鎖的4個(gè)必要條彳牛，只要想辦法破壞任意1個(gè)或多個(gè)條件就可以避 免產(chǎn)生死鎖。下列方法有助于最大限度的降低死鎖：a) 按同一順序訪問對象；transltrans21 idbconnection begintransaction 1 idbconnection begintransaction2.update table a2.update table b3 update table b3 update table a4.idbconnection commit4.ldbconnection.commitb) 避免事務(wù)中的用戶交互，也就是在事務(wù)執(zhí)行過程中不要包含用戶交互的步驟;c) 保持事務(wù)簡短并在一個(gè)批處理中；d ) select 語句加 with(nolock)提示;select * from tablei with(nolock);select * from table2 with(nolock);這種寫法在執(zhí)行中不對查詢到的資