本文目錄一覽：
如何查mysql死鎖進程
查詢死鎖進程
採用如下存儲過程來查詢數據中當前造成死鎖的進程。
drop procedure sp_who_lock
go
CREATE procedure sp_who_lock
as
begin
declare @spid int
declare @blk int
declare @count int
declare @index int
declare @lock tinyint
set @lock=0
create table #temp_who_lock
(
id int identity(1,1),
spid int,
blk int
)
if @@error0 return @@error
insert into #temp_who_lock(spid,blk)
select 0 ,blocked
from (select * from master..sysprocesses where blocked0)a
where not exists(select * from master..sysprocesses where a.blocked =spid and blocked0)
union select spid,blocked from master..sysprocesses where blocked0
if @@error0 return @@error
select @count=count(*),@index=1 from #temp_who_lock
if @@error0 return @@error
if @count=0
begin
select ‘沒有阻塞和死鎖信息’
return 0
end
while @indexA href=”mailto:=@count”=@count
begin
if exists(select 1 from #temp_who_lock a where id@index and exists(select 1 from #temp_who_lock where idA href=”mailto:=@index”=@index and a.blk=spid))
begin
set @lock=1
select @spid=spid,@blk=blk from #temp_who_lock where id=@index
select ‘引起數據庫死鎖的是: ‘+ CAST(@spid AS VARCHAR(10)) + ‘進程號,其執行的SQL語法如下’
select @spid, @blk
dbcc inputbuffer(@spid)
dbcc inputbuffer(@blk)
end
set @index=@index+1
end
if @lock=0
begin
set @index=1
while @indexA href=”mailto:=@count”=@count
begin
select @spid=spid,@blk=blk from #temp_who_lock where id=@index
if @spid=0
select ‘引起阻塞的是:’+cast(@blk as varchar(10))+ ‘進程號,其執行的SQL語法如下’
else
select ‘進程號SPID：’+ CAST(@spid AS VARCHAR(10))+ ‘被’ + ‘進程號SPID：’+ CAST(@blk AS VARCHAR(10)) +’阻塞,其當前進程執行的SQL語法如下’
dbcc inputbuffer(@spid)
dbcc inputbuffer(@blk)
set @index=@index+1
end
end
drop table #temp_who_lock
return 0
end
GO
–執行該存儲過程
exec sp_who_lock
補充：
一、產生死鎖的原因
在SQL Server中，阻塞更多的是產生於實現並發之間的隔離性。為了使得並發連接所做的操作之間的影響到達某一期望值而對資源人為的進行加鎖(鎖本質其實可以看作是一個標誌位)。當一個連接對特定的資源進行操作時，另一個連接同時對同樣的資源進行操作就會被阻塞，阻塞是死鎖產生的必要條件。
二、如何避免死鎖
1.使用事務時，盡量縮短事務的邏輯處理過程，及早提交或回滾事務；
2.設置死鎖超時參數為合理範圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進程懸掛；
3.優化程序，檢查並避免死鎖現象出現；
4.對所有的腳本和SP都要仔細測試，在正是版本之前；
5.所有的SP都要有錯誤處理（通過@error）；
6.一般不要修改SQL SERVER事務的默認級別。不推薦強行加鎖。
三、處理死鎖
1、最簡單的處理死鎖的方法就是重啟服務。
2、根據指定的死鎖進程ID進行處理
根據第二步查詢到的死鎖進行，大致分析造成死鎖的原因，並通過如下語句釋放該死鎖進程
kill pid –pid為查詢出來的死鎖進程號
3、通過存儲過程殺掉某個庫下面的所有死鎖進程和鎖
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_killspid]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[sp_killspid]
GO
create proc sp_killspid
@dbname varchar(200) –要關閉進程的數據庫名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec(‘kill ‘+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
–使用方法，「db_name」為處理的數據庫名稱
exec sp_killspid ‘db_name’
MySQL數據庫中查詢表是否被鎖以及解鎖
1.查看錶被鎖狀態
2.查看造成死鎖的sql語句
3.查詢進程
4.解鎖(刪除進程)
5.查看正在鎖的事物 (8.0以下版本)
6.查看等待鎖的事物 (8.0以下版本)
怎麼統計mysql中有多少個死鎖？
1，查看數據庫的隔離級別：
mysql select @@tx_isolation;
2，去查看先當前庫的線程情況：
mysql show processlist;
沒有看到正在執行的慢SQL記錄線程，再去查看innodb的事務表INNODB_TRX，看下裏面是否有正在鎖定的事務線程，看看ID是否在show full processlist裏面的sleep線程中，如果是，就證明這個sleep的線程事務一直沒有commit或者rollback而是卡住了，我們需要手動kill掉。
mysql SELECT * FROM information_schema.INNODB_TRX;
如果有記錄,則找到trx_mysql_thread_id這個字段對應的id, 將其kill掉。假如id=100
mysql-kill 100
SELECT CONCAT_WS(”,’kill’,’ ‘,t.trx_mysql_thread_id,’;’)a FROM information_schema.INNODB_TRX t;
4，總結分析
表數據量也不大，按照普通的情況來說，簡單的update應該不會造成阻塞的，mysql都是autocommit，不會出現update卡住的情況，去查看下autocommit的值。
mysql select @@autocommit;
1表示自動提交。0表示不自動提交。
如果你發現自己的數據庫autocommit=0，將它改正吧。
解除死鎖的兩種方法：
（1）終止（或撤銷）進程。終止（或撤銷）系統中的一個或多個死鎖進程，直至打破循環環路，使系統從死鎖狀態中解除出來。
（2）搶佔資源。從一個或多個進程中搶佔足夠數量的資源，分配給死鎖進程，以打破死鎖狀態。
如何查看MySQL數據庫的死鎖信息
方法/步驟
使用終端或命令提示符登錄到MySQL，輸入命令：
mysql -h xxxx.xxx.xxx -P 3306 -u username -p password
在MySQL客戶端下輸入命令：
show engine innodb status \G;
在打印出來的信息中找到「LATEST DETECTED DEADLOCK」一節內容
分析其中的內容，我們就可以知道最近導致死鎖的事務有哪些
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hk/n/304334.html
微信掃一掃 
支付寶掃一掃 