sql 报错:事务(进程 ID 101)与另一个进程被死锁在 锁 | 通信缓冲区 资源上,并且已被选作死锁牺牲品。请重新运行该事务。
(1).按同一顺序访问对象。(注:避免出现循环)
(2).避免事务中的用户交互。(注:减少持有资源的时间,较少锁竞争)
(3).保持事务简短并处于一个批处理中。(注:同(2),减少持有资源的时间)
(4).使用较低的隔离级别。(注:使用较低的隔离级别(例如已提交读)比使用较高的隔离级别(例如可序列化)持有共享锁的时间更短,减少锁竞争)
(5).使用基于行版本控制的隔离级别:2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制,可以将读与写操作之间发生的死锁几率降至最低:
SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定 SNAPSHOT 事务隔离级别;
SET READ_COMMITTED_SNAPSHOT ON --指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项,没有加with nolock提示),SELECT语句会对请求的资源加S锁(共享锁);而开启了此选项后,SELECT不会对请求的资源加S锁。
注意:设置 READ_COMMITTED_SNAPSHOT 选项时,数据库中只允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前,数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。
(6).使用绑定连接。(注:绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别),并可以使用同一数据,而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话,也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin tran)后,调用exec sp_getbindtoken @Token out;来取得Token,然后传入另一个会话并执行EXEC sp_bindsession @Token来进行绑定(最后的示例中演示了绑定连接)。
记录一下自己的处理方案:
1、使用SET LOCK_TIMEOUT timeout_period(单位为毫秒)来设定锁请求超时
在执行sql前加:SET LOCK_TIMEOUT 200
2、在查询和更新语句中加:with(nolock) ,但会产生脏读
例:select top 1 *,GETDATE() from tb_1 with(nolock) where id=12
update a set name='xxxx' from tb_1 a with(nolock) where id=11
3、增加事务机制(最好在存储过程中添加)
例子:
SET LOCK_TIMEOUT 200
BEGIN TRANSACTION
declare @bh nvarchar(50)
begin try
--更新
update a set name='xxx',@bh=bh from tb_1 a with(nolock) where Id=11;
--更新信息
update A set State='空闲' from tb_1 A join tb_2 B with(nolock) on A.bh=B.bh and A.jbh=B.jbh where B.Id=55;
--更新状态
if(isnull(@bh,'')<>'')
begin
exec proc_UpdateData @bh;
end
end try
begin catch
--select Error_number() as ErrorNumber, --错误代码
-- Error_severity() as ErrorSeverity, --错误严重级别,级别小于10 try catch 捕获不到
-- Error_state() as ErrorState , --错误状态码
-- Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
-- Error_line() as ErrorLine, --发生错误的行号
-- Error_message() as ErrorMessage --错误的具体信息
if(@@trancount>0) --全局变量@@trancount,事务开启此值+1,他用来判断是有开启事务
rollback tran
end catch
Commit Transaction
如果还有问题,可以在代码中加同步锁(这里如果是多线程运行业务,不建议加),例如在C#代码中:
private static readonly object m_TestLock = new object();
public string XMethod(string params)
{
// ...
lock (m_TestLock )
{
//调用sql 或者 存储过程
//互斥内容
//...
}
}
防止事务死锁最好的方法,还是大家推荐的用存储过程,在存储过程里面使用事务