一聚教程网:一个值得你收藏的教程网站

热门教程

减少mssqlserver数据库死锁的技巧

时间:2022-06-29 08:18:44 编辑:袖梨 来源:一聚教程网

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

 

这个解决办法步骤如下:

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.

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 ….”);
}

 

5. 更新一行数据
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 ….”);
}

 

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


数据库事务应该是这样界定起始范围:

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


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

 

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

 

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

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


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

 

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

 

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

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 不显示
as
set nocount on
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 @@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.id
end
set nocount off
go

 

对于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]

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

如果用户只查看记录但从来不更新它,程序可以什么也不做;反过来,如果用户决定更新某个记录,那么他可以通过一个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(SQL Server Books Online没有关于sp_who2的说明,但sp_who2提供了比sp_who更详细的信息)来确定可能是哪些用户阻塞了其他用户。[适用于6.5,7.0,2000]

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

热门栏目