建站
咨詢
售后
建站咨詢
新聞中心
sqlserver 數(shù)據(jù)庫(kù)死鎖后多長(zhǎng)時(shí)間解鎖
SQL SERVER 通過鎖管理器自動(dòng)發(fā)現(xiàn)和解決死鎖。在 SQL SERVER 中 Lock Monitor 管理線程（spid=4）每 5 秒鐘檢查一次系統(tǒng)中是否存在死鎖，同時(shí)也會(huì)使用死鎖發(fā)現(xiàn)計(jì)數(shù)器（Deadlock Detection Counter）控制檢查死鎖的頻率。
目前創(chuàng)新互聯(lián)建站已為上1000+的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)站空間、網(wǎng)站運(yùn)營(yíng)、企業(yè)網(wǎng)站設(shè)計(jì)、紫金網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。
死鎖發(fā)現(xiàn)計(jì)數(shù)器初始值為 3，當(dāng)發(fā)現(xiàn)死鎖時(shí)被重新設(shè)置為 3，當(dāng)沒有發(fā)現(xiàn)死鎖時(shí)此值減 1。如果死鎖發(fā)現(xiàn)計(jì)數(shù)器大于 0，則在每次有進(jìn)程獲取鎖被阻止時(shí)，鎖管理器都要求 Lock Monitor 線程檢查死鎖；而如果計(jì)數(shù)器等于 0，則在每次有進(jìn)程獲取鎖被阻止時(shí)，鎖管理器不會(huì)要求 Lock Monitor 線程檢查死鎖，只是每 5 秒鐘檢查一次。
Lock Monitor 線程通過檢查鎖的等待列表發(fā)現(xiàn)保持鎖的進(jìn)程和等待鎖的進(jìn)程間的關(guān)系，從而發(fā)現(xiàn)死鎖。
sqlserver 線程鎖死怎么辦
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)資源；
其滿足上面死鎖的四個(gè)必要條件：
(1).互斥：資源S1和S2不能被共享，同一時(shí)間只能由一個(gè)任務(wù)使用；
(2).請(qǐng)求與保持條件：T1持有S1的同時(shí)，請(qǐng)求S2；T2持有S2的同時(shí)請(qǐng)求S1；
(3).非剝奪條件：T1無法從T2上剝奪S2，T2也無法從T1上剝奪S1；
(4).循環(huán)等待條件：上圖中的箭頭構(gòu)成環(huán)路，存在循環(huán)等待。
如何處理SQL Server死鎖問題
死鎖，簡(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無法執(zhí)行，這樣雙方開始死鎖。
好，我們看一個(gè)簡(jiǎn)單的例子，來解釋一下，應(yīng)該如何解決死鎖問題。
-- Batch #1
CREATE DATABASE deadlocktest
GO
USE deadlocktest
SET NOCOUNT ON
DBCC TRACEON (1222, -1)
-- 在SQL2005中，增加了一個(gè)新的dbcc參數(shù)，就是1222，原來在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ù)。
好，打開一個(gè)新的查詢窗口，我們開始執(zhí)行下面的query：
-- Batch #2
USE deadlocktest
SET NOCOUNT ON
WHILE (1=1) EXEC p2 4
GO
開始執(zhí)行后，然后我們打開第三個(gè)查詢窗口，執(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
開始執(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ù)載文件、表、查詢語句，因?yàn)槲覀冞x擇了查詢語句的方式，所以就不需要修改這個(gè)radio option了。
3.點(diǎn)左上角的Start Analysis按鈕
4.抽根煙，回來后看結(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，呵呵，死鎖沒有了。
這種方式，我們可以解決大部分的Sql Server死鎖問題。那么，發(fā)生這個(gè)死鎖的根本原因是什么呢？為什么增加一個(gè)non clustered index，問題就解決了呢？ 這次，我們分析一下，為什么會(huì)死鎖呢？再回顧一下兩個(gè)sp的寫法：
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沒有insert，沒有delete，沒有update，只是一個(gè)select，p2才是update。這個(gè)和我們前面說過的，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來進(jìn)行seek，seek出來的那個(gè)rowid，在第二行中，用來通過聚集索引來查找整行的數(shù)據(jù)。這是什么？就是bookmark lookup?。槭裁?？因?yàn)槲覀冃枰腸2、c3不能完全的被索引t1.c1帶出來，所以需要書簽查找。
好，我們接著看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)
通過聚集索引的seek找到了一行，然后開始更新。這里注意的是，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語句，c2存在于索引idx1中，c1是一個(gè)聚集索引cidx。問題就在這里！我們?cè)趐2中更新了c2這個(gè)值，所以sqlserver會(huì)自動(dòng)更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我們剛才的select語句中。而對(duì)這個(gè)索引列的更改，意味著索引集合的某個(gè)行或者某些行，需要重新排列，而重新排列，需要一個(gè)X鎖。
SO………，問題就這樣被發(fā)現(xiàn)了。
總結(jié)一下，就是說，某個(gè)query使用非聚集索引來select數(shù)據(jù)，那么它會(huì)在非聚集索引上持有一個(gè)S鎖。當(dāng)有一些select的列不在該索引上，它需要根據(jù)rowid找到對(duì)應(yīng)的聚集索引的那行，然后找到其他數(shù)據(jù)。而此時(shí)，第二個(gè)的查詢中，update正在聚集索引上忙乎：定位、加鎖、修改等。但因?yàn)檎谛薷牡哪硞€(gè)列，是另外一個(gè)非聚集索引的某個(gè)列，所以此時(shí)，它需要同時(shí)更改那個(gè)非聚集索引的信息，這就需要在那個(gè)非聚集索引上，加第二個(gè)X鎖。select開始等待update的X鎖，update開始等待select的S鎖，死鎖，就這樣發(fā)生鳥。
那么，為什么我們?cè)黾恿艘粋€(gè)非聚集索引，死鎖就消失鳥？我們看一下，按照上文中自動(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的需求沒有了，因?yàn)樵黾拥母采w索引已經(jīng)足夠把所有的信息都select出來。就這么簡(jiǎn)單。
實(shí)際上，在sqlserver 2005中，如果用profiler來抓eventid：1222，那么會(huì)出現(xiàn)一個(gè)死鎖的圖，很直觀的說。
下面的方法，有助于將死鎖減至最少（詳細(xì)情況，請(qǐng)看SQLServer聯(lián)機(jī)幫助，搜索：將死鎖減至最少即可。
按同一順序訪問對(duì)象。
避免事務(wù)中的用戶交互。
保持事務(wù)簡(jiǎn)短并處于一個(gè)批處理中。
使用較低的隔離級(jí)別。
使用基于行版本控制的隔離級(jí)別。
將 READ_COMMITTED_SNAPSHOT 數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為 ON，使得已提交讀事務(wù)使用行版本控制。
使用快照隔離。
使用綁定連接。
如何監(jiān)控sqlserver 性能 死鎖
具體步驟如下：
1.首先使用下面的命令，將有關(guān)的跟蹤標(biāo)志啟用。
SQL codeDBCC TRACEON (3605,1204,1222,-1)
說明：
3605
將DBCC的結(jié)果輸出到錯(cuò)誤日志。
1204 返回參與死鎖的鎖的資源和類型，以及受影響的當(dāng)前命令。
1222
返回參與死鎖的鎖的資源和類型，以及使用了不符合任何 XSD 架構(gòu)的 XML 格式的受影響的當(dāng)前命令(比1204更進(jìn)一步,SQL
2005及以上可用)。
-1 以全局方式打開指定的跟蹤標(biāo)記。
以上跟蹤標(biāo)志作用域都是全局，即在SQL
Server運(yùn)行過程中，會(huì)一直發(fā)揮作用，直到SQL Server重啟。
如 果要確保SQL Server在重啟后自動(dòng)開啟這些標(biāo)志，可以在SQL
Server服務(wù)啟動(dòng)選項(xiàng)中，使用 /T 啟動(dòng)選項(xiàng)指定跟蹤標(biāo)志在啟動(dòng)期
間設(shè)置為開。(位于SQL Server配置管理器-SQL
Server服務(wù)-SQL Server-屬性-高級(jí)-啟動(dòng)參數(shù))
在運(yùn)行上面的語句后，當(dāng)SQL
Server中發(fā)生死鎖時(shí)，已經(jīng)可以在錯(cuò)誤日志中看到了，但還不夠直觀(和其它信息混在一起)。（SSMS
- SQL Server實(shí)例 -
管理 - SQL Server日志）
2.建表，存放死鎖記錄
SQL codeUSE [Cole] --Cole是我的示例數(shù)據(jù)庫(kù)，你可以根據(jù)實(shí)際情況修改。 GO
CREATE TABLE DeadLockLog ( id int IDENTITY (1, 1) NOT NULL, LogDate DATETIME, ProcessInfo VARCHAR(10), ErrorText VARCHAR(MAX) )
GO
3.建立JOB
新建一個(gè)JOB（假設(shè)名稱為DeadLockJob）,在"步驟"中新建一步驟，隨便寫一個(gè)步驟名稱，數(shù)據(jù)庫(kù)為"Cole",在"命令"欄中輸入以下語句：
SQL code--新建臨時(shí)表 IF OBJECT_ID('tempdb.dbo.#ErrorLog') IS Not Null
DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog (Id int IDENTITY (1, 1) NOT NULL, a DATETIME, b VARCHAR(10), c VARCHAR(MAX)) --將當(dāng)前日志記錄插入臨時(shí)表
INSERT INTO #ErrorLog EXEC master.dbo.sp_readerrorlog --將死鎖信息插入用戶表
insert DeadLockLog
select a, b, c from #ErrorLog where id = (select MAX(id) from #ErrorLog WHERE c Like '%Deadlock encountered%')
DROP TABLE #ErrorLog
4.新建警報(bào)
在"新建警報(bào)"窗體的"常規(guī)"選項(xiàng)卡中,進(jìn)行以下設(shè)置：
名稱：可根據(jù)實(shí)際自行命名，這里我用DeadLockAlert
類型：選擇"SQL
Server性能條件警報(bào)"
對(duì)象：SQLServer:Locks
計(jì)數(shù)器：Number of
Deadlocks/sec
實(shí)例：_Total
計(jì)數(shù)器滿足以下條件時(shí)觸發(fā)警報(bào)：高于
值：0
在"響應(yīng)"選項(xiàng)卡中，選中"執(zhí)行作業(yè)"，并選擇步驟3中我們新建的作業(yè)(即DeadlockJob)
到這里為止，我們已經(jīng)完成了全部步驟，以后，你就可以隨時(shí)查詢DeadLockLog表，來顯示死鎖信息了。
sqlserver怎么清除死鎖
1、首先需要判斷是哪個(gè)用戶鎖住了哪張表.
查詢被鎖表
select?request_session_id?spid,OBJECT_NAME(resource_associated_entity_id)?tableName?
from?sys.dm_tran_locks?where?resource_type='OBJECT'
查詢后會(huì)返回一個(gè)包含spid和tableName列的表.
其中spid是進(jìn)程名,tableName是表名.
2.了解到了究竟是哪個(gè)進(jìn)程鎖了哪張表后,需要通過進(jìn)程找到鎖表的主機(jī).
查詢主機(jī)名
exec?sp_who2?'xxx'
xxx就是spid列的進(jìn)程,檢索后會(huì)列出很多信息,其中就包含主機(jī)名.
3.通過spid列的值進(jìn)行關(guān)閉進(jìn)程.
關(guān)閉進(jìn)程
declare?@spid?int
Set?@spid?=?xxx?--鎖表進(jìn)程
declare?@sql?varchar(1000)
set?@sql='kill?'+cast(@spid?as?varchar)
exec(@sql)
PS:有些時(shí)候強(qiáng)行殺掉進(jìn)程是比較危險(xiǎn)的,所以最好可以找到執(zhí)行進(jìn)程的主機(jī),在該機(jī)器上關(guān)閉進(jìn)程.
當(dāng)前標(biāo)題：sqlserver鎖死,sqlserver查詢死鎖sql
網(wǎng)站網(wǎng)址：http://fisionsoft.com.cn/article/hcosji.html
