你的位置:首页 > 数据库

[数据库]并发下的死锁问题


    写本文之前我还傻傻的以为死锁只能是那种经典:

    session1  session2

    update    select

    select    update

 

    不多说明了教科书般的经典案例,对SQL 的理解逐渐深入,遇到的案例也不断增加今天来分享一个同一张表2类update 语句使用不同索引,在大量并发下导致的死锁。

    废话不多说直接开整!

   

    -------------------------------------转载请注明出处------------http://www.cnblogs.com/double-K/p/5036606.html------------------

    先创建一张测试表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 = 0while @a < 1000begin insert into table_1select @a ,'1','2','3',newid(),'test'set @a = @a + 1end 

    创建索引,在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=1begin 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=1beginupdate table_1 set b = 'i' where a in (294,184,197,894)
and c ='2'end

 

    使用profiler 抓取死锁信息:使用locks的模板就可以 ,建议去掉两个strating 记录太多。

    

 

 

 -------------------------------------转载请注明出处------------http://www.cnblogs.com/double-K/p/5036606.html------------------

 

 

    运行两个语句 查看死锁信息:

    

    session 56 拥有聚集索引上的X锁,去申请 非聚集索引上的X锁的同时,session 55拥有非聚集索引的U锁,去申请聚集索引的U锁而造成了死锁的情况,这个问题可以看出在in的条件数量多的时候 优化器选择了聚集索引扫描,更新数据及索引(聚集索引和非聚集索引),而数量少的时候使用非聚集索引扫描查找,更新数据及索引(聚集索引和非聚集索引)。

    创建索引以及业务设计的时候要多考虑一下应用的场景避免这种。

    出现这种问题要怎么解决呢?

    1.提高或降低隔离级别,不推荐...治标不治本的方法。

    2.找出问题的根源,如本例中如果都使用聚集索引的扫描或非聚集索引的查找,调整索引的使用。

    

    

-------------------------------------转载请注明出处------------http://www.cnblogs.com/double-K/p/5036606.html------------------