sqlServer查看死鎖及解鎖

--查看死鎖

作為一家“創(chuàng)意+整合+營銷”的成都網(wǎng)站建設(shè)機構(gòu)，我們在業(yè)內(nèi)良好的客戶口碑。創(chuàng)新互聯(lián)公司提供從前期的網(wǎng)站品牌分析策劃、網(wǎng)站設(shè)計、成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、創(chuàng)意表現(xiàn)、網(wǎng)頁制作、系統(tǒng)開發(fā)以及后續(xù)網(wǎng)站營銷運營等一系列服務，幫助企業(yè)打造創(chuàng)新的互聯(lián)網(wǎng)品牌經(jīng)營模式與有效的網(wǎng)絡(luò)營銷方法,創(chuàng)造更大的價值。

select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name?

from sysprocesses?

where spid in?

( select blocked from sysprocesses where blocked 0 ) or (blocked 0)?

--解鎖

kill 進程號（spid）

sqlserver怎么用sql查看具體那個表被鎖住了

詳細步驟如下：

1、點擊【新建查詢】按鈕，打開SQL命令編輯框，對數(shù)據(jù)庫表的操作以及維護都可以通過編輯SQL命令實現(xiàn)。

2、在編輯框內(nèi)編輯創(chuàng)建數(shù)據(jù)庫表的代碼，確認代碼無誤后，單擊【執(zhí)行】按鈕，創(chuàng)建數(shù)據(jù)表。

3、創(chuàng)建數(shù)據(jù)表的源代碼如下：

use test go

if exists(select name from sys.tables where name='Student')

drop table Student go

create table Student

(sname nchar(10) primary key,

sex nchar(2) not null,

bir datetime)

數(shù)據(jù)庫管理系統(tǒng)，database management system，簡稱dbms，是一種操縱和管理數(shù)據(jù)庫的大型軟件，用于建立、使用和維護數(shù)據(jù)庫。用戶通過dbms訪問數(shù)據(jù)庫中的數(shù)據(jù)，數(shù)據(jù)庫管理員也通過dbms進行數(shù)據(jù)庫的維護工作。它可使多個應用程序和用戶用不同的方法在同時或不同時刻去建立，修改和詢問數(shù)據(jù)庫。

提供數(shù)據(jù)定義語言（ddl）。用它書寫的數(shù)據(jù)庫模式被翻譯為內(nèi)部表示。數(shù)據(jù)庫的邏輯結(jié)構(gòu)、完整性約束和物理儲，存結(jié)構(gòu)保存在內(nèi)部的數(shù)據(jù)字典中。數(shù)據(jù)庫的各種數(shù)據(jù)操作（如查找、修改、插入和刪除等）和數(shù)據(jù)庫的維護管理都是以數(shù)據(jù)庫模式為依據(jù)的。

如何監(jiān)控sqlserver 性能 死鎖

具體步驟如下：

1.首先使用下面的命令，將有關(guān)的跟蹤標志啟用。

SQL codeDBCC TRACEON (3605,1204,1222,-1)

說明：

3605

將DBCC的結(jié)果輸出到錯誤日志。

1204 返回參與死鎖的鎖的資源和類型，以及受影響的當前命令。

1222

返回參與死鎖的鎖的資源和類型，以及使用了不符合任何 XSD 架構(gòu)的 XML 格式的受影響的當前命令(比1204更進一步,SQL

2005及以上可用)。

-1 以全局方式打開指定的跟蹤標記。

以上跟蹤標志作用域都是全局，即在SQL

Server運行過程中，會一直發(fā)揮作用，直到SQL Server重啟。

如 果要確保SQL Server在重啟后自動開啟這些標志，可以在SQL

Server服務啟動選項中，使用 /T 啟動選項指定跟蹤標志在啟動期

間設(shè)置為開。(位于SQL Server配置管理器-SQL

Server服務-SQL Server-屬性-高級-啟動參數(shù))

在運行上面的語句后，當SQL

