并发下的死锁问题

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

　　　　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 = 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 记录太多。

　　　　

 -------------------------------------转载请注明出处------------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------------------