高并发update的 死锁产生原因
时间:2019-01-03 14:04 来源:CSDN 作者:赵崇
在说这个之前首先了解一下讲一下update语句sql中的情况。
死锁产生的条件:
出现循环等待资源。
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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
创建完后，应该是这个样子：
2.准备高并发的查询环境
因为一个人测试，很不好模拟现场的环境。所以使用sql 的waitfor 阻塞某一个查询：
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等待。Ａ在等待Ｂ的Ｕ锁释放。
结论
Ａ在等待Ｂ释放X锁，Ｂ在等待Ａ释放X锁。所以才会发生死锁。
解决方案：
给查询条件加索引，原因参考下一篇详解索引。
总结
死锁的发生，肯定是因为资源的循环等待。分析死锁，就是分析这些进程分别需要等待什么资源。在sql server中 一般可以使用 sql profiler进行死锁的监控。
---------------------
作者：赵崇
来源：CSDN
原文：https://blog.csdn.net/zc474235918/article/details/50547250
版权声明：本文为博主原创文章，转载请附上博文链接！
(责任编辑：IT)
在说这个之前首先了解一下讲一下update语句sql中的情况。
死锁产生的条件:
出现循环等待资源。
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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
创建完后，应该是这个样子：
2.准备高并发的查询环境
因为一个人测试，很不好模拟现场的环境。所以使用sql 的waitfor 阻塞某一个查询：
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等待。Ａ在等待Ｂ的Ｕ锁释放。
结论
Ａ在等待Ｂ释放X锁，Ｂ在等待Ａ释放X锁。所以才会发生死锁。
解决方案：
给查询条件加索引，原因参考下一篇详解索引。
总结
死锁的发生，肯定是因为资源的循环等待。分析死锁，就是分析这些进程分别需要等待什么资源。在sql server中 一般可以使用 sql profiler进行死锁的监控。
---------------------
作者：赵崇
来源：CSDN
原文：https://blog.csdn.net/zc474235918/article/details/50547250
版权声明：本文为博主原创文章，转载请附上博文链接！
(责任编辑：IT) |