sqlserver 死锁欧洲杯竞猜平台

文章分享一篇关于减少mssqlserver数据库死锁的技巧,有需要了解的朋友可以参考一下。

/*–处理死锁

这里的办法,对所有的数据库都适用。

查看当前进程,或死锁进程,并能自动杀掉死进程

这个解决办法步骤如下:

因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

  1. 每个表中加 updated_count (integer) 字段

  2. 新增一行数据,updated_count =0 :insert into table_x
    (f1,f2,…,update_count) values(…,0);

  3. 根据主键获取一行数据 SQL,封装成一个 DAO 函数(我的习惯是每个表一个
    uuid 字段做主键。从不用组合主键,组合主键在多表 join 时 SQL
    写起来很麻烦;也不用用户录入的业务数据做主键,因为凡是用户录入的数据都可能错误,然后要更改,不适合做主键)。select
    * from table_x where pk = ?

  4. 删除一行数据4.1 先通过主键获取此行数据, 见 3.

–邹建 2004.4–*/

4.2 delete from table_x where pk = ? and update_count=? , 这里 where
中的 update_count 通过 4.1 中获取4.3 检查 4.2
执行影响数据行数,如果删除失败,则是别人已经删除或者更新过同一行数据,抛异常,在最外面
rollback,并通过合适的词语提醒用户有并发操作,请稍候再试。int count =
cmd.ExecuteNonQuery();if(udpatedCount 1){throw new
Exception(检测到并发操作,为防止死锁,已放弃当前操作,请稍候再试,表
xxx, 数据 key .);}

/*–调用示例

  1. 更新一行数据5.1 先通过主键获取此行数据, 见 3.5.2 update table_x set
    f1=?,f2=?, …,update_count=update_count+1 where pk = ? and
    update_count=? , 这里where 中的 update_count 通过 5.1 中获取5.3 检查
    5.2
    执行影响数据行数,如果更新失败,则是别人已经删除或者更新过同一行数据,抛异常,在最外面
    rollback,并通过合适的词语提醒用户有并发操作,请稍候再试。int count =
    cmd.ExecuteNonQuery();if(udpatedCount 1){throw new
    Exception(检测到并发操作,为防止死锁,已放弃当前操作,请稍候再试,表
    xxx, 数据 key .);}

  2. 数据库访问层 DAO 中,绝对不要写 try catch,也不要写 commit/rollback.
    因为当我写了一个 dao1.insert(xxx) ,另一个人写了 dao2.insert(xxx),
    两周后有可能会有人把这两个函数组合在一起放在一个事务中。如果dao1.insert(xxx)已经
    commit ,那么dao2.insert(xxx) 中rollback
    会达不到期望效果。很多电脑书中示例代码,都有这个错误。

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

6.1 单机版程序,每个按钮操作,对应一个事务。可以在把
connection/transaction 传递到 dao
中。在按钮响应的代码处,处理事务。catch 到任何 Exception 都要 rollback.

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

6.2 网页版程序,每个按钮操作,对应一个事务。可以在把
connection/transaction 传递到 dao
中。在按钮响应的代码处,处理事务。我强烈建议对于
Web应用,数据库连接的打开/关闭、数据库事务的开始和 commit/rollback 全在
filter 中处理(Java EE 和 ASP.NET MVC 都有 filter,
其它的不知道),事务、数据库连接通过 threadlocal 传入到 DAO 中。filter 中
catch 到任何 Exception 都要 rollback.

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

见过很多用 Spring
的人,代码中启动了几个数据库事务自己都不知道,符不符合自己的需要,也不知道。我的建议是,禁止使用
Spring 管理数据库事务。

7.
单表的增、删、改、通过主键查,应该用工具自动生成。自动生成代码,应该放在单独一个目录,以便后面有数据库表改动,可以重新生成代码并覆盖。自动生成的文件,在第一行就写上注释,表示这是一个自动生成的文件,以后会被自动覆盖,所以不要改这个文件。

举例来说,对于 tm_system_user 表,可以自动生成 TmSystemUserDAO,
包含函数: insert(TmSystemUser), update(TmSystemUser),
delete(TmSystemUser), getByKey(key), batchInsert(TmSystemUser[])。

  1. 总是使用事务,并用 ReadCommited 级别,即使是纯查询
    SQL,也这么写。这可以简化设计与写代码,没有发现明显多余的性能消耗。

  2. 数据设计时,尽量避免 update/delete.
    举例来说,如果是一个请假条的审批流程,把请假条申请设计成一个表,领导批复设计成另一个表。尽量避免设计时合并成一个表,把批准状态(同意/否决)、批准时间当成请假条申请的属性。说极端一点,最好从数据库设计上,避免后续编程有
    update/delete, 只有 insert。 好像现在流行的 NoSQL 也是这么个思路。

  3. 补充,如果在后台检查页面录入数据,报错处理,有以下两种方法:

10.1 只要有一个错误,就 throw exception.

10.2
把所有的错误都检测出来,比如,用户名未录入,电子邮件未录入,放在一个
List中,然后 throw exception.

看解决方法

use master –必须在master数据库中创建go

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

/**//*–处理死锁 查看当前进程,或死锁进程,并能自动杀掉死进程

因为是针对死锁的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

感谢: caiyunxia,jiangopen 两位提供的参考信息

–邹建 2004.04(引用请保留此信息)–*/

