Ⅰ 如何排查sql死鎖的錯誤
1. 開啟死鎖日誌輸出(deadlock trace)
DBCC TRACEON(1204,1222)
Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock.
Trace flag 1222 formats deadlock information, first by processes and then by resources.
開啟了上面的選項之後, SQL會輸出死鎖的細節信息到SQL Error Log中(默認位置Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n)
2. 開啟SQL Profiler.
Start SQL profiler
On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
Click the Events tab, only add Locks\Lock:deadlock and Locks\Lock:deadlock chain
Click the Data columns tab, add DatabaseID, IndexID, ObjectID
可以通過下面的語句把DatabaseID和ObjectID換成DatabaseName和ObjectName
SELECT database_name(DatabaseID)
SELECT object_name(ObjectID)
3. 使用下面的查詢語句來檢查那個進程被鎖住了.
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
從Blocked列中得到SPID
DBCC inputbuffer (SPID)
sp_who2
sp_lock2
Ⅱ 如何查看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查看死鎖的存儲過程的代碼示例的全部內容，本文我們就介紹到這里了，希望本次的介紹能夠對您有所收獲！
Ⅲ 怎樣查詢引起死鎖的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 )
Ⅳ sql server中怎樣查詢引起死鎖的sql語句
DECLARE@spidINT
DECLARE@blkINT
DECLARE@countINT
DECLARE@indexINT
DECLARE@lockTINYINT
SET@lock=0
CREATETABLE#temp_who_lock
(
idINTIDENTITY(1,1),
spidINT,
blkINT
)
--if@@error<>0return@@error
INSERTINTO#temp_who_lock
(spid,
blk)
SELECT0,
blocked
FROM(SELECT*
FROMmaster..sysprocesses
WHEREblocked>0)a
WHERENOTEXISTS(SELECT*
FROMmaster..sysprocesses
WHEREa.blocked=spid
ANDblocked>0)
UNION
SELECTspid,
blocked
FROMmaster..sysprocesses
WHEREblocked>0
--if@@error<>0return@@error
SELECT@count=Count(*),
@index=1
FROM#temp_who_lock
--select@count,@index
--if@@error<>0return@@error
IF@count=0
BEGIN
SELECT'沒有阻塞和死鎖信息'
--return0
END
WHILE@index<=@count
BEGIN
IFEXISTS(SELECT1
FROM#temp_who_locka
WHEREid>@index
ANDEXISTS(SELECT1
FROM#temp_who_lock
WHEREid<=@index
ANDa.blk=spid))
BEGIN
SET@lock=1
SELECT@spid=spid,
@blk=blk
FROM#temp_who_lock
WHEREid=@index
SELECT'引起資料庫死鎖的是:'+Cast(@spidASVARCHAR(10))+'進程號,其執行的SQL語法如下';
SELECT@spid,
@blkDBCCinputbuffer(@spid)
DBCCinputbuffer(@blk)
END
SET@index=@index+1
END
IF@lock=0
BEGIN
SET@index=1
WHILE@index<=@count
BEGIN
SELECT@spid=spid,
@blk=blk
FROM#temp_who_lock
WHEREid=@index
IF@spid=0
SELECT'引起阻塞的是:'+Cast(@blkASVARCHAR(10))+'進程號,其執行的SQL語法如下'
ELSE
SELECT'進程號SPID：'+Cast(@spidASVARCHAR(10))+'被'+'進程號SPID：'+Cast(@blkASVARCHAR(10))+'阻塞,其當前進程執行的SQL語法如下'
PRINT(LTRIM(@spid)+''+LTRIM(@blk));
if(@spid<>0)
BEGIN
DBCCinputbuffer(@spid)--
END
DBCCinputbuffer(@blk)--引起阻塞語句
SET@index=@index+1
END
END
DROPTABLE#temp_who_lock
--return0
--KILL54
Ⅳ sql server 2008查看錶哪個數據死鎖
1.查詢分析器執行 sp_lock 查看
2.右鍵伺服器-選擇「活動和監視器」，查看進程選項。注意「任務狀態」欄位。
3.右鍵服務名稱-選擇報表-標准報表-活動-所有正在阻塞的事務。祝你愉快，滿意請採納哦
Ⅵ sql server 2008死鎖與阻塞怎樣解決
MSDN：包含正在 SQL Server 實例上運行的進程的相關信息。這些進程可以是客戶端進程或系統進程。 視圖中主要的欄位： 1. Spid：Sql Servr 會話ID 2. Kpid：Windows 線程ID 3. Blocked：正在阻塞求情的會話 ID。如果此列為 Null，則標識請求未被阻塞 4. Waittype：當前連接的等待資源編號，標示是否等待資源，0 或 Null表示不需要等待任何資源 5. Waittime：當前等待時間，單位為毫秒，0 表示沒有等待 6. DBID：當前正由進程使用的資料庫ID 7. UID：執行命令的用戶ID 8. Login_time：客戶端進程登錄到伺服器的時間。 9. Last_batch：上次執行存儲過程或Execute語句的時間。對於系統進程，將存儲Sql Server 的啟動時間 10.Open_tran：進程的打開事務個數。如果有嵌套事務，就會大於1 11.Status：進程ID 狀態，dormant = 正在重置回話 ; running = 回話正在運行一個或多個批處理 ; background = 回話正在運行一個後台任務 ; rollback = 會話正在處理事務回滾 ; pending = 回話正在等待工作現成變為可用 ; runnable = 會話中的任務在等待獲取 Scheler 來運行的可執行隊列中 ; spinloop = 會話中的任務正在等待自旋鎖變為可用 ; suspended = 會話正在等待事件完成 12.Hostname：建立鏈接的客戶端工作站的名稱 13.Program_name：應用程序的名稱，就是 連接字元串中配的 Application Name 14.Hostprocess：建立連接的應用程序在客戶端工作站里的進程ID號 15.Cmd：當前正在執行的命令 16.Loginame：登錄名 應用實例： 1. 檢查資料庫是否發生阻塞 先查找哪個鏈接的 blocked 欄位不為0。如 SPID53的blocked 欄位不為0，而是 52。SPID 52 的 blocked 為0，就可以得出結論：此時有阻塞發生，53 被 52 阻塞住了。如果你發現一個連接的 blocked 欄位的值等於它自己，那說明這個連接正在做磁碟讀寫，它要等自己的 I/O 做完。 2. 查找鏈接在那個資料庫上 檢查 dbid 即可。得到 dbid，可以運行以下查詢得到資料庫的名字： Select name,dbid from master.sys.sysdatabases
Ⅶ 如何查找SQL2008死鎖進程及對應SQL
-- 查詢死鎖
select
request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from
sys.dm_tran_locks
where
resource_type='OBJECT'
--殺死死鎖進程
kill 354
創造死鎖條件
開兩個查詢窗口
BEGIN TRANSACTION--開始事務
update job set creator='00000' where id='001'
WAITFOR DELAY '02:00';
select * from job where id='001'
Ⅷ 對SQL Server 2008資料庫進行收縮時，會遇到死鎖現象，如何解決。
樓主 可以試試下面的方法：
清空日誌
DUMP TRANSACTION 庫名
WITH
NO_LOG
2.截斷事務日誌：
BACKUP LOG 資料庫名 WITH
NO_LOG
3.收縮資料庫文件
資料庫名--右擊--任務--收縮--文件
--文件類型選擇日誌--收縮操作選擇第二個 將文件收縮到0 ,確定就可以了
4. 也可以用SQL語句來完成
--收縮資料庫
DBCC SHRINKDATABASE(客戶資料)
--收縮指定數據文件,1是文件號,可以通過這個語句查詢到:select * from
sysfiles
DBCC SHRINKFILE(1)收縮的時候把恢復模式改為簡單 否則收縮不了
希望解決了樓主的問題
Ⅸ MSSQLSERVER中怎樣查詢引起死鎖的sql語句
當死鎖發生後，通過服務端的Trace就可以將死鎖信息傳到日誌。在SQL Server 2000時代，只能通過Trace flag 1204來開啟，由於Trace flag 1204並不能提供XML死鎖圖，在SQL Server 2005以及之後的版本被Trace flag 1222所取代。
為了在服務端針對所有的Session開啟Trace flag 1222。可以通過如代碼所示。
DBCC TRACEON(1222,-1)
另一種方法是開啟Profiler來捕捉,Profiler捕捉到的圖示死鎖信息內容就更直觀了，
Ⅹ 用sql語句，怎麼解決mysql資料庫死鎖
MySQL死鎖問題的相關知識是本文我們主要要介紹的內容，接下來我們就來一一介紹這部分內容，希望能夠對您有所幫助。
1、MySQL常用存儲引擎的鎖機制
MyISAM和MEMORY採用表級鎖(table-level locking)
BDB採用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖
InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖
2、各種鎖特點
表級鎖：開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖沖突的概率最高，並發度最低
行級鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖沖突的概率最低，並發度也最高
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，並發度一般
3、各種鎖的適用場景
表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用
行級鎖則更適合於有大量按索引條件並發更新數據，同時又有並發查詢的應用，如一些在線事務處理系統
4、死鎖
是指兩個或兩個以上的進程在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。
表級鎖不會產生死鎖。所以解決死鎖主要還是針對於最常用的InnoDB。
5、死鎖舉例分析
在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。
在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。
例如，一個表db。tab_test，結構如下：
id：主鍵;
state：狀態;
time：時間;
索引：idx_1(state，time)
出現死鎖日誌如下：
?***(1) TRANSACTION:
?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read
?mysql tables in use 1, locked 1
?LOCK WAIT 3 lock struct(s), heap size 320
?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
?update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任務1的sql語句)
?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務1等待的索引記錄)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) TRANSACTION:
?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
?mysql tables in use 1, locked 1
?3 lock struct(s), heap size 320, undo log entries 1
?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務2的sql語句)
?*** (2) HOLDS THE LOCK(S): (任務2已獲得的鎖)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務2等待的鎖)
?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting
?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
?*** WE ROLL BACK TRANSACTION (1)
?(回滾了任務1，以解除死鎖)
原因分析：
當「update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)」執行時，MySQL會使用idx_1索引，因此首先鎖定相關的索引記錄，因為idx_1是非主鍵索引，為執行該語句，MySQL還會鎖定主鍵索引。
假設「update tab_test set state=1067,time=now () where id in (9921180)」幾乎同時執行時，本語句首先鎖定主鍵索引，由於需要更新state的值，所以還需要鎖定idx_1的某些索引記錄。
這樣第一條語句鎖定了idx_1的記錄，等待主鍵索引，而第二條語句則鎖定了主鍵索引記錄，而等待idx_1的記錄，這樣死鎖就產生了。
6、解決辦法
拆分第一條sql，先查出符合條件的主鍵值，再按照主鍵更新記錄：
?select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);
?update tab_test state=1064,time=now() where id in(......);