如何處理SQL Server死鎖問題

死鎖，簡而言之，兩個(gè)或者多個(gè)trans，同時(shí)請求對方正在請求的某個(gè)對象，導(dǎo)致雙方互相等待。簡單的例子如下：

為義馬等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及義馬網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為網(wǎng)站制作、成都網(wǎng)站制作、義馬網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎ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會請求對于B的X鎖，trans2會請求對于A的X鎖，而二者的鎖在step2上已經(jīng)被對方分別持有了。由于得不到鎖，后面的Commit無法執(zhí)行，這樣雙方開始死鎖。

好，我們看一個(gè)簡單的例子，來解釋一下，應(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中，對于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ù)庫，插入了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)了死鎖。

那么，我們該如何解決它？

在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)會自動創(chuàng)建這個(gè)索引。

重新運(yùn)行batch #3，呵呵，死鎖沒有了。

這種方式，我們可以解決大部分的Sql Server死鎖問題。那么，發(fā)生這個(gè)死鎖的根本原因是什么呢？為什么增加一個(gè)non clustered index，問題就解決了呢？ 這次，我們分析一下，為什么會死鎖呢？再回顧一下兩個(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，根本不貼邊?。?/p>

那么，什么導(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í)候，它會申請一個(gè)針對clustered index的X鎖的。

實(shí)際上到這里，我們就明白了為什么update會對select產(chǎn)生死鎖。update的時(shí)候，會申請一個(gè)針對clustered index的X鎖，這樣就阻塞住了（注意，不是死鎖?。﹕elect里面最后的那個(gè)clustered index seek。死鎖的另一半在哪里呢？注意我們的select語句，c2存在于索引idx1中，c1是一個(gè)聚集索引cidx。問題就在這里！我們在p2中更新了c2這個(gè)值，所以sqlserver會自動更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我們剛才的select語句中。而對這個(gè)索引列的更改，意味著索引集合的某個(gè)行或者某些行，需要重新排列，而重新排列，需要一個(gè)X鎖。

SO………，問題就這樣被發(fā)現(xiàn)了。

總結(jié)一下，就是說，某個(gè)query使用非聚集索引來select數(shù)據(jù)，那么它會在非聚集索引上持有一個(gè)S鎖。當(dāng)有一些select的列不在該索引上，它需要根據(jù)rowid找到對應(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ā)生鳥。

那么，為什么我們增加了一個(gè)非聚集索引，死鎖就消失鳥？我們看一下，按照上文中自動增加的索引之后的執(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)

哦，對于clustered index的需求沒有了，因?yàn)樵黾拥母采w索引已經(jīng)足夠把所有的信息都select出來。就這么簡單。

實(shí)際上，在sqlserver 2005中，如果用profiler來抓eventid：1222，那么會出現(xiàn)一個(gè)死鎖的圖，很直觀的說。

下面的方法，有助于將死鎖減至最少（詳細(xì)情況，請看SQLServer聯(lián)機(jī)幫助，搜索：將死鎖減至最少即可。

按同一順序訪問對象。

避免事務(wù)中的用戶交互。

保持事務(wù)簡短并處于一個(gè)批處理中。

使用較低的隔離級別。

使用基于行版本控制的隔離級別。

將 READ_COMMITTED_SNAPSHOT 數(shù)據(jù)庫選項(xiàng)設(shè)置為 ON，使得已提交讀事務(wù)使用行版本控制。

使用快照隔離。

使用綁定連接。

mongod可以代替sqlserver嗎

可以，并且有這些優(yōu)勢

1、使用JSON風(fēng)格語法，易于掌握和理解：MongoDB使用JSON的變種BSON作為內(nèi)部存儲的格式和語法。針對MongoDB的操作都使用JSON風(fēng)格語法，客戶端提交或接收的數(shù)據(jù)都使用JSON形式來展現(xiàn)。相對于SQL來說，更加直觀，容易理解和掌握。這也是根據(jù)我自己項(xiàng)目的情況出發(fā)，最后選擇了mongodb的一個(gè)原因。