代码如下复制代码

/**//*–调用示例

exec p_lockinfo–*/create proc p_lockinfo@kill_lock_spid bit=1,
–是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock bit=1
–如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示asset nocount
ondeclare @count int,@s nvarchar(1000),@i intselect
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_addressinto #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 blocked0)a order by
s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_spid_if_nolock=1begin 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=@@rowcountend

if @count0begin 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 @@rowcount=0 insert #t1(a) values(null) 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) if @@rowcount=0
insert #t1(a) values(null) set @i=@i+1 end select
a.*,进程的SQL语句=b.EventInfo from #t a join #t1 b on a.id=b.idendset
nocount offgo

对于SQL Server的死锁问题,下面是几则实践中很有用的小技巧。

■ 使用SQL Server Profiler的Create Trace Wizard运行Identify The Cause of
a
Deadlock跟踪来辅助识别死锁问题,它将提供帮助查找数据库产生死锁原因的原始数据。[适用于7.0,2000]


如果无法消除应用中的所有死锁,请确保提供了这样一种程序逻辑:它能够在死锁出现并中止用户事务之后,以随机的时间间隔自动重新提交事务。这里等待时间的随机性非常重要,这是因为另一个竞争的事务也可能在等待,我们不应该让两个竞争的事务等待同样的时间,然后再在同一时间执行它们,这样的话将导致新的死锁。[适用于6.5,7.0,2000]

■ 尽可能地简化所有T-SQL事务。此举将减少各种类型的锁的数量,有助于提高SQL
Server应用的整体性能。如果可能的话,应将较复杂的事务分割成多个较简单的事务。[适用于6.5,7.0,2000]


所有条件逻辑、变量赋值以及其他相关的预备设置操作应当在事务之外完成,而不应该放到事务之内。永远不要为了接受用户输入而暂停某个事务,用户输入应当总是在事务之外完成。[适用于6.5,7.0,2000]

■ 在存储过程内封装所有事务,包括BEGIN TRANSACTION和COMMIT
TRANSACTION语句。此举从两个方面帮助减少阻塞的锁。首先,它限制了事务运行时客户程序和SQL
Server之间的通信,从而使得两者之间的任何消息只能出现于非事务运行时间。其次,由于存储过程强制它所启动的事务或者完成、或者中止,从而防止了用户留下未完成的事务。[适用于6.5,7.0,2000]


如果客户程序需要先用一定的时间检查数据,然后可能更新数据,也可能不更新数据,那么最好不要在整个记录检查期间都锁定记录。假设大部分时间都是检查数据而不是更新数据,那么处理这种特殊情况的一种方法就是:先选择出记录,然后把它发送给客户。

如果用户只查看记录但从来不更新它,程序可以什么也不做;反过来,如果用户决定更新某个记录,那么他可以通过一个WHERE子句检查当前的数据是否和以前提取的数据相同,然后执行UPDATE。

类似地,我们还可以检查记录中的时间标识列。如果数据相同,则执行UPDATE操作;如果记录已经改变,则应用应该提示用户以便用户决定如何处理。虽然这种方法需要编写更多的代码,但它能够减少加锁时间和次数,提高应用的整体性能。[适用于6.5,7.0,2000]


尽可能地为用户连接指定具有最少限制的事务隔离级别,而不是总是使用默认的READ
COMMITTED。为了避免由此产生任何其他问题,应当参考不同隔离级别将产生的效果,仔细地分析事务的特性。[适用于6.5,7.0,2000]


使用游标会降低并发性。为避免这一点,如果可以使用只读的游标则应该使用READ_ONLY游标选项,否则如果需要进行更新,尝试使用OPTIMISTIC游标选项以减少加锁。设法避免使用SCROLL_LOCKS游标选项,该选项会增加由于记录锁定引起的问题。[适用于6.5,7.0,2000]


如果用户抱怨说他们不得不等待系统完成事务,则应当检查服务器上的资源锁定是否是导致该问题的原因。进行此类检查时可以使用SQL
Server Locks Object: Average Wait Time
(ms),用该计数器来度量各种锁的平均等待时间。

如果可以确定一种或几种类型的锁导致了事务延迟,就可以进一步探究是否可以确定具体是哪个事务产生了这种锁。Profiler是进行这类具体分析的最好工具。[适用于7.0,2000]


使用sp_who和sp_who2来确定可能是哪些用户阻塞了其他用户。[适用于6.5,7.0,2000]


试试下面的一个或多个有助于避免阻塞锁的建议:1)对于频繁使用的表使用集簇化的索引;2)设法避免一次性影响大量记录的T-SQL语句,特别是INSERT和UPDATE语句;3)设法让UPDATE和DELETE语句使用索引;4)使用嵌套事务时,避免提交和回退冲突。[适用于6.5,7.0,2000]

相关文章