摘要：?SQL Server死鎖的捕獲、分析和解決方法。
一、什么是死鎖？
簡單來說，我和你，金鎖和銀鎖。
我拿著金鎖，我需要再拿到銀鎖，才能完成任務(wù)，
你拿著銀鎖，你需要再拿到金鎖，才能完成任務(wù)。
我拿不到銀鎖，你拿不到金鎖，這就形成死鎖了。
二、死鎖發(fā)生后，SQL Server怎么處理？
SQL Server內(nèi)置有死鎖偵測和處理機(jī)制，每5S會檢測一次，如果有死鎖，就會評估下哪個事務(wù)回滾的開銷比較低，將其kill掉，然后反饋1205錯誤。
實(shí)際上并沒有這么簡單，比如可以設(shè)置會話的優(yōu)先級，優(yōu)先級越低，被選為犧牲品的可能性就越大。
三、死鎖發(fā)生后怎么處理？
捕獲死鎖>>分析死鎖>>解決方案
先模擬獲取死鎖的demo
/*建表*/CREATETABLE[dbo].[deadlockTest]([id][int]IDENTITY(1,1)NOTNULL,[userid][varchar](10)NULL,[num][int]NULL,CONSTRAINT[PK_deadlockTest]PRIMARYKEYCLUSTERED([id]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]/*建索引*/createindexix_useridondeadlockTest(userid)/*生成測試數(shù)據(jù)*/insertintodeadlockTestselect1,1insertintodeadlockTestselect2,2/*事務(wù)1*/--我begintranupdatedeadlockTestsetnum=100whereid=1--金鎖updatedeadlockTestsetnum=100whereid=2--銀鎖? ?。∽⒁膺@一句執(zhí)行事務(wù)2后，回來再執(zhí)行/*事務(wù)2*/--你begintranupdatedeadlockTestsetnum=100whereid=2--銀鎖updatedeadlockTestsetnum=100whereid=1--金鎖
結(jié)果：
1、捕獲死鎖，方法較多，常用的兩種方法
>>打開跟蹤標(biāo)志 1222[RDS不支持]，可以從SQL SERVER日志中獲取到死鎖信息
DBCC TRACEON(1222,-1)
>>SQL Server Profiler抓取
2、分析死鎖
完整的死鎖信息【敏感信息XXX脫敏】綠底和紅字是分析文字
第一部分：犧牲品
?
第二部分：進(jìn)程信息
?
<process id="process40fb278"taskpriority="0" logused="144"?waitresource="KEY: 5:72057594039238656 (61a06abd401c)"?waittime="3421" ownerId="83492" transactionname="user_transaction" lasttranstarted="2018-01-31T00:19:12.110" XDES="0xa9ac290" lockMode="X" schedulerid="4" kpid="4312" status="suspended" spid="53" sbid="0" ecid="0" priority="0"?trancount="2"?lastbatchstarted="2018-01-31T00:19:17.500" lastbatchcompleted="2018-01-31T00:19:12.110" lastattention="1900-01-01T00:00:00.110" clientapp="Microsoft SQL Server Management Studio - 查詢" hostname="ALI-XXX" hostpid="13556" loginname="HZ\XXX" isolationlevel="read committed (2)" xactid="83492"?currentdb="5"?lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
? ?
UPDATE [deadlockTest] set [num] = @1? WHERE [id]=@2???
update deadlockTest set num=100 where id=2??
? ?
update deadlockTest set num=100 where id=2??
<process id="process40eb468"?taskpriority="0" logused="144"?waitresource="KEY: 5:72057594039238656 (8194443284a0)"?waittime="6676" ownerId="83494" transactionname="user_transaction" lasttranstarted="2018-01-31T00:19:14.247" XDES="0x4126378" lockMode="X" schedulerid="2" kpid="4340" status="suspended" spid="52" sbid="0" ecid="0" priority="0"?trancount="2"?lastbatchstarted="2018-01-31T00:19:14.247" lastbatchcompleted="2018-01-31T00:19:08.843" lastattention="1900-01-01T00:00:00.843" clientapp="Microsoft SQL Server Management Studio - 查詢" hostname="ALI-XXX" hostpid="13556" loginname="HZ\XXX" isolationlevel="read committed (2)" xactid="83494" currentdb="5" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
? ?
UPDATE [deadlockTest] set [num] = @1? WHERE [id]=@2? ??
update deadlockTest set num=100 where id=1
?--commit? ? ?
? ?
begin tran?
update deadlockTest set num=100 where id=2
update deadlockTest set num=100 where id=1
--commit?
第三部分：資源信息
?
hobtid="72057594039238656" dbid="5" objectname="blockTest.dbo.deadlockTest" indexname="PK_deadlockTest"?id="lock2c69480" mode="X" associatedObjectId="72057594039238656">
? ?
??
? ?
<waiter id="process40fb278" mode="X" requestType="wait"/>
? ?
? ?
? ?
根據(jù)上面的死鎖信息，可以得到：
可以看到死鎖的原因，兩個事務(wù)都想獲取對方持有資源上的X鎖進(jìn)行update，互不相讓，所以就形成了死鎖。
KEY: 5:72057594039238656? ?這個說明，鎖定的資源是KEY，數(shù)據(jù)庫是5，資源是72057594039238656
這些可以通過腳本獲取到具體的信息，但是沒有必要，第三部分resource-list完全可以獲取到，數(shù)據(jù)庫是blockTest，表示deadlockTest，鍵是PK_deadlockTest
解決方案，
由于兩個事務(wù)執(zhí)行的SQL順序相反，所以產(chǎn)生了這種情況，該case的解法就是將兩個事務(wù)SQL執(zhí)行順序設(shè)為一致即可。
四、死鎖總結(jié)
死鎖不能完全避免，只能是盡量降低，一般常用的方法【降低互斥發(fā)生的風(fēng)險、減少鎖的申請數(shù)量、降低鎖持有的時間】：
1、按相同順序訪問對象
這樣就會降低互斥發(fā)生的風(fēng)險，也就是DEMO的這個案例
2、事務(wù)盡量簡短
因?yàn)槭聞?wù)commit后，才會釋放該事務(wù)中持有的鎖，所以事務(wù)越簡短，持有鎖的時間就會越短，從而降低死鎖發(fā)生的概率。
3、優(yōu)化SQL，盡量避免table scan，index scan這種方式
這樣SQL執(zhí)行掃描/查找的數(shù)量就會減少，從而達(dá)到減少申請鎖的數(shù)量的目的。
4、事務(wù)中避免與用戶的交互
這個會大大增長鎖持有的時間，DEMO的這個案例，也可以理解為與用戶交互了，因?yàn)槭聞?wù)1第二個update，測試時是等待第二個事務(wù)執(zhí)行后，再去手動執(zhí)行的。
還有一個方法，就是降低事務(wù)的隔離級別，低隔離級別S鎖的持有時間會較短，但是這個方法大部分情況下是不可能采納的。
版權(quán)聲明：本文內(nèi)容由互聯(lián)網(wǎng)用戶自發(fā)貢獻(xiàn)，版權(quán)歸作者所有，本社區(qū)不擁有所有權(quán)，也不承擔(dān)相關(guān)法律責(zé)任。如果您發(fā)現(xiàn)本社區(qū)中有涉嫌抄襲的內(nèi)容，歡迎發(fā)送郵件至：yqgroup@service.aliyun.com?進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，本社區(qū)將立刻刪除涉嫌侵權(quán)內(nèi)容。
