并发情况下，索引与死锁的关系

[专注or全面]

测试方法：并发下判断数据是否存在，存在则插入
说明：
这种问题有很多种写法，其他的我都测试过了,
这里只讨论这两种方式，其他方式也能达到目的，但是不在本帖讨论范围

--建表
create table tb_1 (id int,a varchar(50),dt datetime default getdate())
go
--表tb_1上没有任何索引，采用updlock,serializable锁提示
begin tran
	declare @i int 
	set @i=RAND()*10000
 if not exists (select * from tb_1 with(updlock,serializable) where id=@i)
    begin
        insert tb_1 (id,a) values (@i,NEWID())
    end
commit tran

serializable等同于 HOLDLOCK。保持共享锁直到事务完成，使共享锁更具有限制性；
而不是无论事务是否完成，都在不再需要所需表或数据页时立即释放共享锁。

1，加updlock,serializable提示 为什么会出现死锁，
不知道可不可以这样理解死锁：
对于updlock，在查询执行时，先在记录上添加共享锁，找到记录后再添加排它锁，
这个过程中，可能存在多个会话添加共享锁，随后共享锁都不释放，再请求第二步的排它锁
然后就出现在同一个资源上，不同的会话占用了同一个资源上的共享锁，同时又请求排它锁，
所以会造成死锁的情况

从死锁图中也可以看到，不是那种对不同资源的相互请求造成的，而是对同一个资源的请求造成的


但是好似乎有点矛盾

事实上，加updlock,serializable提示的时候，
直接在表上加了排它锁，表级的排它锁，那么所有回话之前就不会死锁了啊
比如我这个查询语句


2，updlock，仅仅只添加updlock提示的时候，没有出现死锁的问题，但是会出现重复数据
这个就比较容易理解了，
因为仅仅添加updlock提示的时候，可能存在多个会话去查询同一条数据的情况，
如果都没有查到，那么就要同时（即便不是“同时”插入，也要执行插入的sql，总是逻辑走到插入这里了）插入数据
所以就会出现重复数据的情况



当在表tb_1上添加索引的时候（create index index_1 on tb_1(id)）

1，对于updlock,serializable就不会出现死锁了，这又是为什么呢？

2，对于updlock提示，依然会出现重复数据
这个跟上面没有索引时是一样的，会出现重复数据


感谢您的讨论！

[rouqu]

我同时打开了Profile，没有扑捉到deadlock。 1 2 3 4 5 6 7 8 9 10 11 12 --建表 create table tb_1 (id int,a varchar(50),dt datetime default getdate()) go --表tb_1上没有任何索引，采用updlock,serializable锁提示 begin tran declare @i int set @i=RAND()*10000 if not exists (select * from tb_1 with(updlock,serializable) where id=@i) begin insert tb_1 (id,a) values (@i,NEWID()) end commit tran

[专注or全面]

要用多线程去测试的， 我用的是工具，sqlqueryStress，非常著名的一个工具 比如设置100个线程，每个线程循环1000次，很容易就出现了

[专注or全面]

引用 7 楼 rouqu 的回复:

大哥 为啥我执行你第一段没有死锁？

参考第一个截图

[lzh3ng]

学习学习。。。。。。。

[littdshen]

太牛b了，求详细讲述！

[littdshen]

为什么，改不了我的评论呢？

[阳泉酒家小当家]

引用 3 楼 x_wy46 的回复:

[quote=引用 2 楼 dotnetstudio 的回复:] 个人觉得加updlock,serializable是表级操作，将锁表，你如果这个时候执行插入，将会产生死锁。

是表级锁没错，那为什么加了索引之后就不会产生死锁了呢？[/quote] 在没索引的时候，由于不知道哪条数据才是符合你要找的要求的，所以没办法，只能一条一条读出来，然后一条一条过滤判断，是否是你要找的那条记录。 其实有了索引后，产生了一个质的变化，不需要一条一条去判断，而是通过索引，快速找到要查找的数据， 所以就不需要锁住表，进一步的，也就相对不容易死锁。

[--小F--]

引用 3 楼 x_wy46 的回复:

[quote=引用 2 楼 dotnetstudio 的回复:] 个人觉得加updlock,serializable是表级操作，将锁表，你如果这个时候执行插入，将会产生死锁。

是表级锁没错，那为什么加了索引之后就不会产生死锁了呢？[/quote] 加索引以后 扫描到所需要的信息就返回值了 不是全表扫描

[专注or全面]

引用 16 楼 yupeigu 的回复:

[quote=引用 3 楼 x_wy46 的回复:] [quote=引用 2 楼 dotnetstudio 的回复:] 个人觉得加updlock,serializable是表级操作，将锁表，你如果这个时候执行插入，将会产生死锁。

是表级锁没错，那为什么加了索引之后就不会产生死锁了呢？[/quote] 在没索引的时候，由于不知道哪条数据才是符合你要找的要求的，所以没办法，只能一条一条读出来，然后一条一条过滤判断，是否是你要找的那条记录。 其实有了索引后，产生了一个质的变化，不需要一条一条去判断，而是通过索引，快速找到要查找的数据， 所以就不需要锁住表，进一步的，也就相对不容易死锁。[/quote] 没有索引的时候是表级的排它锁，按道理只有一个会话完成之后其他会话才能执行，不会产生死锁才对啊

[misterliwei]

http://dba.stackexchange.com/questions/61259/is-this-a-race-condition-or-bug-in-sql-server-using-serializable-til-with-updloc

[guguda2008]

