一个很疑惑的死锁问题求解

Question

最近数据库经常死锁,所以找了一下发现一个问题.求高手.
简单重现步骤:
A开启一个事物更新表里面主键ID是 123的记录 然后立马查询这个记录 事物不提交也不回滚
B去调用1个存储过程查询 记录是 234的记录.然后查询条件中有like 然后就死锁了
但是最恶心的如果查询这个234的记录语句不放在存储过程中那么就不会死锁.A执行代码:
BEGIN TRANUPDATE [dbo].[DetailsOfCharge]
SET [LastOPDate] = GETDATE()
WHERE [DOCNo] = '123'SELECT [t1].[DOCIndex]
FROM [dbo].[DetailsOfCharge] AS [t1]
WHERE @@ROWCOUNT > 0 AND [DOCNo] = '123'B执行代码:
[ShareCharge] @SOANo = '2', @DOCNo = '234'B的存储过程代码:
CREATE  proc [dbo].[ShareCharge]
(
@SOANo char(30),
@DOCNo varchar (30)=''
)
as
BEGIN
SET NOCOUNT ON;
SET @DOCNo = '%' + @DOCNo
select * from DetailsOfCharge doc 
where doc.SoaNo = @SoaNo and doc.DOCNo LIKE @DOCNo
END
SOANo是主表主键 DOCNo是子表主键
子表的123和234 2条记录属于不同的主表记录
所以我就很郁闷了 这为什么会锁那?

Answer 1

死锁分析 
http://blog.csdn.net/roy_88/article/details/2686724

Answer 2

请看完再发.这不是一个标准的死锁.可以说是一个bug.存储过程中有问题,直接在查询分析器中执行无任何问题.

Answer 3

没有加锁
SET @DOCNo = '%' + @DOCNo
select * from DetailsOfCharge doc  
where doc.SoaNo = @SoaNo and doc.DOCNo LIKE @DOCNo
查询太慢吧

Answer 4

有用心看麼？以上是SQL2005用索引可解決

Answer 5

另一种解決方法，用鎖的方式 --A
SELECT [t1].[DOCIndex]
FROM [dbo].[DetailsOfCharge] AS [t1] WITH (UPDLOCK)
WHERE @@ROWCOUNT > 0 AND [DOCNo] = '123'--B
select * from DetailsOfCharge doc WITH (UPDLOCK,READPAST)
where doc.SoaNo = @SoaNo and doc.DOCNo LIKE @DOCNo

Answer 6

READPAST--為不讀取其他交易已鎖定的資料列和頁面
樓主可參照聯機，根據實現需求再指定

Answer 7

这个问题已经解决了.
表里面索引全有.而且查询速度绝对很快.再慢也不可能慢出死锁来.
我是想知道为什么有人开了事物其他人在存储过程中用Like查询不先关的数据会死锁.
从存储过程中把语句拷贝出来相当快而且不死.

Answer 8

2个记录查询的主键条件是不一样的.A和B语句里面查询的结果完全是没有交集的.为什么多个like会陷入死锁?我主要想知道这个问题.最恶心的是这个like直接在查询分析器中执行没有任何问题,所以因为这个问题纠结了很久.

Answer 9

存储过程 的优化，和 sql 语句优化 不太一样。往往 sql 语句 执行都比 存储过程的快，特别是带参数的存储过程，因为存储过程 只变异一次。当然 在2005之后可以加一些选项，来优化这个问题

Answer 10

你貼的語句，沒有什麼問題。要看看程序是否加了事務隔離等級SERIALIZABLE你的語句是A執行完更新后，B就可以執行不會出來死鎖，會等待A執行完更新

Answer 11

SET @DOCNo = '%' + @DOCNo
select * from DetailsOfCharge doc  
where doc.SoaNo = @SoaNo and doc.DOCNo LIKE @DOCNo'%' 这个在前面，会走表扫描（或者索引扫描），如果数据是Read Committed 模式，
就会被死锁；如果是 SET @DOCNo =  @DOCNo+'%' 这个形式，而且有相关索引的话，
应该不会死锁（如果有死锁，就得分析你前面的事物究竟锁住了什么资源（sp_lock），
如果查询和修改的数据在物理分布上有交叉，查询也可能会被锁住，需要再分析原因）。

Answer 12

A执行代码:
BEGIN TRANUPDATE [dbo].[DetailsOfCharge]
SET [LastOPDate] = GETDATE()
WHERE [DOCNo] = '123'SELECT [t1].[DOCIndex]
FROM [dbo].[DetailsOfCharge] AS [t1]
WHERE @@ROWCOUNT > 0 AND [DOCNo] = '123'
 ------执行完成后,此时对[ [DOCNo] = '123']的行加上排它锁(X),
 --并对行所在页加上意向排它锁(IX)
 
B执行代码:
[ShareCharge] @SOANo = '2', @DOCNo = '234'
--执行上述代码查找时会对扫描的页加上共享锁,当扫描到[DOCNo] = '123'所在的页时
--,因为共享锁是不是兼容意向排它锁的 ,导致阻塞.
--当前面那个事务结束后,才会继续执行扫描.--还有不管你是调用过程,还是用SQL语句执行查找,应该都会被阻塞的.我这里测试结果也是这样
--所以前面的事务要尽快提交.