sql死鎖查詢

1. 如何查看sql Server 2008的死鎖

在SQL Server 2008資料庫中，查看死鎖可以用存儲過程來實現，本文我們主要就介紹了SQL Server 2008查看死鎖的存儲過程的代碼示例，希望能夠對您有所幫助。
代碼示例如下：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcere') = 1) drop procere [dbo].[sp_who_lock] GO use master go create procere sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF @@ERROR<>0 RETURN @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到臨時表的記錄數 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0 select '現在沒有阻塞和死鎖信息' as message -- 循環開始 while @intCounter <= @intCountProperties begin -- 取第一條記錄 select @spidspid = spid,@blbl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select '引起資料庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '進程號,其執行的SQL語法如下' else select '進程號SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '進程號SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當前進程執行的SQL語法如下' DBCC INPUTBUFFER (@bl ) end -- 循環指針下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end

以上就是SQL Server 2008查看死鎖的存儲過程的代碼示例的全部內容，本文我們就介紹到這里了，希望本次的介紹能夠對您有所收獲！

2. 怎麼查看 sql server 2008 死鎖日誌

1.查詢分析器執行 sp_lock 查看

2.右鍵伺服器-選擇「活動和監視器」，查看進程選項。注意「任務狀態」欄位。

3.右鍵服務名稱-選擇報表-標准報表-活動-所有正在阻塞的事務。祝你愉快，滿意請採納哦

3. 如何查看SQL死鎖

其實所有的死鎖最深層的原因就是一個:資源競爭
表現一:
一個用戶A 訪問表A(鎖住了表A),然後又訪問表B
另一個用戶B 訪問表B(鎖住了表B),然後企圖訪問表A

這時用戶A由於用戶B已經鎖住表B，它必須等待用戶B釋放表B,才能繼續，好了他老人家就只好老老實實在這等了
同樣用戶B要等用戶A釋放表A才能繼續這就死鎖了
解決方法：
這種死鎖是由於你的程序的BUG產生的，除了調整你的程序的邏輯別無他法
仔細分析你程序的邏輯，
1：盡量避免同時鎖定兩個資源
2: 必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相含旁同的順序來鎖定資源.

表現二:
用談咐橡戶A讀一條紀錄，然後修改該條紀錄
這是用戶B修改該條紀錄
這里用戶A的事務里鎖的性質由共享鎖企圖上升到獨占鎖(for update),而用戶B里的獨占鎖由於A有共享鎖存在所以必須等A釋
放掉共享鎖，而A由於B的獨占鎖而無法上升的獨占鎖也就不可能釋放共享鎖，於是出現了死鎖。
這種死鎖比較隱蔽，但其實在稍大點的項目中經常發生。
解決方法:
讓用戶A的事務（即先讀後寫類型的操作),在簡物select 時就是用Update lock
語法如下：
select * from table1 with(updlock) where ....

4. sqlserver怎麼用sql查看具體那個表被鎖住了

詳細步驟如下：

1、點擊【新建查詢】按鈕，打開SQL命令編輯框，對資料庫表的操作以及維護都可以通過編輯SQL命令實現。

3、創建數據表的源代碼如下：

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)

5. 怎樣查詢引起死鎖的sql語句

elect 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select ' 引起數梁塵據庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + ' 進程號, 其執行的SQL 語法如下'
else
select ' 進程號塌型SPID ：'+ CAST(@spid AS VARCHAR(10))+ ' 被橡衫禪' + ' 進程號SPID ：'+ CAST(@bl AS VARCHAR(10)) +' 阻塞, 其當前進程執行的SQL 語法如下'
DBCC INPUTBUFFER (@bl )

6. SQLServer死鎖的解除方法

SQL Server死鎖使我們經差鍵常遇到的問題 下面就為您介紹如何查詢SQL Server死喊慶晌鎖 希望對您學習SQL Server死鎖方面能有所幫鄭鋒助

SQL Server死鎖的查詢方法

exec master dbo p_lockinfo 顯示死鎖的進程 不顯示正常的進程

exec master dbo p_lockinfo 殺死死鎖的進程 不顯示正常的進程

SQL Server死鎖的解除方法

Create proc p_lockinfo

@kill_lock_spid bit= 是否殺掉死鎖的進程 殺掉 僅顯示

@show_spid_if_nolock bit= 如果沒有死鎖的進程 是否顯示正常進程信息 顯示 不顯示

as

declare @count int @s nvarchar（ ） @i int

select id=identity（int ） 標志

進程ID=spid 線程ID=kpid 塊進程ID=blocked 資料庫ID=dbid

資料庫名=db_name（dbid） 用戶ID=uid 用戶名=loginame 累計CPU時間=cpu

