高并发update的死锁产生原因

死锁产生的条件:出现循环等待资源。

update对锁的流程:当sql发出一个update请求之后,数据库会对表中的每条记录加上U锁。然后数据库会根据where条件,将符合条件的记录转换为X锁。对不满足条件的记录释放U锁。

环境模拟

1. 创建数据库环境

--创建数据库
  create database DeadLockTest;
--创建数据表 (没有主键)
  use DealLocktest;
  create table t_table(
    A varchar(10),
    B varchar(10),
    C varchar(10)
  )
insert into  t_table values('a1','b1','c1');
insert into  t_table values('a2','b2','c2');
insert into  t_table values('a3','b3','c3');
insert into  t_table values('a4','b4','c4');
insert into  t_table values('a5','b5','c5');
insert into  t_table values('a6','b6','c6');
insert into  t_table values('a7','b7','c7');
insert into  t_table values('a8','b8','c8');
insert into  t_table values('a9','b9','c9');

创建完后,应该是这个样子:

2.准备高并发的查询环境

A要执行的sql语句:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 begin tran
    print convert(nvarchar(30),convert(datetime,getdate(),121),121)
       update t_table
     set A='a2'
     where B='b2'
    print convert(nvarchar(30),convert(datetime,getdate(),121),121)
    EXEC sp_lock @@spid

   waitfor  delay '00:00:05'

   update t_table
     set A='b4'
     where B='b4'
     EXEC sp_lock @@spid
    print convert(nvarchar(30),convert(datetime,getdate(),121),121)
 commit tran

B要进行的sql语句:

SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED
    begin tran
  update t_table
    set A='a1'
    where B='b1'
    EXEC sp_lock @@spid
   commit tran

因为A中第一条查询和第二天查询有5s间隔,所以整个的执行过程为:

1、执行A的第一条语句 阻塞5s

2、执行B的查询

3、A的阻塞释放,要执行A的第二条语句

 

分析:

1、执行A的查询的时候,数据库将所有的记录加上U锁,然后将将不是c2的记录全部释放U锁。对满足条件的数据添加X锁。此时,A事务还没有结束,A不会释放此时的X锁。

2、B查询的时候,B会对表中的所有记录添加U锁,因为B查询要用到t_table这张表,B查询c1的时候,满足条件给c1添加上X锁。执行c=2的时候,要给c2加U锁,此时该记录被A添加了X锁。所以B查询需要等A释放的X锁,才可以执行。此时B等待。B要等待A释放c2的X锁

3、A的阻塞释放之后,A要进行第二条查询。这个时候A要对符合第二个查询条件的记录添加X锁。当执行c1的时候,c1刚才被B添加了X锁。所以此时A等待。A在等待B的U锁释放。

 

结论:A在等待B释放X锁,B在等待A释放X锁。所以才会发生死锁。

 

解决方案:给查询条件加索引,原因参考下一篇详解索引。

 

总结:死锁的发生,肯定是因为资源的循环等待。分析死锁,就是分析这些进程分别需要等待什么资源。在sql server中 一般可以使用 sql profiler进行死锁的监控。

—— 完 ——
相关推荐
评论

立 为 非 似

中 谁 昨 此

宵 风 夜 星

。 露 , 辰

文章点击榜

细 无 轻 自

如 边 似 在

愁 丝 梦 飞

。 雨 , 花