建站
咨詢
售后
建站咨詢
新聞中心
sqlserver怎么清除死鎖
1、首先需要判斷是哪個用戶鎖住了哪張表.
公司主營業(yè)務(wù)：成都做網(wǎng)站、網(wǎng)站制作、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)建站是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)建站推出甌海免費(fèi)做網(wǎng)站回饋大家。
查詢被鎖表
select?request_session_id?spid,OBJECT_NAME(resource_associated_entity_id)?tableName?
from?sys.dm_tran_locks?where?resource_type='OBJECT'
查詢后會返回一個包含spid和tableName列的表.
其中spid是進(jìn)程名,tableName是表名.
2.了解到了究竟是哪個進(jìn)程鎖了哪張表后,需要通過進(jìn)程找到鎖表的主機(jī).
查詢主機(jī)名
exec?sp_who2?'xxx'
xxx就是spid列的進(jìn)程,檢索后會列出很多信息,其中就包含主機(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:有些時候強(qiáng)行殺掉進(jìn)程是比較危險(xiǎn)的,所以最好可以找到執(zhí)行進(jìn)程的主機(jī),在該機(jī)器上關(guān)閉進(jìn)程.
sqlserver鎖表機(jī)制
這個問題要具體分析：
第一，事務(wù)隔離級別基本兩種模式，一種是阻塞式（read committed，repeatable read，serializable）
，一種是非阻塞式(read uncommitted,snapshot)。
默認(rèn)是read committed，這種情況一般在更新表的時候，如果不使用hint 提示，基本是先對表添加IX鎖，級別不算高，基本和其他鎖兼容，但是repeatable read，serializable 事務(wù)隔離級別就會先對表添加IX鎖，然后向X鎖轉(zhuǎn)化，而X鎖和大多數(shù)鎖都不兼容，容易發(fā)生表阻塞。
第二種隔離級別不會有以上問題，但是又引入了其它的問題。
以上是一種情況。
另外一種就是 鎖升級，一個鎖是96B內(nèi)存，如果太多，sqlserver就會升級為表鎖，一般是5000以上行級鎖就升級為一個表X鎖。
所以適當(dāng)?shù)奈募纸M和表分區(qū) 是有必要的。
其次就是資源互相引用導(dǎo)致事務(wù)長時間不能釋放，導(dǎo)致真正的死鎖，不過SQL2005以后，這種情況發(fā)生的概率很低。
留個問題你自己去想。
兩個SQL，兩個連接，同時執(zhí)行。
update A set A.NAME=xxx where A.id=55
update A set A.NAME=xxx where A.id=56, 如果 56 不存在你說會發(fā)生什么情況呢？
如何處理SQL Server死鎖問題
死鎖，簡而言之，兩個或者多個trans，同時請求對方正在請求的某個對象，導(dǎo)致雙方互相等待。簡單的例子如下：
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í)行，這樣雙方開始死鎖。
好，我們看一個簡單的例子，來解釋一下，應(yīng)該如何解決死鎖問題。
-- Batch #1
CREATE DATABASE deadlocktest
GO
USE deadlocktest
SET NOCOUNT ON
DBCC TRACEON (1222, -1)
-- 在SQL2005中，增加了一個新的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)建一個deadlock的示范數(shù)據(jù)庫，插入了1000條數(shù)據(jù)，并在表t1上建立了c1列的聚集索引，和c2列的非聚集索引。另外創(chuàng)建了兩個sp，分別是從t1中select數(shù)據(jù)和update數(shù)據(jù)。
好，打開一個新的查詢窗口，我們開始執(zhí)行下面的query：
-- Batch #2
USE deadlocktest
SET NOCOUNT ON
WHILE (1=1) EXEC p2 4
GO
開始執(zhí)行后，然后我們打開第三個查詢窗口，執(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í)行，哈哈，很快，我們看到了這樣的錯誤信息：
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有三個選擇：負(fù)載文件、表、查詢語句，因?yàn)槲覀冞x擇了查詢語句的方式，所以就不需要修改這個radio option了。
3.點(diǎn)左上角的Start Analysis按鈕
4.抽根煙，回來后看結(jié)果吧！出現(xiàn)了一個分析結(jié)果窗口，其中，在Index Recommendations中，我們發(fā)現(xiàn)了一條信息：大意是，在表t1上增加一個非聚集索引索引：t2+t1。
5.在當(dāng)前窗口的上方菜單上，選擇Action菜單，選擇Apply Recommendations，系統(tǒng)會自動創(chuàng)建這個索引。
重新運(yùn)行batch #3，呵呵，死鎖沒有了。
這種方式，我們可以解決大部分的Sql Server死鎖問題。那么，發(fā)生這個死鎖的根本原因是什么呢？為什么增加一個non clustered index，問題就解決了呢？ 這次，我們分析一下，為什么會死鎖呢？再回顧一下兩個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，只是一個select，p2才是update。這個和我們前面說過的，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)
我們看到了一個nested loops，第一行，利用索引t1.c2來進(jìn)行seek，seek出來的那個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的時候，它會申請一個針對clustered index的X鎖的。
實(shí)際上到這里，我們就明白了為什么update會對select產(chǎn)生死鎖。update的時候，會申請一個針對clustered index的X鎖，這樣就阻塞住了（注意，不是死鎖！）select里面最后的那個clustered index seek。死鎖的另一半在哪里呢？注意我們的select語句，c2存在于索引idx1中，c1是一個聚集索引cidx。問題就在這里！我們在p2中更新了c2這個值，所以sqlserver會自動更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我們剛才的select語句中。而對這個索引列的更改，意味著索引集合的某個行或者某些行，需要重新排列，而重新排列，需要一個X鎖。
SO………，問題就這樣被發(fā)現(xiàn)了。
總結(jié)一下，就是說，某個query使用非聚集索引來select數(shù)據(jù)，那么它會在非聚集索引上持有一個S鎖。當(dāng)有一些select的列不在該索引上，它需要根據(jù)rowid找到對應(yīng)的聚集索引的那行，然后找到其他數(shù)據(jù)。而此時，第二個的查詢中，update正在聚集索引上忙乎：定位、加鎖、修改等。但因?yàn)檎谛薷牡哪硞€列，是另外一個非聚集索引的某個列，所以此時，它需要同時更改那個非聚集索引的信息，這就需要在那個非聚集索引上，加第二個X鎖。select開始等待update的X鎖，update開始等待select的S鎖，死鎖，就這樣發(fā)生鳥。
那么，為什么我們增加了一個非聚集索引，死鎖就消失鳥？我們看一下，按照上文中自動增加的索引之后的執(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)一個死鎖的圖，很直觀的說。
下面的方法，有助于將死鎖減至最少（詳細(xì)情況，請看SQLServer聯(lián)機(jī)幫助，搜索：將死鎖減至最少即可。
按同一順序訪問對象。
避免事務(wù)中的用戶交互。
保持事務(wù)簡短并處于一個批處理中。
使用較低的隔離級別。
使用基于行版本控制的隔離級別。
將 READ_COMMITTED_SNAPSHOT 數(shù)據(jù)庫選項(xiàng)設(shè)置為 ON，使得已提交讀事務(wù)使用行版本控制。
使用快照隔離。
使用綁定連接。
如何掌握SQLServer的鎖機(jī)制
SQL SERVER里的鎖機(jī)制：
NOLOCK（不加鎖）
此選項(xiàng)被選中時，SQL Server 在讀取或修改數(shù)據(jù)時不加任何鎖。 在這種情況下，用戶有可能讀取到未完成事務(wù)（Uncommited Transaction）或回滾(Roll Back)中的數(shù)據(jù), 即所謂的“臟數(shù)據(jù)”。
HOLDLOCK（保持鎖）
此選項(xiàng)被選中時，SQL Server 會將此共享鎖保持至整個事務(wù)結(jié)束，而不會在途中釋放。 例如，“ SELECT * FROM my_table HOLDLOCK”就要求在整個查詢過程中，保持對表的鎖定，直到查詢完成才釋放鎖定。
UPDLOCK（修改鎖）
此選項(xiàng)被選中時，SQL Server 在讀取數(shù)據(jù)時使用修改鎖來代替共享鎖，并將此鎖保持至整個事務(wù)或命令結(jié)束。使用此選項(xiàng)能夠保證多個進(jìn)程能同時讀取數(shù)據(jù)但只有該進(jìn)程能修改數(shù)據(jù)。
TABLOCK（表鎖）
此選項(xiàng)被選中時，SQL Server 將在整個表上置共享鎖直至該命令結(jié)束。 這個選項(xiàng)保證其他進(jìn)程只能讀取而不能修改數(shù)據(jù)。
PAGLOCK（頁鎖）
此選項(xiàng)為默認(rèn)選項(xiàng)， 當(dāng)被選中時，SQL Server 使用共享頁鎖。
TABLOCKX（排它表鎖）
此選項(xiàng)被選中時，SQL Server 將在整個表上置排它鎖直至該命令或事務(wù)結(jié)束。這將防止其他進(jìn)程讀取或修改表中的數(shù)據(jù)。
關(guān)于Sqlserver數(shù)據(jù)庫 鎖機(jī)制的小疑問，下種情況是否需要加入鎖機(jī)制
不需要，就算確實(shí)用戶同時執(zhí)行，數(shù)據(jù)庫的操作機(jī)制是有隊(duì)列的，所以不存在并發(fā)情況。
鎖基本用不到，我反正開發(fā)了5年了沒用到過。
你要了解死鎖發(fā)生的情況，一般是用事務(wù)的時候可能會碰到死鎖，你申請了A資源，鎖住了A然后申請B資源，其他人申請了B資源，然后申請A，這樣就互不相讓，導(dǎo)致A,B資源都不可訪問了，不過其他數(shù)據(jù)我不知道，SQLSERVER發(fā)生這種死鎖不是一直鎖死的，過幾分鐘就會發(fā)現(xiàn)這個死鎖，把鎖釋放掉，2個事務(wù)都失敗。
分享名稱：sqlserver列鎖,sqlserver行鎖
鏈接地址：http://www.dlmjj.cn/article/dscgsdg.html