2、Schema-less，支持嵌入子文檔：MongoDB是一個(gè)Schema-free的文檔數(shù)據(jù)庫。一個(gè)數(shù)據(jù)庫可以有多個(gè)Collection，每個(gè)Collection是Documents的集合。Collection和Document和傳統(tǒng)數(shù)據(jù)庫的Table和Row并不對等。無需事先定義Collection，隨時(shí)可以創(chuàng)建。Collection中可以包含具有不同schema的文檔記錄。 這意味著，你上一條記錄中的文檔有3個(gè)屬性，而下一條記錄的文檔可以有10個(gè)屬性，屬性的類型既可以是基本的數(shù)據(jù)類型（如數(shù)字、字符串、日期等），也可以是數(shù)組或者散列，甚至還可以是一個(gè)子文檔（embed document）。這樣，可以實(shí)現(xiàn)逆規(guī)范化（denormalizing）的數(shù)據(jù)模型，提高查詢的速度。

3、簡單易用的查詢方式：直接使用JSON，支持范圍查詢、正則表達(dá)式查詢。

4、CRUD更加簡單，支持in-place update：只要定義一個(gè)數(shù)組，然后傳遞給MongoDB的insert/update方法就可自動插入或更新；對于更新模式，MongoDB支持一個(gè)upsert選項(xiàng)，即：“如果記錄存在那么更新，否則插入”。MongoDB的update方法還支持Modifier，通過Modifier可實(shí)現(xiàn)在服務(wù)端即時(shí)更新，省去客戶端和服務(wù)端的通訊。這些modifer可以讓MongoDB具有和Redis、Memcached等KV類似的功能：較之MySQL，MonoDB更加簡單快速。Modifier也是MongoDB可以作為對用戶行為跟蹤的容器。在實(shí)際中使用Modifier來將用戶的交互行為快速保存到MongoDB中以便后期進(jìn)行統(tǒng)計(jì)分析和個(gè)性化定制

5、所有的屬性類型都支持索引，甚至數(shù)組：這可以讓某些任務(wù)實(shí)現(xiàn)起來非常的輕松。在MongoDB中，“_id”屬性是主鍵，默認(rèn)MongoDB會對_id創(chuàng)建一個(gè)唯一索引。

6、性能高效，速度快： MongoDB使用c++/boost編寫，在多數(shù)場合，其查詢速度對比MySQL要快的多，對于CPU占用非常小。部署也很簡單，對大多數(shù)系統(tǒng)，只需下載后二進(jìn)制包解壓就可以直接運(yùn)行，幾乎是零配置。

7、服務(wù)端腳本和Map/Reduce：MongoDB允許在服務(wù)端執(zhí)行腳本，可以用Javascript編寫某個(gè)函數(shù)，直接在服務(wù)端執(zhí)行，也可以把函數(shù)的定義存儲在服務(wù)端，下次直接調(diào)用即可。MongoDB不支持事務(wù)級別的鎖定，對于某些需要自定義的“原子性”操作，可以使用Server side腳本來實(shí)現(xiàn)，此時(shí)整個(gè)MongoDB處于鎖定狀態(tài)。Map/Reduce也是MongoDB中比較吸引人的特性。Map/Reduce可以對大數(shù)據(jù)量的表進(jìn)行統(tǒng)計(jì)、分類、合并的工作，完成原先SQL的GroupBy等聚合函數(shù)的功能。并且Mapper和Reducer的定義都是用Javascript來定義服務(wù)端腳本。

sql server2000高手看下

sql server 的鎖是自動管理的，事務(wù)是最小的原子單位，即是在begin train

commit train/rollback train之間只做一次提交，結(jié)果是要么全部執(zhí)行成功要么全部執(zhí)行失敗

在另一個(gè)用戶里執(zhí)行insert 語句的時(shí)候很快 不用等這20秒是insert 語句的時(shí)候沒有調(diào)用這個(gè)事務(wù)，你將這個(gè)事務(wù)放到查詢分析器執(zhí)行是要20秒的

樓主，我建議是你看下sql server鎖機(jī)制，因?yàn)槟鉯nsert的記錄是新的跟color='白' 沒有關(guān)系，如果你在這個(gè)時(shí)候再去update Mobbile set num=num+5 where color='白' 肯定是需要等待的

在用戶有sql請求時(shí)，系統(tǒng)分析請求，自動在滿足鎖定條件和系統(tǒng)性能之間為數(shù)據(jù)庫加上適當(dāng)?shù)逆i，同時(shí)系統(tǒng)在運(yùn)行期間常常自動進(jìn)行優(yōu)化處理，實(shí)行動態(tài)加鎖。對于一般的用戶而言，通過系統(tǒng)的自動鎖定管理機(jī)制基本可以滿足使用要求，但如果對數(shù)據(jù)安全、數(shù)據(jù)庫完整性和一致性有特殊要求，就需要了解sql server的鎖機(jī)制，掌握數(shù)據(jù)庫鎖定方法