减少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.
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 select @count=@@rowcount,@i=1 if @count=0 and @show_spid_if_nolock=1 if @count>0
|
对于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]
补充:数据库,Mssql