并发下的死锁问题

页面导航:首页 > 数据库 > MsSql > 并发下的死锁问题

并发下的死锁问题

来源: 作者: 时间:2016-01-27 10:35 【

写本文之前我还傻傻的以为死锁只能是那种经典:session1 session2update selectselect update不多说明了教科书般的经典案例,对SQL 的理解逐渐深入,遇到的案例也不断增加今天来分享一个同一张
写本文之前我还傻傻的以为死锁只能是那种经典:
 
session1  session2
 
update    select
 
select    update
 
不多说明了教科书般的经典案例,对SQL 的理解逐渐深入,遇到的案例也不断增加今天来分享一个同一张表2类update 语句使用不同索引,在大量并发下导致的死锁。
 
废话不多说直接开整!
 
先创建一张测试表table_1
 
CREATE TABLE [dbo].[Table_1](
    [a] [int] NULL,
    [b] [nchar](10) NULL,
    [c] [nchar](10) NULL,
    [d] [nchar](10) NULL,
    [e] [uniqueidentifier] NULL,
    [f] [nchar](10) NULL
) ON [PRIMARY]

 

插入测试数据
 
declare @a int 
set @a = 0
while @a < 1000
begin 
insert into table_1
select @a ,'1','2','3',newid(),'test'
set @a = @a + 1

end 

 

 
创建索引,在e列(newid) 上创建聚集索引,在a 列 创建非聚集索引 包含列b
 
/****** Object:  Index [NonClusteredIndex-20151210-152313]    Script Date: 2015/12/10 15:34:50 ******/

CREATE CLUSTERED INDEX [ClusteredIndex-20151210-152411] ON [dbo].[Table_1]
(
    [e] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20151210-152313] ON [dbo].[Table_1]
(
 [a] ASC
)
INCLUDE (  [b]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

 

 
-----------------------------准备工作就绪----------------------------
 
下面来说一下这个死锁的模拟 :
 
一个session利用a列的非聚集索引更新数据
 
一个session 利用e列聚集索引扫描更新数据
 
下面我们看一下测试的语句:
 
 
------使用索引查找
update table_1 set b = 'i' where a in (294,184,197,894)
and c ='2'
 
------聚集索引扫描update table_1  set b = 'u' where a in (294,184,197,894,6,7,8,9,10,13,154,234,654,213,76,98,345,213,543,3,33,333,55,689,90,99,999,56,4,35,789,43,123)
and c ='2'
 
执行计划:
开2个窗口分别运行:
while 1=1
begin 
update table_1  set b = 'u' where a in (294,184,197,894,6,7,8,9,10,13,154,234,654,213,76,98,345,213,543,3,33,333,55,689,90,99,999,56,4,35,789,43,123)
and c ='2'
end

while 1=1
begin
update table_1 set b = 'i' where a in (294,184,197,894)
and c ='2'
end

 

 
使用profiler 抓取死锁信息:使用locks的模板就可以 ,建议去掉两个strating 记录太多。
运行两个语句 查看死锁信息:
session 56 拥有聚集索引上的X锁,去申请 非聚集索引上的X锁的同时,session 55拥有非聚集索引的U锁,去申请聚集索引的U锁而造成了死锁的情况,这个问题可以看出在in的条件数量多的时候 优化器选择了聚集索引扫描,更新数据及索引(聚集索引和非聚集索引),而数量少的时候使用非聚集索引扫描查找,更新数据及索引(聚集索引和非聚集索引)。
 
创建索引以及业务设计的时候要多考虑一下应用的场景避免这种。
 
出现这种问题要怎么解决呢?
 
1.提高或降低隔离级别,不推荐...治标不治本的方法。
 
2.找出问题的根源,如本例中如果都使用聚集索引的扫描或非聚集索引的查找,调整索引的使用。
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<