处理死锁
sp_who active --看看哪个引起的阻塞，blk
sp_lock --看看锁住了那个资源id，objid ，select object_name(objid) 得到
dbcc inputbuffer(@blk) -- 看看是那个语句
/*--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
--邹建 2004.4--*/
/*--调用示例
exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程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,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
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 master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go
在SQL Server 2005中解决死锁问题
作者：佚名 2007-05-29
数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。
将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。
use Northwind
begin tran
insert into Orders(CustomerId) values(@#ALFKI@#)
waitfor delay @#00:00:05@#
select * from Orders where CustomerId = @#ALFKI@#
commit
print @#end tran@#
SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print @#end tran@#语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。
现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径：
下面利用的try
catch来解决死锁。
SET XACT_ABORT ON
declare @r int
set @r = 1
while @r <= 3
begin
begin tran
begin try
insert into Orders(CustomerId) values(@#ALFKI@#)
waitfor delay @#00:00:05@#
select * from Orders where CustomerId = @#ALFKI@#
commit
break
end try
begin catch
rollback
waitfor delay @#00:00:03@#
set @r = @r + 1
continue
end catch
end
解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。
但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样:
declare @r int
set @r = 1
while @r <= 3
begin
begin tran
begin try
insert into Orders(CustomerId) values(@#ALFKI@#)
waitfor delay @#00:00:05@#
select * from Orders where CustomerId = @#ALFKI@#
commit
break
end try
begin catch
rollback
waitfor delay @#00:00:03@#
set @r = @r + 1
continue
end catch
end
if ERROR_NUMBER() <> 0
begin
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
declare @ErrorState int;
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror (@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
end
我希望将来SQL Server 2005能够直接抛出原有异常，比如提供一个无参数的RaiseError。
因此方案有点臃肿，但将死锁问题封装到T-SQL中有助于明确职责，提高高层系统的清晰度。现在，对于DataAccess的代码，或许再也不需要考虑死锁问题了。
-- ===========================================
-- 获取阻塞的 session_id 及阻塞时间
DECLARE @tb_block TABLE(
top_blocking_session_id smallint,
session_id smallint,
blocking_session_id smallint,
wait_time int,
Level int,
blocking_path varchar(8000),
PRIMARY KEY(
session_id, blocking_session_id)
)
INSERT @tb_block(
session_id,
blocking_session_id,
wait_time)
SELECT
session_id,
blocking_session_id,
wait_time = MAX(wait_time)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GROUP BY session_id, blocking_session_id
-- ===========================================
-- 处理阻塞的 session_id 之间的关系
DECLARE
@Level int
SET @Level = 1
INSERT @tb_block(
session_id, top_blocking_session_id, blocking_session_id,
Level, blocking_path)
SELECT DISTINCT
blocking_session_id, blocking_session_id, 0,
@Level, RIGHT(100000 + blocking_session_id, 5)
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE session_id = A.blocking_session_id)
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
UPDATE A SET
top_blocking_session_id = B.top_blocking_session_id,
Level = @Level,
blocking_path = B.blocking_path
+ RIGHT(100000 + A.session_id, 5)
FROM @tb_block A, @tb_block B
WHERE A.blocking_session_id = B.session_id
AND B.Level = @Level - 1
END
-- ===========================================
-- 如果只要显示阻塞时间超过多少毫秒的记录，可以在这里做一个过滤
-- 这里假设阻塞时间必须超过 1 秒钟(1000毫秒)
DELETE A
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE top_blocking_session_id =A.top_blocking_session_id
AND wait_time >= 1000)
-- ===========================================
-- 使用 DBCC INPUTBUFFER 获取阻塞进程的 T-SQL 脚本
DECLARE @tb_block_sql TABLE(
id int IDENTITY,
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000),
session_id smallint)
DECLARE
@session_id smallint
DECLARE tb CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT DISTINCT
session_id
FROM @tb_block
OPEN tb
FETCH tb INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @tb_block_sql(
EventType, Parameters, EventInfo)
EXEC(N'DBCC INPUTBUFFER(' + @session_id + ') WITH NO_INFOMSGS')
IF @@ROWCOUNT > 0
UPDATE @tb_block_sql SET
session_id = @session_id
WHERE IDENTITYCOL = @@IDENTITY
FETCH tb INTO @session_id
END
CLOSE tb
DEALLOCATE tb
-- ===========================================
-- 显示阻塞进程信息
;WITH
BLK AS(
SELECT
A.top_blocking_session_id,
A.session_id,
A.blocking_session_id,
A.Level,
A.blocking_path,
SQL = B.EventInfo
FROM @tb_block A
LEFT JOIN @tb_block_sql B
ON A.session_id = B.session_id
)
SELECT
-- BlockPath = REPLICATE(' ', Level * 2 - 2)
-- + '|-- '
-- + RTRIM(session_id),
BLK.top_blocking_session_id,
BLK.session_id,
BLK.blocking_session_id,
BLK.Level,
wait_type = P.waittype,
wait_time = P.waittime,
last_wait_type = P.lastwaittype,
wait_resource = P.waitresource,
P.login_time,
P.last_batch,
P.open_tran,
P.status,
host_name = P.hostname,
P.program_name,
P.cmd,
login_name = P.loginame,
BLK.SQL,
current_sql = T.text,
current_run_sql = SUBSTRING(T.text,
P.stmt_start / 2 + 1,
CASE
WHEN P.stmt_end = -1 THEN LEN(T.text)
ELSE (P.stmt_end - P.stmt_start) / 2+1
END)
FROM BLK
-- 简省代码起见，直接引用 sysprocess, 读者可以改为引用前述介绍的“查询进程"的脚本进行替换
INNER JOIN master.dbo.sysprocesses P
ON BLK.session_id = P.spid
OUTER APPLY sys.dm_exec_sql_text(P.sql_handle) T
ORDER BY BLK.top_blocking_session_id, BLK.blocking_path
sp_lock --看看锁住了那个资源id，objid ，select object_name(objid) 得到
dbcc inputbuffer(@blk) -- 看看是那个语句
/*--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
--邹建 2004.4--*/
/*--调用示例
exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程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,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
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 master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go
在SQL Server 2005中解决死锁问题
作者：佚名 2007-05-29
数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。
将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。
use Northwind
begin tran
insert into Orders(CustomerId) values(@#ALFKI@#)
waitfor delay @#00:00:05@#
select * from Orders where CustomerId = @#ALFKI@#
commit
print @#end tran@#
SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print @#end tran@#语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。
现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径：
下面利用的try
catch来解决死锁。 SET XACT_ABORT ON
declare @r int
set @r = 1
while @r <= 3
begin
begin tran
begin try
insert into Orders(CustomerId) values(@#ALFKI@#)
waitfor delay @#00:00:05@#
select * from Orders where CustomerId = @#ALFKI@#
commit
break
end try
begin catch
rollback
waitfor delay @#00:00:03@#
set @r = @r + 1
continue
end catch
end
解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。
但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样:
declare @r int
set @r = 1
while @r <= 3
begin
begin tran
begin try
insert into Orders(CustomerId) values(@#ALFKI@#)
waitfor delay @#00:00:05@#
select * from Orders where CustomerId = @#ALFKI@#
commit
break
end try
begin catch
rollback
waitfor delay @#00:00:03@#
set @r = @r + 1
continue
end catch
end
if ERROR_NUMBER() <> 0
begin
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
declare @ErrorState int;
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror (@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
end
我希望将来SQL Server 2005能够直接抛出原有异常，比如提供一个无参数的RaiseError。
因此方案有点臃肿，但将死锁问题封装到T-SQL中有助于明确职责，提高高层系统的清晰度。现在，对于DataAccess的代码，或许再也不需要考虑死锁问题了。
-- ===========================================
-- 获取阻塞的 session_id 及阻塞时间
DECLARE @tb_block TABLE(
top_blocking_session_id smallint,
session_id smallint,
blocking_session_id smallint,
wait_time int,
Level int,
blocking_path varchar(8000),
PRIMARY KEY(
session_id, blocking_session_id)
)
INSERT @tb_block(
session_id,
blocking_session_id,
wait_time)
SELECT
session_id,
blocking_session_id,
wait_time = MAX(wait_time)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GROUP BY session_id, blocking_session_id
-- ===========================================
-- 处理阻塞的 session_id 之间的关系
DECLARE
@Level int
SET @Level = 1
INSERT @tb_block(
session_id, top_blocking_session_id, blocking_session_id,
Level, blocking_path)
SELECT DISTINCT
blocking_session_id, blocking_session_id, 0,
@Level, RIGHT(100000 + blocking_session_id, 5)
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE session_id = A.blocking_session_id)
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
UPDATE A SET
top_blocking_session_id = B.top_blocking_session_id,
Level = @Level,
blocking_path = B.blocking_path
+ RIGHT(100000 + A.session_id, 5)
FROM @tb_block A, @tb_block B
WHERE A.blocking_session_id = B.session_id
AND B.Level = @Level - 1
END
-- ===========================================
-- 如果只要显示阻塞时间超过多少毫秒的记录，可以在这里做一个过滤
-- 这里假设阻塞时间必须超过 1 秒钟(1000毫秒)
DELETE A
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE top_blocking_session_id =A.top_blocking_session_id
AND wait_time >= 1000)
-- ===========================================
-- 使用 DBCC INPUTBUFFER 获取阻塞进程的 T-SQL 脚本
DECLARE @tb_block_sql TABLE(
id int IDENTITY,
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000),
session_id smallint)
DECLARE
@session_id smallint
DECLARE tb CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT DISTINCT
session_id
FROM @tb_block
OPEN tb
FETCH tb INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @tb_block_sql(
EventType, Parameters, EventInfo)
EXEC(N'DBCC INPUTBUFFER(' + @session_id + ') WITH NO_INFOMSGS')
IF @@ROWCOUNT > 0
UPDATE @tb_block_sql SET
session_id = @session_id
WHERE IDENTITYCOL = @@IDENTITY
FETCH tb INTO @session_id
END
CLOSE tb
DEALLOCATE tb
-- ===========================================
-- 显示阻塞进程信息
;WITH
BLK AS(
SELECT
A.top_blocking_session_id,
A.session_id,
A.blocking_session_id,
A.Level,
A.blocking_path,
SQL = B.EventInfo
FROM @tb_block A
LEFT JOIN @tb_block_sql B
ON A.session_id = B.session_id
)
SELECT
-- BlockPath = REPLICATE(' ', Level * 2 - 2)
-- + '|-- '
-- + RTRIM(session_id),
BLK.top_blocking_session_id,
BLK.session_id,
BLK.blocking_session_id,
BLK.Level,
wait_type = P.waittype,
wait_time = P.waittime,
last_wait_type = P.lastwaittype,
wait_resource = P.waitresource,
P.login_time,
P.last_batch,
P.open_tran,
P.status,
host_name = P.hostname,
P.program_name,
P.cmd,
login_name = P.loginame,
BLK.SQL,
current_sql = T.text,
current_run_sql = SUBSTRING(T.text,
P.stmt_start / 2 + 1,
CASE
WHEN P.stmt_end = -1 THEN LEN(T.text)
ELSE (P.stmt_end - P.stmt_start) / 2+1
END)
FROM BLK
-- 简省代码起见，直接引用 sysprocess, 读者可以改为引用前述介绍的“查询进程"的脚本进行替换
INNER JOIN master.dbo.sysprocesses P
ON BLK.session_id = P.spid
OUTER APPLY sys.dm_exec_sql_text(P.sql_handle) T
ORDER BY BLK.top_blocking_session_id, BLK.blocking_path
浙公网安备 33010602011771号