Server中發(fā)生死鎖時，已經(jīng)可以在錯誤日志中看到了，但還不夠直觀(和其它信息混在一起)。（SSMS

- SQL Server實例 -

管理 - SQL Server日志）

2.建表，存放死鎖記錄

SQL codeUSE [Cole] --Cole是我的示例數(shù)據(jù)庫，你可以根據(jù)實際情況修改。 GO

CREATE TABLE DeadLockLog ( id int IDENTITY (1, 1) NOT NULL, LogDate DATETIME, ProcessInfo VARCHAR(10), ErrorText VARCHAR(MAX) )

GO

3.建立JOB

新建一個JOB（假設(shè)名稱為DeadLockJob）,在"步驟"中新建一步驟，隨便寫一個步驟名稱，數(shù)據(jù)庫為"Cole",在"命令"欄中輸入以下語句：

SQL code--新建臨時表 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)) --將當前日志記錄插入臨時表

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.新建警報

在"新建警報"窗體的"常規(guī)"選項卡中,進行以下設(shè)置：

名稱：可根據(jù)實際自行命名，這里我用DeadLockAlert

類型：選擇"SQL

Server性能條件警報"

對象：SQLServer:Locks

計數(shù)器：Number of

Deadlocks/sec

實例：_Total

計數(shù)器滿足以下條件時觸發(fā)警報：高于

值：0

在"響應"選項卡中，選中"執(zhí)行作業(yè)"，并選擇步驟3中我們新建的作業(yè)(即DeadlockJob)

到這里為止，我們已經(jīng)完成了全部步驟，以后，你就可以隨時查詢DeadLockLog表，來顯示死鎖信息了。

sqlserver 數(shù)據(jù)庫死鎖后多長時間解鎖

您好：

SQL SERVER 通過鎖管理器自動發(fā)現(xiàn)和解決死鎖。在 SQL SERVER 中 Lock Monitor 管理線程（spid=4）每 5 秒鐘檢查一次系統(tǒng)中是否存在死鎖，同時也會使用死鎖發(fā)現(xiàn)計數(shù)器（Deadlock Detection Counter）控制檢查死鎖的頻率。

死鎖發(fā)現(xiàn)計數(shù)器初始值為 3，當發(fā)現(xiàn)死鎖時被重新設(shè)置為 3，當沒有發(fā)現(xiàn)死鎖時此值減 1。如果死鎖發(fā)現(xiàn)計數(shù)器大于 0，則在每次有進程獲取鎖被阻止時，鎖管理器都要求 Lock Monitor 線程檢查死鎖；而如果計數(shù)器等于 0，則在每次有進程獲取鎖被阻止時，鎖管理器不會要求 Lock Monitor 線程檢查死鎖，只是每 5 秒鐘檢查一次。

Lock Monitor 線程通過檢查鎖的等待列表發(fā)現(xiàn)保持鎖的進程和等待鎖的進程間的關(guān)系，從而發(fā)現(xiàn)死鎖。

如何處理SQL Server死鎖問題

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

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，分別到達了step3，那么trans1會請求對于B的X鎖，trans2會請求對于A的X鎖，而二者的鎖在step2上已經(jīng)被對方分別持有了。由于得不到鎖，后面的Commit無法執(zhí)行，這樣雙方開始死鎖。

好，我們看一個簡單的例子，來解釋一下，應該如何解決死鎖問題。

-- 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進行了增強，這就是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有三個選擇：負載文件、表、查詢語句，因為我們選擇了查詢語句的方式，所以就不需要修改這個radio option了。

3.點左上角的Start Analysis按鈕

4.抽根煙，回來后看結(jié)果吧！出現(xiàn)了一個分析結(jié)果窗口，其中，在Index Recommendations中，我們發(fā)現(xiàn)了一條信息：大意是，在表t1上增加一個非聚集索引索引：t2+t1。

5.在當前窗口的上方菜單上，選擇Action菜單，選擇Apply Recommendations，系統(tǒng)會自動創(chuàng)建這個索引。