先说没索引时 语句由两部分组成，一查询，二插入 查询时用表提示加U锁，加S锁HOLDLOCK 插入时加X锁 然后假设有两个进程插入同一个ID 进程1进行完第一步了，释放U锁，保持S锁 同时进程2开始进行第一步，加U锁，保持S锁 然后进程1开始进行第二步，加X锁时被进程2的U锁和S锁阻塞 然后进行2开始进行第二步，释放U锁，保持S锁，加X锁时被进程1的S锁阻塞 最后形成死锁 ----------------------------------------------我学艺不精瞎BB不要鸟我------------------------------------------- 再说有索引时 进程1进行完第一步，释放U锁，保持S锁，但索引页上的U锁或X锁（我也不知道）没释放（事务预写之类的东西导致写的比较慢） 进程2开始进行第一步时，加U锁被进程1阻塞 进程1进行第二步，加X锁，保持U锁，索引页上的锁没释放 进程2继续被阻塞 进程1结束第二步，释放所有锁，进程2正常加锁进行第一二步 最后没有死锁 ----------------------------------------------我学艺不精瞎BB不要鸟我------------------------------------------- 另一种推测，加了索引后对=@ID的查询速度变快，事务变短，所以没能出现死锁。 ----------------------------------------------我学艺不精瞎BB不要鸟我------------------------------------------- 不加holdlock是比较标准的死锁语句，有没有索引一样的

[专注or全面]

引用 23 楼 guguda2008 的回复:

先说没索引时 语句由两部分组成，一查询，二插入 查询时用表提示加U锁，加S锁HOLDLOCK 插入时加X锁 然后假设有两个进程插入同一个ID 进程1进行完第一步了，释放U锁，保持S锁 同时进程2开始进行第一步，加U锁，保持S锁 然后进程1开始进行第二步，加X锁时被进程2的U锁和S锁阻塞 然后进行2开始进行第二步，释放U锁，保持S锁，加X锁时被进程1的S锁阻塞 最后形成死锁 ----------------------------------------------我学艺不精瞎BB不要鸟我------------------------------------------- 再说有索引时 进程1进行完第一步，释放U锁，保持S锁，但索引页上的U锁或X锁（我也不知道）没释放（事务预写之类的东西导致写的比较慢） 进程2开始进行第一步时，加U锁被进程1阻塞 进程1进行第二步，加X锁，保持U锁，索引页上的锁没释放 进程2继续被阻塞 进程1结束第二步，释放所有锁，进程2正常加锁进行第一二步 最后没有死锁 ----------------------------------------------我学艺不精瞎BB不要鸟我------------------------------------------- 另一种推测，加了索引后对=@ID的查询速度变快，事务变短，所以没能出现死锁。 ----------------------------------------------我学艺不精瞎BB不要鸟我------------------------------------------- 不加holdlock是比较标准的死锁语句，有没有索引一样的

这样解释逻辑上没有问题，看了你的解释，我对U锁有了进一步的认识，谢谢

[CodeC]

接下来的日子会一天比一天好

[xiaoxiangqing]

加了索引，不需要表扫描。

[laoer_2002]

学习了

[qq206115678]

才发现打错了，上边的U是X，唉！

[csdn_azn]

这些问题对我这个菜鸟来说真的是太深奥了。。

[nyf886]

引用 楼主 x_wy46 的回复:

测试方法：并发下判断数据是否存在，存在则插入 说明： 这种问题有很多种写法，其他的我都测试过了, 这里只讨论这两种方式，其他方式也能达到目的，但是不在本帖讨论范围

--建表
create table tb_1 (id int,a varchar(50),dt datetime default getdate())
go
--表tb_1上没有任何索引，采用updlock,serializable锁提示
begin tran
	declare @i int 
	set @i=RAND()*10000
 if not exists (select * from tb_1 with(updlock,serializable) where id=@i)
    begin
        insert tb_1 (id,a) values (@i,NEWID())
    end
commit tran

serializable等同于 HOLDLOCK。保持共享锁直到事务完成，使共享锁更具有限制性； 而不是无论事务是否完成，都在不再需要所需表或数据页时立即释放共享锁。 1，加updlock,serializable提示 为什么会出现死锁， 不知道可不可以这样理解死锁： 对于updlock，在查询执行时，先在记录上添加共享锁，找到记录后再添加排它锁， 这个过程中，可能存在多个会话添加共享锁，随后共享锁都不释放，再请求第二步的排它锁 然后就出现在同一个资源上，不同的会话占用了同一个资源上的共享锁，同时又请求排它锁， 所以会造成死锁的情况 从死锁图中也可以看到，不是那种对不同资源的相互请求造成的，而是对同一个资源的请求造成的 但是好似乎有点矛盾 事实上，加updlock,serializable提示的时候， 直接在表上加了排它锁，表级的排它锁，那么所有回话之前就不会死锁了啊 比如我这个查询语句 2，updlock，仅仅只添加updlock提示的时候，没有出现死锁的问题，但是会出现重复数据 这个就比较容易理解了， 因为仅仅添加updlock提示的时候，可能存在多个会话去查询同一条数据的情况， 如果都没有查到，那么就要同时（即便不是“同时”插入，也要执行插入的sql，总是逻辑走到插入这里了）插入数据 所以就会出现重复数据的情况 当在表tb_1上添加索引的时候（create index index_1 on tb_1(id)） 1，对于updlock,serializable就不会出现死锁了，这又是为什么呢？ 2，对于updlock提示，依然会出现重复数据 这个跟上面没有索引时是一样的，会出现重复数据 感谢您的讨论！

[Carry_Dominator]

加索引以后 扫描到所需要的信息就返回值了 不是全表扫描

[twtiqfn]

太强了，我连锁的概念都不太懂，牛啊，学习