sql 死锁问题

小小的技术员

已于 2022-02-21 14:51:37 修改

阅读量1.2k

点赞数

分类专栏： Sql C# 文章标签： sql 数据库 c#

于 2022-02-21 14:49:22 首次发布

本文链接：https://blog.csdn.net/xuzishe/article/details/123046800

版权

C# 同时被 2 个专栏收录

11 篇文章 1 订阅

订阅专栏

Sql

8 篇文章 0 订阅

订阅专栏

sql 报错：事务(进程 ID 101)与另一个进程被死锁在锁 | 通信缓冲区资源上，并且已被选作死锁牺牲品。请重新运行该事务。

参考内容：

SQL Server死锁总结 - Silent Void - 博客园

SET LOCK_TIMEOUT (Transact-SQL) - lalalla - 博客园

(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 或者 存储过程
        //互斥内容
        //...


    }
}

防止事务死锁最好的方法，还是大家推荐的用存储过程，在存储过程里面使用事务