重新運行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，根本不貼邊啊！

那么，什么導致了死鎖？

需要從事件日志中，看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í)行計劃。怎么看呢？可以執(zhí)行set statistics profile on，這句就可以了。下面是p1的執(zhí)行計劃

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來進行seek，seek出來的那個rowid，在第二行中，用來通過聚集索引來查找整行的數(shù)據(jù)。這是什么？就是bookmark lookup啊！為什么？因為我們需要的c2、c3不能完全的被索引t1.c1帶出來，所以需要書簽查找。

好，我們接著看p2的執(zhí)行計劃。

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鎖的。

實際上到這里，我們就明白了為什么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鎖。當有一些select的列不在該索引上，它需要根據(jù)rowid找到對應的聚集索引的那行，然后找到其他數(shù)據(jù)。而此時，第二個的查詢中，update正在聚集索引上忙乎：定位、加鎖、修改等。但因為正在修改的某個列，是另外一個非聚集索引的某個列，所以此時，它需要同時更改那個非聚集索引的信息，這就需要在那個非聚集索引上，加第二個X鎖。select開始等待update的X鎖，update開始等待select的S鎖，死鎖，就這樣發(fā)生鳥。

那么，為什么我們增加了一個非聚集索引，死鎖就消失鳥？我們看一下，按照上文中自動增加的索引之后的執(zhí)行計劃：

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的需求沒有了，因為增加的覆蓋索引已經(jīng)足夠把所有的信息都select出來。就這么簡單。

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

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

按同一順序訪問對象。

避免事務中的用戶交互。

保持事務簡短并處于一個批處理中。

使用較低的隔離級別。

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

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

使用快照隔離。

使用綁定連接。

如何處理SQL Server死鎖問題？

死鎖，簡而言之，兩個或者多個trans，同時請求對方正在請求的某個對象，導致雙方互相等待。簡單的例子如下：\x0d\x0a trans1 trans2\x0d\x0a ------------------------------------------------------------------------\x0d\x0a 1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction\x0d\x0a 2.update table A 2.update table B\x0d\x0a 3.update table B 3.update table A\x0d\x0a 4.IDBConnection.Commit 4.IDBConnection.Commit \x0d\x0a 那么，很容易看到，如果trans1和trans2，分別到達了step3，那么trans1會請求對于B的X鎖，trans2會請求對于A的X鎖，而二者的鎖在step2上已經(jīng)被對方分別持有了。由于得不到鎖，后面的Commit無法執(zhí)行，這樣雙方開始死鎖。\x0d\x0a 好，我們看一個簡單的例子，來解釋一下，應該如何解決死鎖問題。\x0d\x0a -- Batch #1\x0d\x0a CREATE DATABASE deadlocktest\x0d\x0a GO\x0d\x0a USE deadlocktest\x0d\x0a SET NOCOUNT ON\x0d\x0a DBCC TRACEON (1222, -1)\x0d\x0a -- 在SQL2005中，增加了一個新的dbcc參數(shù)，就是1222，原來在2000下，我們知道，可以執(zhí)行dbcc \x0d\x0a --traceon(1204,3605,-1)看到所有的死鎖信息。SqlServer 2005中，對于1204進行了增強，這就是1222。\x0d\x0a GO \x0d\x0a \x0d\x0a IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1\x0d\x0a IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1\x0d\x0a IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2\x0d\x0a GO\x0d\x0a CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000)) \x0d\x0a GO\x0d\x0a DECLARE @x int\x0d\x0a SET @x = 1\x0d\x0a WHILE (@x = [@p1] AND [t1].[c2] = [@p1] AND [deadlocktest].[dbo].[t1].[c2]

回答于?2022-11-16

當前名稱：sqlserver表死鎖,sqlserver表死鎖原因
文章分享：http://chinadenli.net/article16/dsgpjgg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機網(wǎng)站建設(shè)、動態(tài)網(wǎng)站、電子商務、定制網(wǎng)站、、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)