这样处理正在使用数据库中的死锁问题会不会影响其性能？

[abcdwell]

正在使用中的程序操作数据库时出现如下错误：
Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

并且会导致处理数据出错，不知所措，
于是在网上找了这个方法，说可以查看数据库死锁原因和地方，
1）、非常担心这样会影响到数据库性能；
2）、如果在用户操作数据库时实时地打开Profiler来监控，会不会拖慢数据库反应速度？
3）、SQL2005的Profiler有没有办法过滤出死锁信息，也就是说，只监控死锁的相关操作？

如下：

--打开 trace flag
DBCC TRACEON (1222,-1)
--5）关闭trace flag可运行以下语句
--DBCC TRACEOFF (1222,-1)

--1）查看max degree of parallelism设置可运行以下语句：
sp_configure

--2）修改max degree of parallelism值为1可运行以下语句：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

--3）修改max degree of parallelism值为4可运行以下语句：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO

[abcdwell]

自己顶自己一次……

[Mr_Nice]

如果在用户操作数据库时实时地打开Profiler来监控，会不会拖慢数据库反应速度？

-- 会。 profiler 目前最好的应用是抓点。 实时监控，消耗资源比较大。

SQL2005的Profiler有没有办法过滤出死锁信息，也就是说，只监控死锁的相关操作？

-- 参考http://technet.microsoft.com/zh-cn/library/ms188246(SQL.90).aspx

[--小F--]

参考下:
虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务： 

回滚，而回滚会取消事务执行的所有工作。 

由于死锁时回滚而由应用程序重新提交。 


下列方法有助于最大限度地降低死锁： 

按同一顺序访问对象。 

避免事务中的用户交互。 

保持事务简短并在一个批处理中。 

使用低隔离级别。 

使用绑定连接。 


按同一顺序访问对象

如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。例如，如果两个并发事务获得 Supplier 表上的锁，然后获得 Part 表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在 Supplier 表上。第一个事务提交或回滚后，第二个事务继续进行。不发生死锁。将存储过程用于所有的数据修改可以标准化访问对象的顺序。

避免事务中的用户交互

避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，例如答复应用程序请求参数的提示。例如，如果事务正在等待用户输入，而用户去吃午餐了或者甚至回家过周末了，则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。

保持事务简短并在一个批处理中

在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。

保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。

使用低隔离级别

确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺。

使用绑定连接

使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。次级连接所获得的任何锁可以象由主连接获得的锁那样持有，反之亦然，因此不会相互阻塞

检测死锁

如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？

这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。


 

use master
go
create procedure 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  @spid = spid,@bl = 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


 

杀死锁和进程

如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。

use master
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_killspid]
GO

create proc p_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

--用法  
exec p_killspid  'newdbpy'


 

查看锁信息

如何查看系统中所有锁的详细信息？在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。

--查看锁信息
create table #t(req_spid int,obj_name sysname)

declare @s nvarchar(4000)
    ,@rid int,@dbname sysname,@id int,@objname sysname

declare tb cursor for 
    select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
    from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
    set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
    exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
    insert into #t values(@rid,@objname)
    fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb

select 进程id=a.req_spid
    ,数据库=db_name(rsc_dbid)
    ,类型=case rsc_type when 1 then 'NULL 资源（未使用）'
        when 2 then '数据库'
        when 3 then '文件'
        when 4 then '索引'
        when 5 then '表'
        when 6 then '页'
        when 7 then '键'
        when 8 then '扩展盘区'
        when 9 then 'RID（行 ID)'
        when 10 then '应用程序'
    end
    ,对象id=rsc_objid
    ,对象名=b.obj_name
    ,rsc_indid
 from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

go
drop table #t



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/12/24/5071970.aspx

[obuntu]

1，profiler可以只监控死锁的事件；
2，DBCC TRACEON (1222,-1)会在发生死锁时生成XML文件，你可以查看完整的死锁信息，对引起死锁的问题进行分析，然后再进行处理；
3，'max degree of parallelism'只是设置并行度，跟死锁一般没关系。在OLTP上，该值反而建议设置为1，也就是禁用并行；
4，运行profiler肯定会稍微影响到性能，特别是在资源已经很紧张的情况下。一般情况下，是可以大胆使用，但注意筛选少的事件进行跟踪。

[abcdwell]

[Quote=引用 4 楼 obuntu 的回复:]
1，profiler可以只监控死锁的事件；
2，DBCC TRACEON (1222,-1)会在发生死锁时生成XML文件，你可以查看完整的死锁信息，对引起死锁的问题进行分析，然后再进行处理；
3，'max degree of parallelism'只是设置并行度，跟死锁一般没关系。在OLTP上，该值反而建议设置为1，也就是禁用并行；
4，运行profiler肯定会稍微影响到性能，特别是在资源已经很紧张的情况下。一般情况下，是可以大胆使用，但注意筛选少的事件进行跟踪。[/Quote]
先感谢你的回复！
因为数据正在使用，一旦导致数据库不可以正常工作，可不得了，
所以操作比较慎重，想知道地详细些，如下：
1、如何监控？
2、生成的XML文件一般放在什么位置？
3、如果说设置上这项，也就是说禁止并行，会不会就可以彻底避免死锁？ 因为毕竟是少数，
4、资源是比较紧张，内存才1G，而且是DDR1，所以不太敢用，

[obuntu]

1222没用过，一般只用1204

你到安装目录下的log目找找看。

如果要查找死锁，一般打开trace flag就可以了。像1204的话，会把死锁信息写在ERRORLOG里，去看错误日志是否有deadlock encountered等信息。

虽然有些死锁会有并行引起，但不一定是并行就是引起死锁的原因，因此禁用并行是无法保证彻底避免死锁的，死锁的原因很多。

你要做的就是先打开trace flag， 分析 捕捉到的死锁信息，然后再进行解决。

[claro]

obuntu已经说的很详细了。没有啥补充的，重新描述一次：
1222是输出文本所以比较直观，其结果也是最详细的。
该设置后如果程序无异常即可，不会影响到性能，结果会抛到ERRORLOG里。

[claro]

才看到内存才1G！这样的配置不建议profiler来trace。建议升级硬件，非SERV的内存条已经很便宜了。
如果是SERV，带校验的条子就可以。

[billpu]

sqlserver2005 就用1222吧(明显信息优于1204,只是为了兼容保留了1204),如果是sqlserver2000 那只能用1204
另外看日志也是很痛苦的事情,慢慢看吧
如果可能建议你买本书看看Microsoft SQL Server2005技术内幕查询调整和优化