登陸時間=login_time 打開事務數=open_tran 進程狀態=status

工作站名=hostname 應用程序名=program_name 工作站進程ID=hostprocess

域名=nt_domain 網卡地址=net_address

into #t from（

select 標志= 死鎖的進程

spid kpid a blocked dbid uid loginame cpu login_time open_tran

status hostname program_name hostprocess nt_domain net_address

s =a spid s =

from mastersysprocesses a join （

select blocked from mastersysprocesses group by blocked

）b on a spid=b blocked where a blocked=

union all

select |_犧牲品_>

spid kpid blocked dbid uid loginame cpu login_time open_tran

status hostname program_name hostprocess nt_domain net_address

s =blocked s =

from mastersysprocesses a where blocked<>

）a order by s s

select @count=@@rowcount @i=

if @count= and @show_spid_if_nolock=

begin

insert #t

select 標志= 正常的進程

spid kpid blocked dbid db_name（dbid） uid loginame cpu login_time

open_tran status hostname program_name hostprocess nt_domain net_address

from mastersysprocesses

set @count=@@rowcount

end

if @count>

begin

create table #t （id int identity（ ） a nvarchar（ ） b Int EventInfo nvarchar（ ））

if @kill_lock_spid=

begin

declare @spid varchar（ ） @標志 varchar（ ）

while @i<=@count

begin

select @spid=進程ID @標志=標志 from #t whereid=@i

insert #t exec（ dbcc inputbuffer（ +@spid+ ） ）

if @標志= 死鎖的進程 exec（ kill +@spid）

set @i=@i+

end

end

else

while @i<=@count

begin

select @s= dbcc inputbuffer（ +cast（進程ID as varchar）+ ） from #t whereid=@i

insert #t exec（@s）

set @i=@i+

end

select a * 進程的SQL語句=b EventInfo

from #t a join #t b on a id=b id

lishixin/Article/program/SQLServer/201311/22183

7. 如何查看SQL表死鎖

SELECT SPID=p.spid,
DBName = convert(CHAR(20),d.name),
ProgramName = program_name,
LoginName = convert(CHAR(20),l.name),
HostName = convert(CHAR(20),hostname),
Status = p.status,
BlockedBy = p.blocked,
LoginTime = login_time,
QUERY = CAST(t.TEXT AS VARCHAR(MAX))
FROM MASTER.dbo.sysprocesses p
INNER JOIN MASTER.dbo.sysdatabases d
ON p.dbid = d.dbid
INNER JOIN MASTER.dbo.syslogins l
ON p.sid = l.sid
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE p.blocked = 0
AND EXISTS (SELECT 1
FROM MASTER.dbo.sysprocesses p1
WHERE p1.blocked = p.spid)
SELECT SPID=p.spid,
DBName = convert(CHAR(20),d.name),
ProgramName = program_name,
LoginName = convert(CHAR(20),l.name),
HostName = convert(CHAR(20),hostname),
Status = p.status,
BlockedBy = p.blocked,
LoginTime = login_time,
QUERY = CAST(t.TEXT AS VARCHAR(MAX))
FROM MASTER.dbo.sysprocesses p
INNER JOIN MASTER.dbo.sysdatabases d
ON p.dbid = d.dbid
INNER JOIN MASTER.dbo.syslogins l
ON p.sid = l.sid
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE p.blocked = 0
AND EXISTS (SELECT 1
FROM MASTER.dbo.sysprocesses p1
WHERE p1.blocked = p.spid)
用這個查詢兩次，如果兩次都有某個語句就是了，可以用kill結束它

8. 如何查看SQL Server 2008的死鎖

為了查看死鎖信息，資料庫引擎提供了監視工具，分別為兩個跟蹤標志以及
SQL
Server
Profiler中的死鎖圖形事件。
跟蹤標志
1204
和跟蹤標志
1222
發生死鎖時，跟蹤標志
1204
和跟蹤標志
1222
會返回在
SQL
Server
錯誤日誌中捕獲的信息。跟蹤標志
1204
會報告由死鎖所涉及的每個節點設置格式的死鎖信息。跟蹤標志
1222
會設置死鎖信息的格式，順序為先按進程，然後按資源。可以同時啟用這兩個跟蹤標志，以獲取同一個死鎖事件的兩種表示形式。
SQL
Server
Profiler
中的
TraceEvent
Class:
LocksEvent
Name:
Deadlock
Graph
提供
一個XML
圖表.，你可以從中看出發生了什麼。

9. mysql 查看死鎖

第一步，查出已鎖的進程

查侍者看正在鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

``

查看等待鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

``

