金沙注册网站-新金沙官网 计算机数据 减少mssqlserver数据库死锁的技巧

减少mssqlserver数据库死锁的技巧

文章分享一篇关于减少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(引用请保留此信息)–*/

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图