如何批量殺死sqlserver死鎖
成都創(chuàng)新互聯(lián)公司-專(zhuān)業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性?xún)r(jià)比滁州網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式滁州網(wǎng)站制作公司更省心,省錢(qián),快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋滁州地區(qū)。費(fèi)用合理售后完善，十多年實(shí)體公司更值得信賴(lài)。
使用sp_lock可以查看鎖的信息， 使用kill 可以把產(chǎn)生鎖的聯(lián)接刪除。 產(chǎn)生鎖，一般是開(kāi)發(fā)過(guò)程的處理有問(wèn)題。 還有就是增加關(guān)鍵字或是索引，減小鎖的粒度。
查詢(xún)語(yǔ)句的表名后加(nolock)少用臨時(shí)表和group by HAVING。
這是與檢測(cè)死鎖相配套的一種措施。
當(dāng)檢測(cè)到系統(tǒng)中已發(fā)生死鎖時(shí)，須將進(jìn)程從死鎖狀態(tài)中解脫出來(lái)。
常用的實(shí)施方法是撤銷(xiāo)或掛起一些進(jìn)程，以便回收一些資源，再將這些資源分配給已處于阻塞狀態(tài)的進(jìn)程，使之轉(zhuǎn)為就緒狀態(tài)，以繼續(xù)運(yùn)行。
死鎖的檢測(cè)和解除措施，有可能使系統(tǒng)獲得較好的資源利用率和吞吐量，但在實(shí)現(xiàn)上難度也最大。
(1)
HOLDLOCK:
在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語(yǔ)句執(zhí)行完立即釋放所添加的鎖。
(2)
NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個(gè)選項(xiàng)僅僅應(yīng)用于SELECT語(yǔ)句。
(3)
PAGLOCK：指定添加頁(yè)鎖（否則通?？赡芴砑颖礞i）。
(4)
READCOMMITTED用與運(yùn)行在提交讀隔離級(jí)別的事務(wù)相同的鎖語(yǔ)義執(zhí)行掃描。默認(rèn)情況下，SQL
Server
2000
在此隔離級(jí)別上操作。
(5)
READPAST:
跳過(guò)已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過(guò)那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，
READPAST僅僅應(yīng)用于READ
COMMITTED隔離性級(jí)別下事務(wù)操作中的SELECT語(yǔ)句操作。
(6)
READUNCOMMITTED：等同于NOLOCK。
(7)
REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級(jí)別。
(8)
ROWLOCK：使用行級(jí)鎖，而不使用粒度更粗的頁(yè)級(jí)鎖和表級(jí)鎖。
(9)
SERIALIZABLE：用與運(yùn)行在可串行讀隔離級(jí)別的事務(wù)相同的鎖語(yǔ)義執(zhí)行掃描。等同于
HOLDLOCK。
(10)
TABLOCK：指定使用表級(jí)鎖，而不是使用行級(jí)或頁(yè)面級(jí)的鎖，SQL
Server在該語(yǔ)句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了...(1)
HOLDLOCK:
在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語(yǔ)句執(zhí)行完立即釋放所添加的鎖。
(2)
NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個(gè)選項(xiàng)僅僅應(yīng)用于SELECT語(yǔ)句。
(3)
PAGLOCK：指定添加頁(yè)鎖（否則通?？赡芴砑颖礞i）。
(4)
READCOMMITTED用與運(yùn)行在提交讀隔離級(jí)別的事務(wù)相同的鎖語(yǔ)義執(zhí)行掃描。默認(rèn)情況下，SQL
Server
2000
在此隔離級(jí)別上操作。
(5)
READPAST:
跳過(guò)已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過(guò)那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，
READPAST僅僅應(yīng)用于READ
COMMITTED隔離性級(jí)別下事務(wù)操作中的SELECT語(yǔ)句操作。
(6)
READUNCOMMITTED：等同于NOLOCK。
(7)
REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級(jí)別。
(8)
ROWLOCK：使用行級(jí)鎖，而不使用粒度更粗的頁(yè)級(jí)鎖和表級(jí)鎖。
(9)
SERIALIZABLE：用與運(yùn)行在可串行讀隔離級(jí)別的事務(wù)相同的鎖語(yǔ)義執(zhí)行掃描。等同于
HOLDLOCK。
(10)
TABLOCK：指定使用表級(jí)鎖，而不是使用行級(jí)或頁(yè)面級(jí)的鎖，SQL
Server在該語(yǔ)句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結(jié)束。
(11)
TABLOCKX：指定在表上使用排它鎖，這個(gè)鎖可以阻止其他事務(wù)讀或更新這個(gè)表的數(shù)據(jù)，直到這個(gè)語(yǔ)句或整個(gè)事務(wù)結(jié)束。
(12)
UPDLOCK
：指定在
讀表中數(shù)據(jù)時(shí)設(shè)置更新
鎖（update
lock）而不是設(shè)置共享鎖，該鎖一直保持到這個(gè)語(yǔ)句或整個(gè)事務(wù)結(jié)束，使用UPDLOCK的作用是允許用戶先讀取數(shù)據(jù)（而且不阻塞其他用戶讀數(shù)據(jù)），并且保證在后來(lái)再更新數(shù)據(jù)時(shí)，這一段時(shí)間內(nèi)這些數(shù)據(jù)沒(méi)有被其他用戶修改。
死鎖，簡(jiǎn)而言之，兩個(gè)或者多個(gè)trans，同時(shí)請(qǐng)求對(duì)方正在請(qǐng)求的某個(gè)對(duì)象，導(dǎo)致雙方互相等待。簡(jiǎn)單的例子如下：
trans1 trans2
------------------------------------------------------------------------
1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction
2.update table A 2.update table B
3.update table B 3.update table A
4.IDBConnection.Commit 4.IDBConnection.Commit
那么，很容易看到，如果trans1和trans2，分別到達(dá)了step3，那么trans1會(huì)請(qǐng)求對(duì)于B的X鎖，trans2會(huì)請(qǐng)求對(duì)于A的X鎖，而二者的鎖在step2上已經(jīng)被對(duì)方分別持有了。由于得不到鎖，后面的Commit無(wú)法執(zhí)行，這樣雙方開(kāi)始死鎖。
好，我們看一個(gè)簡(jiǎn)單的例子，來(lái)解釋一下，應(yīng)該如何解決死鎖問(wèn)題。
-- Batch #1
CREATE DATABASE deadlocktest
GO
USE deadlocktest
SET NOCOUNT ON
DBCC TRACEON (1222, -1)
-- 在SQL2005中，增加了一個(gè)新的dbcc參數(shù)，就是1222，原來(lái)在2000下，我們知道，可以執(zhí)行dbcc
--traceon(1204,3605,-1)看到所有的死鎖信息。SqlServer 2005中，對(duì)于1204進(jìn)行了增強(qiáng)，這就是1222。
GO
IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1
IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2
GO
CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))
GO
DECLARE @x int
SET @x = 1
WHILE (@x = 1000) BEGIN
INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)
SET @x = @x + 1
END
GO
CREATE CLUSTERED INDEX cidx ON t1 (c1)
CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)
GO
CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
GO
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
GO
上述sql創(chuàng)建一個(gè)deadlock的示范數(shù)據(jù)庫(kù)，插入了1000條數(shù)據(jù)，并在表t1上建立了c1列的聚集索引，和c2列的非聚集索引。另外創(chuàng)建了兩個(gè)sp，分別是從t1中select數(shù)據(jù)和update數(shù)據(jù)。
好，打開(kāi)一個(gè)新的查詢(xún)窗口，我們開(kāi)始執(zhí)行下面的query：
-- Batch #2
USE deadlocktest
SET NOCOUNT ON
WHILE (1=1) EXEC p2 4
GO
開(kāi)始執(zhí)行后，然后我們打開(kāi)第三個(gè)查詢(xún)窗口，執(zhí)行下面的query：
-- Batch #3
USE deadlocktest
SET NOCOUNT ON
CREATE TABLE #t1 (c2 int, c3 int)
GO
WHILE (1=1) BEGIN
INSERT INTO #t1 EXEC p1 4
TRUNCATE TABLE #t1
END
GO
開(kāi)始執(zhí)行，哈哈，很快，我們看到了這樣的錯(cuò)誤信息：
Msg 1205, Level 13, State 51, Procedure p1, Line 4
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
spid54發(fā)現(xiàn)了死鎖。
那么，我們?cè)撊绾谓鉀Q它？
在SqlServer 2005中，我們可以這么做：
1.在trans3的窗口中，選擇EXEC p1 4，然后right click，看到了菜單了嗎？選擇Analyse Query in Database Engine Tuning Advisor。
2.注意右面的窗口中，wordload有三個(gè)選擇：負(fù)載文件、表、查詢(xún)語(yǔ)句，因?yàn)槲覀冞x擇了查詢(xún)語(yǔ)句的方式，所以就不需要修改這個(gè)radio option了。
3.點(diǎn)左上角的Start Analysis按鈕
4.抽根煙，回來(lái)后看結(jié)果吧！出現(xiàn)了一個(gè)分析結(jié)果窗口，其中，在Index Recommendations中，我們發(fā)現(xiàn)了一條信息：大意是，在表t1上增加一個(gè)非聚集索引索引：t2+t1。
5.在當(dāng)前窗口的上方菜單上，選擇Action菜單，選擇Apply Recommendations，系統(tǒng)會(huì)自動(dòng)創(chuàng)建這個(gè)索引。
重新運(yùn)行batch #3，呵呵，死鎖沒(méi)有了。
這種方式，我們可以解決大部分的Sql Server死鎖問(wèn)題。那么，發(fā)生這個(gè)死鎖的根本原因是什么呢？為什么增加一個(gè)non clustered index，問(wèn)題就解決了呢？ 這次，我們分析一下，為什么會(huì)死鎖呢？再回顧一下兩個(gè)sp的寫(xiě)法：
CREATE PROC p1 @p1 int AS
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
GO
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
GO
很奇怪吧！p1沒(méi)有insert，沒(méi)有delete，沒(méi)有update，只是一個(gè)select，p2才是update。這個(gè)和我們前面說(shuō)過(guò)的，trans1里面updata A，update B；trans2里面upate B，update A，根本不貼邊??！
那么，什么導(dǎo)致了死鎖？
需要從事件日志中，看sql的死鎖信息：
Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.
The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.
首先，我們看看p1的執(zhí)行計(jì)劃。怎么看呢？可以執(zhí)行set statistics profile on，這句就可以了。下面是p1的執(zhí)行計(jì)劃
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))
|--Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] = [@p1] AND [t1].[c2] = [@p1]+(1)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)
我們看到了一個(gè)nested loops，第一行，利用索引t1.c2來(lái)進(jìn)行seek，seek出來(lái)的那個(gè)rowid，在第二行中，用來(lái)通過(guò)聚集索引來(lái)查找整行的數(shù)據(jù)。這是什么？就是bookmark lookup??！為什么？因?yàn)槲覀冃枰腸2、c3不能完全的被索引t1.c1帶出來(lái)，所以需要書(shū)簽查找。
好，我們接著看p2的執(zhí)行計(jì)劃。
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
|--Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))
|--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
|--Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN ...
|--Top(ROWCOUNT est 0)
|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)
通過(guò)聚集索引的seek找到了一行，然后開(kāi)始更新。這里注意的是，update的時(shí)候，它會(huì)申請(qǐng)一個(gè)針對(duì)clustered index的X鎖的。
實(shí)際上到這里，我們就明白了為什么update會(huì)對(duì)select產(chǎn)生死鎖。update的時(shí)候，會(huì)申請(qǐng)一個(gè)針對(duì)clustered index的X鎖，這樣就阻塞住了（注意，不是死鎖?。﹕elect里面最后的那個(gè)clustered index seek。死鎖的另一半在哪里呢？注意我們的select語(yǔ)句，c2存在于索引idx1中，c1是一個(gè)聚集索引cidx。問(wèn)題就在這里！我們?cè)趐2中更新了c2這個(gè)值，所以sqlserver會(huì)自動(dòng)更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我們剛才的select語(yǔ)句中。而對(duì)這個(gè)索引列的更改，意味著索引集合的某個(gè)行或者某些行，需要重新排列，而重新排列，需要一個(gè)X鎖。
SO………，問(wèn)題就這樣被發(fā)現(xiàn)了。
總結(jié)一下，就是說(shuō)，某個(gè)query使用非聚集索引來(lái)select數(shù)據(jù)，那么它會(huì)在非聚集索引上持有一個(gè)S鎖。當(dāng)有一些select的列不在該索引上，它需要根據(jù)rowid找到對(duì)應(yīng)的聚集索引的那行，然后找到其他數(shù)據(jù)。而此時(shí)，第二個(gè)的查詢(xún)中，update正在聚集索引上忙乎：定位、加鎖、修改等。但因?yàn)檎谛薷牡哪硞€(gè)列，是另外一個(gè)非聚集索引的某個(gè)列，所以此時(shí)，它需要同時(shí)更改那個(gè)非聚集索引的信息，這就需要在那個(gè)非聚集索引上，加第二個(gè)X鎖。select開(kāi)始等待update的X鎖，update開(kāi)始等待select的S鎖，死鎖，就這樣發(fā)生鳥(niǎo)。
那么，為什么我們?cè)黾恿艘粋€(gè)非聚集索引，死鎖就消失鳥(niǎo)？我們看一下，按照上文中自動(dòng)增加的索引之后的執(zhí)行計(jì)劃：
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|--Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] = [@p1] AND [deadlocktest].[dbo].[t1].[c2] = [@p1]+(1)) ORDERED FORWARD)
哦，對(duì)于clustered index的需求沒(méi)有了，因?yàn)樵黾拥母采w索引已經(jīng)足夠把所有的信息都select出來(lái)。就這么簡(jiǎn)單。
實(shí)際上，在sqlserver 2005中，如果用profiler來(lái)抓eventid：1222，那么會(huì)出現(xiàn)一個(gè)死鎖的圖，很直觀的說(shuō)。
下面的方法，有助于將死鎖減至最少（詳細(xì)情況，請(qǐng)看SQLServer聯(lián)機(jī)幫助，搜索：將死鎖減至最少即可。
按同一順序訪問(wèn)對(duì)象。
避免事務(wù)中的用戶交互。
保持事務(wù)簡(jiǎn)短并處于一個(gè)批處理中。
使用較低的隔離級(jí)別。
使用基于行版本控制的隔離級(jí)別。
將 READ_COMMITTED_SNAPSHOT 數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為 ON，使得已提交讀事務(wù)使用行版本控制。
使用快照隔離。
使用綁定連接。
重慶分公司
重慶分公司