INNODB_TRX表主要是包含了正在InnoDB引擎中執行的所有事務的信息，包括賀談散waiting for a lock和禪氏running的事務

select * from information_schema.innodb_trx

``

第二步，kill進程

show engin innodb status; //最後一次死鎖信息及sql

show open tables where in_use > 0 //查看鎖表

10. 如何處理SQL Server死鎖問題

死鎖，簡而言之，兩個或者多個trans，同時請求對方正在請求的某個對象，導致雙方互相等待。簡單的例子如下：x0dx0a trans1 trans2x0dx0a ------------------------------------------------------------------------x0dx0a 1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransactionx0dx0a 2.update table A 2.update table Bx0dx0a 3.update table B 3.update table Ax0dx0a 4.IDBConnection.Commit 4.IDBConnection.Commit x0dx0a 那麼，很容易看到，如果trans1和trans2，分別到達了step3，那麼trans1會請求對於B的X鎖，trans2會請求對於A的X鎖，而二者的鎖在step2上已經被對方分別持有了。由於得不到鎖，後面的Commit無法執行，這樣雙方開始死鎖。x0dx0a 好，我們看一個簡單的例子，來解釋一下，應該如何解決死鎖問題。x0dx0a -- Batch #1x0dx0a CREATE DATABASE deadlocktestx0dx0a GOx0dx0a USE deadlocktestx0dx0a SET NOCOUNT ONx0dx0a DBCC TRACEON (1222, -1)x0dx0a -- 在SQL2005中，增加了一個新的dbcc參數，就是1222，原來在2000下，我們知道，可以執行dbcc x0dx0a --traceon(1204,3605,-1)看到所有的死鎖信息。SqlServer 2005中，對於1204進行了增強，這就是1222。x0dx0a GO x0dx0a x0dx0a IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1x0dx0a IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1x0dx0a IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2x0dx0a GOx0dx0a CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000)) x0dx0a GOx0dx0a DECLARE @x intx0dx0a SET @x = 1x0dx0a WHILE (@x <= 1000) BEGINx0dx0a INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)x0dx0a SET @x = @x + 1x0dx0a ENDx0dx0a GOx0dx0a CREATE CLUSTERED INDEX cidx ON t1 (c1)x0dx0a CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)x0dx0a GOx0dx0a CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1x0dx0a GOx0dx0a CREATE PROC p2 @p1 int ASx0dx0a UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1x0dx0a UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1x0dx0a GOx0dx0a 上述sql創建一個deadlock的示範資料庫，插入了1000條數據，並在表t1上建立了c1列的聚集索引，和c2列的非聚集索引。另外創建了兩個sp，分別是從t1中select數據和update數據。 x0dx0a 好，打開一個新的查詢窗口，我們開始執行下面的query：x0dx0a -- Batch #2x0dx0a USE deadlocktestx0dx0a SET NOCOUNT ONx0dx0a WHILE (1=1) EXEC p2 4x0dx0a GOx0dx0a 開始執行後，然後我們打開第三個查詢窗口，執行下面的query：x0dx0a -- Batch #3x0dx0a USE deadlocktestx0dx0a SET NOCOUNT ONx0dx0a CREATE TABLE #t1 (c2 int, c3 int)x0dx0a GOx0dx0a WHILE (1=1) BEGINx0dx0a INSERT INTO #t1 EXEC p1 4x0dx0a TRUNCATE TABLE #t1x0dx0a ENDx0dx0a GOx0dx0a 開始執行，哈哈，很快，我們看到了這樣的錯誤信息：x0dx0a Msg 1205, Level 13, State 51, Procere p1, Line 4x0dx0a Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.x0dx0a spid54發現了死鎖。 x0dx0a 那麼，我們該如何解決它？x0dx0a 在SqlServer 2005中，我們可以這么做：x0dx0a 1.在trans3的窗口中，選擇EXEC p1 4，然後right click，看到了菜單了嗎？選擇Analyse Query in Database Engine Tuning Advisor。x0dx0a 2.注意右面的窗口中，wordload有三個選擇：負載文件、表、查詢語句，因為我們選擇了查詢語句的方式，所以就不需要修改這個radio option了。x0dx0a 3.點左上角的Start Analysis按鈕x0dx0a 4.抽根煙，回來後看結果吧！出現了一個分析結果窗口，其中，在Index Recommendations中，我們發現了一條信息：大意是，在表t1上增加一個非聚集索引索引：t2+t1。x0dx0a 5.在當前窗口的上方菜單上，選擇Action菜單，選擇Apply Recommendations，系統會自動創建這個索引。x0dx0a 重新運行batch #3，呵呵，死鎖沒有了。x0dx0a 這種方式，我們可以解決大部分的Sql Server死鎖問題。那麼，發生這個死鎖的根本原因是什麼呢？為什麼增加一個non clustered index，問題就解決了呢？ 這次，我們分析一下，為什麼會死鎖呢？再回顧一下兩個sp的寫法：x0dx0a CREATE PROC p1 @p1 int AS x0dx0a SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1 x0dx0a GOx0dx0a CREATE PROC p2 @p1 int ASx0dx0a UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1x0dx0a UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1x0dx0a GOx0dx0a 很奇怪吧！p1沒有insert，沒有delete，沒有update，只是一個select，p2才是update。這個和我們前面說過的，trans1裡面updata A，update B；trans2裡面upate B，update A，根本不貼邊啊！x0dx0a 那麼，什麼導致了死鎖？x0dx0a 需要從事件日誌中，看sql的死鎖信息：x0dx0a Spid X is running this query (line 2 of proc [p1], inputbuffer 「? EXEC p1 4 ?」): x0dx0a SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1x0dx0a Spid Y is running this query (line 2 of proc [p2], inputbuffer 「EXEC p2 4」): x0dx0a UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1x0dx0a x0dx0a The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock. x0dx0a The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.x0dx0a 首先，我們看看p1的執行計劃。怎麼看呢？可以執行set statistics profile on，這句就可以了。下面是p1的執行計劃x0dx0a SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1x0dx0a |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))x0dx0a |--Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)x0dx0a |--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)x0dx0a 我們看到了一個nested loops，第一行，利用索引t1.c2來進行seek，seek出來的那個rowid，在第二行中，用來通過聚集索引來查找整行的數據。這是什麼？就是bookmark lookup啊！為什麼？因為我們需要的c2、c3不能完全的被索引t1.c1帶出來，所以需要書簽查找。 x0dx0a 好，我們接著看p2的執行計劃。x0dx0a UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1x0dx0a |--Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))x0dx0a |--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))x0dx0a |--Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN ...x0dx0a |--Top(ROWCOUNT est 0)x0dx0a |--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD) x0dx0a 通過聚集索引的seek找到了一行，然後開始更新。這里注意的是，update的時候，它會申請一個針對clustered index的X鎖的。x0dx0a 實際上到這里，我們就明白了為什麼update會對select產生死鎖。update的時候，會申請一個針對clustered index的X鎖，這樣就阻塞住了（注意，不是死鎖！）select裡面最後的那個clustered index seek。死鎖的另一半在哪裡呢？注意我們的select語句，c2存在於索引idx1中，c1是一個聚集索引cidx。問題就在這里！我們在p2中更新了c2這個值，所以sqlserver會自動更新包含c2列的非聚集索引：idx1。而idx1在哪裡？就在我們剛才的select語句中。而對這個索引列的更改，意味著索引集合的某個行或者某些行，需要重新排列，而重新排列，需要一個X鎖。x0dx0a SO???，問題就這樣被發現了。x0dx0a 總結一下，就是說，某個query使用非聚集索引來select數據，那麼它會在非聚集索引上持有一個S鎖。當有一些select的列不在該索引上，它需要根據rowid找到對應的聚集索引的那行，然後找到其他數據。而此時，第二個的查詢中，update正在聚集索引上忙乎：定位、加鎖、修改等。但因為正在修改的某個列，是另外一個非聚集索引的某個列，所以此時，它需要同時更改那個非聚集索引的信息，這就需要在那個非聚集索引上，加第二個X鎖。select開始等待update的X鎖，update開始等待select的S鎖，死鎖，就這樣發生鳥。 x0dx0a 那麼，為什麼我們增加了一個非聚集索引，死鎖就消失鳥？我們看一下，按照上文中自動增加的索引之後的執行計劃：x0dx0a SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1x0dx0a |--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)x0dx0a 哦，對於clustered index的需求沒有了，因為增加的覆蓋索引已經足夠把所有的信息都select出來。就這么簡單。x0dx0a 實際上，在sqlserver 2005中，如果用profiler來抓eventid：1222，那麼會出現一個死鎖的圖，很直觀的說。x0dx0a 下面的方法，有助於將死鎖減至最少（詳細情況，請看SQLServer聯機幫助，搜索：將死鎖減至最少即可。x0dx0a按同一順序訪問對象。 x0dx0a避免事務中的用戶交互。 x0dx0a保持事務簡短並處於一個批處理中。 x0dx0a使用較低的隔離級別。 x0dx0a使用基於行版本控制的隔離級別。 x0dx0a將 READ_COMMITTED_SNAPSHOT 資料庫選項設置為 ON，使得已提交讀事務使用行版本控制。 x0dx0a使用快照隔離。x0dx0a使用綁定連接。