SQLSERVER2008 死锁问题

sl318620 2013-03-09 08:22:03

Application 偶尔会发生死锁的情况，以前就做了一些分析，初步定位了两段有冲突的地方，今天特意做了一下试验，并把DBCC监控死锁的命令打开，果然发现是两段SQL同时执行可能会发生冲突，但分析了半天，仍没有找出死锁的原因，请大家来看看，并说说怎么设计来避免。通过Application 加锁的方式来规避也是可以的，但改动要麻烦点，看通过SQLServer的索引设计或者写法的改变是否可以避免这个问题.


Query1 第一个connection:
在一个查询的Connection中查询多个备件，但其中是没有事务的.


waitfor DELAY '00:00:05'

WHILE (1=1)
BEGIN
select pcp.* from PartCountryPlannerReviewReason pcp
inner join ValidationReviewReason vrr on pcp.ReviewReasonID = vrr.ID
WHERE pcp.ChubID = 1 AND pcp.Material in
('620893-001','670539-001','671352-001','608150-001','681958-001','605961-001','604054-001','657095-601','613458-001')
AND pcp.ReviewedOn is null AND vrr.CanBeBatchReviewed = 1

END


query1 执行计划：
select pcp.* from PartCountryPlannerReviewReason pcp inner join ValidationReviewReason vrr on pcp.ReviewReasonID = vrr.ID WHERE pcp.ChubID = 1 AND pcp.Material in ('620893-001','670539-001','671352-001','608150-001','681958-001','605961-001','604054-001','657095-601','613458-001') AND pcp.ReviewedOn is null AND vrr.CanBeBatchReviewed = 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([pcp].[ReviewReasonID]))
|--Clustered Index Scan(OBJECT:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[PK_PartCountryPlannerReviewReason] AS [pcp]), WHERE:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ChubID] as [pcp].[ChubID]=(1) AND [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ReviewedOn] as [pcp].[ReviewedOn] IS NULL AND ([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'604054-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'605961-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'608150-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'613458-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'620893-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'657095-601' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'670539-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'671352-001' OR [AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [pcp].[Material]=N'681958-001')))
|--Clustered Index Seek(OBJECT:([AMEETOT3Trans].[dbo].[ValidationReviewReason].[PK_ValidationReviewReason] AS [vrr]), SEEK:([vrr].[ID]=[AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ReviewReasonID] as [pcp].[ReviewReasonID]), WHERE:([AMEETOT3Trans].[dbo].[ValidationReviewReason].[CanBeBatchReviewed] as [vrr].[CanBeBatchReviewed]=(1)) ORDERED FORWARD)

Query 2 第二个Connnection:


WHILE(1=1)
BEGIN
UPDATE PRR
SET Remark = 'XXX test'
FROM PartCountryPlannerReviewReason PRR
inner join InventorySafetyRop isr on prr.ChubID = isr.CHUBID AND PRR.Material = isr.Material
WHERE PRR.ChubID = 1 AND isr.IsValidated = 1
END

query2 执行计划：

UPDATE PRR SET Remark = 'XXX test' FROM PartCountryPlannerReviewReason PRR inner join InventorySafetyRop isr on prr.ChubID = isr.CHUBID AND PRR.Material = isr.Material WHERE PRR.ChubID = 1 AND isr.IsValidated = 1
|--Clustered Index Update(OBJECT:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[PK_PartCountryPlannerReviewReason] AS [PRR]), SET:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Remark] as [PRR].[Remark] = [Expr1006]))
|--Compute Scalar(DEFINE:([Expr1006]=N'XXX test'))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Hash Match(Left Semi Join, HASH:([PRR].[Material])=([isr].[Material]), RESIDUAL:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[Material] as [PRR].[Material]=[AMEETOT3Trans].[dbo].[InventorySafetyRop].[Material] as [isr].[Material]))
|--Bitmap(HASH:([PRR].[Material]), DEFINE:([Bitmap1013]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([PRR].[Material]))
| |--Clustered Index Scan(OBJECT:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[PK_PartCountryPlannerReviewReason] AS [PRR]), WHERE:([AMEETOT3Trans].[dbo].[PartCountryPlannerReviewReason].[ChubID] as [PRR].[ChubID]=(1)) ORDERED)
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([isr].[Material]), WHERE:(PROBE([Bitmap1013],[AMEETOT3Trans].[dbo].[InventorySafetyRop].[Material] as [isr].[Material])))
|--Hash Match(Inner Join, HASH:([isr].[CHUBID], [Uniq1005])=([isr].[CHUBID], [Uniq1005]), RESIDUAL:([AMEETOT3Trans].[dbo].[InventorySafetyRop].[CHUBID] as [isr].[CHUBID] = [AMEETOT3Trans].[dbo].[InventorySafetyRop].[CHUBID] as [isr].[CHUBID] AND [Uniq1005] = [Uniq1005]))
|--Bitmap(HASH:([isr].[CHUBID], [Uniq1005]), DEFINE:([Bitmap1012]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([isr].[CHUBID], [Uniq1005]))
| |--Index Seek(OBJECT:([AMEETOT3Trans].[dbo].[InventorySafetyRop].[IX_InventorySafetyRop_IsValidated] AS [isr]), SEEK:([PtnId1002]=(2) AND [isr].[IsValidated]=(1) AND [isr].[CHUBID]=(1)) ORDERED FORWARD)
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([isr].[CHUBID], [Uniq1005]))
|--Index Scan(OBJECT:([AMEETOT3Trans].[dbo].[InventorySafetyRop].[IX_InventorySafetyRop_Material] AS [isr]), SEEK:([PtnId1002]=(2)), WHERE:([AMEETOT3Trans].[dbo].[InventorySafetyRop].[CHUBID] as [isr].[CHUBID]=(1) AND PROBE([Bitmap1012],[AMEETOT3Trans].[dbo].[InventorySafetyRop].[CHUBID] as [isr].[CHUBID],[Uniq1005])) ORDERED FORWARD)

几秒钟后出现死锁

下面贴图：
1,PartCountryReviewReason table structure:
看死锁报错主要是PartCountryReviewReason这张表的主键ID死锁，所以现在只把这个表的Structure贴了上来，如有需要也可以帖其它的表结构.


2,deadlock sqlserver information:



...全文
421 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
sl318620 2013-03-11
  • 打赏
  • 举报
 回复
引用 15 楼 perfectaction 的回复:
上面的第一个SELECT加上with(nlock)就可以避免，一般都建议这么做。
这个这么做应该会更好，但application 中用dataentity 来SELECT加一个nolock hint语句还真不容易，所以就准备还是放到后台usp中的update中来改。
lxl 2013-03-11
  • 打赏
  • 举报
 回复
要从调整不合理的程序来彻底解决问题
lxl 2013-03-11
  • 打赏
  • 举报
 回复
是的， WHILE (1=1)这样的循环有意义吗？
nzperfect 2013-03-11
  • 打赏
  • 举报
 回复
上面的第一个SELECT加上with(nlock)就可以避免，一般都建议这么做。
sl318620 2013-03-11
  • 打赏
  • 举报
 回复
谢谢大家的参与，结帖
szm341 2013-03-11
  • 打赏
  • 举报
 回复
引用 19 楼 sl318620 的回复:
另外分布式应用程序中并发情况很常见的，Update和SELECT同时发生也不奇怪，只是加锁的地方可以探讨，原来用乐观锁方式，认为SQLSERVE……
这个有时确实是需要程序员在设计的时候取考虑的，一般在容易产生死锁的地方的程序要顺序执行 就像我上面举的例子，查询按照123的顺序，update安装321的顺序，这样便有机会产生死锁 如果查询123而update也123，那肯定是发生的阻塞，从而避免死锁
sl318620 2013-03-11
  • 打赏
  • 举报
 回复
引用 16 楼 lxl 的回复:
是的， WHILE (1=1)这样的循环有意义吗？
这个不要考虑WHILE (1=1),只是想说明现某一次很偶然的机会中Update会和SELECT发生冲突，实际应用中肯定不会这么写. 另外分布式应用程序中并发情况很常见的，Update和SELECT同时发生也不奇怪，只是加锁的地方可以探讨，原来用乐观锁方式，认为SQLSERVER自己会去处理，但从这个地方看来或者这种情况，必须程序员来考虑
sl318620 2013-03-10
  • 打赏
  • 举报
 回复
最后找到了解决方法： WHILE(1=1) BEGIN --BEGIN TRY --BEGIN TRAN UPDATE PRR SET Remark = 'XXX test' FROM PartCountryPlannerReviewReason PRR with(TABLOCK) inner join InventorySafetyRop isr on prr.ChubID = isr.CHUBID AND PRR.Material = isr.Material WHERE PRR.ChubID = 1 AND isr.IsValidated = 1 END 在Update的时候用表级锁就可以了。 加之后能解决分析如下：SELECT CHUBID是非聚集索引，在查找其它字段时，仍然需要用到聚集索引，在SELECT 出来1,2时 正准备申请3的S锁，而这时可能3正在被Update 修改，修改数据时聚集索引总时会被更新，这通过执行计划可以看到，形成阻塞，而这是Update完3需要申请1,2的X锁时，发现SELECT上的S锁正占用，所以开成死锁。与你上面的分析一致。 但加之后呢，就不存在这个问题了，因为Update是要加表级修改锁Tablock是根据SELECT或者Update/Insert/Delete决定加锁的类型的，这时候发现SELECT已加S锁，所以加不上，这时候就形成阻塞，那就等SELECT完成后，再做Update,再做Update时， SELECT也不能在表U锁上加S锁，所以也是阻塞。
sl318620 2013-03-10
  • 打赏
  • 举报
 回复
引用 13 楼 Vidor 的回复:
TABLOCK可以解决问题，但是并发性就。。。快照隔离或许是不错的选择。 可以在索引方面考虑调整一下，两个都是聚集索引扫描，而且UPDATE是并行处理的，这个才是导致死锁的关键。
加上Tablock后并发性是要差一点，但也能接受 不过你说的快照隔离以及索引到时可以研究一下。 这两个地方要用到聚集索引扫描到是可以理解的，因为要去SELECT或者Update到其它未加索引的行，最张都要靠索引来定位。
Vidor 2013-03-10
  • 打赏
  • 举报
 回复
TABLOCK可以解决问题，但是并发性就。。。快照隔离或许是不错的选择。 可以在索引方面考虑调整一下，两个都是聚集索引扫描，而且UPDATE是并行处理的，这个才是导致死锁的关键。
sl318620 2013-03-10
  • 打赏
  • 举报
 回复
应该是锁粒度问题导致的，S行锁或者页锁应该都是出来数据就立即释放，然后又要申请新的S锁 现在把Update改成表级锁就解决这个问题，因为在Update的时候Select所需要申请的页锁或者行锁都申请不到任何资源，只能阻塞，换过来，如果SELECT申请到的任何行锁或者页锁都会阻塞Update的申请。这样只有排队而不会形成死锁。
Vidor 2013-03-10
  • 打赏
  • 举报
 回复
S行锁是会即时释放的，不应该导致死锁，分析半天，基本弄明白了，是锁粒度问题。 根据LOG图片来分析，应该是死在页锁不是行锁，SELECT等待IX的资源，证明它在申请页共享锁S，如果申请页的IS，IX是不会诸塞IS的。 不知道分析得对不对。
szm341 2013-03-09
  • 打赏
  • 举报
 回复
引用 7 楼 sl318620 的回复:
这样同一张表的SELECT和Update都会造成死锁，有什么好办法可以规避这种死锁。
不一定是同一张表，也许是多表联合造成的，多表资源之间互相锁定 一般较复杂的查询可以用(nolock)提示让其不加共享锁查询，这种做法需要你的业务上对数据要求不敏感，因为nolock提示可能会产生脏读数据 还有就是update的时候提示rowlock，不过数据量过大时还是会导致锁升级 我之前研究过，update时尽量用聚集索引做筛选条件，这样资源锁定的比较精准，不会或者较少锁住数据页中的其他数据行，尽量缩短和减少资源锁定 不过这种死锁牺牲的一般都是查询进程，对业务影响应该不大 其实我看最有效的就是nolock了，cte关于update的情况我还在研究
sl318620 2013-03-09
  • 打赏
  • 举报
 回复
这种情况存在的可能性还是比较大的.
sl318620 2013-03-09
  • 打赏
  • 举报
 回复
这样同一张表的SELECT和Update都会造成死锁，有什么好办法可以规避这种死锁。
szm341 2013-03-09
  • 打赏
  • 举报
 回复
是的，我刚刚也做了个简单的实验，通常来说，查询结束即会释放共享锁 但是假设查询较慢时，查询id=1、2、3查询到每一条数据要用时1分钟 另一个事务中update id=3、2、1时，假设update2、3时持有x锁， 而当update1时，查询所持有的锁导致阻塞，update进程等待 而当查询进行到2、3时，由于update所持有的x锁导致了查询等待 这样就构成了死锁的条件
sl318620 2013-03-09
  • 打赏
  • 举报
 回复
这个不会错的，SQL LOG中写的很明显，SELECT的被牺牲掉了，阻塞那就好理解了： Date 3/9/2013 8:14:21 PM Log SQL Server (Current - 3/9/2013 8:14:00 PM) Source spid16s Message deadlock victim=process643a748 而且SQL Management也显示了出错的信息，我没有截图上来。
szm341 2013-03-09
  • 打赏
  • 举报
 回复
你确定发生的是死锁而不是阻塞吗？一般查询会与更新产生死锁吗~
sl318620 2013-03-09
  • 打赏
  • 举报
 回复
这是测试，原来里面是有数据库的，这样做就是增加出错的概率。
sl318620 2013-03-09
  • 打赏
  • 举报
 回复
我自己分析了一下：难道是它们在互掐ID吗？ 比如: SELECT 锁定了一些聚焦索引的ID(共享锁) UPDATE 锁定了另外一些聚焦锁引的ID(X锁),正准备去申请SELECT 锁定的一些ID,但这些ID正在被SELECT锁住，而SELECT又准备去申请锁Update锁定的一些ID，所以导致的死锁。 但这一理论是基于SELECT和我UPDATE都是按行和页去锁定的，这貌似又不对。
加载更多回复（1）
查询SQL server数据库死锁存储过程
查询SQL server数据库死锁存储过程
SQL SERVER 2008 R2 查看和杀死死锁进程
SQL SERVER 2008 R2 查看和杀死死锁进程。数据库进程死锁是一个很严重的问题，快速定位到死锁的进程尤为重要。
sql server 性能优化和日常管理维护
课程总结sql server运行过程中对系统资源的使用和由此产生性能问题，介绍数据库规划设计、事务和锁的基本知识，如何处理阻塞和死锁，优化索引和读懂统计信息和执行计划等知识，同时详细介绍SQL 2014新功能特性内存...
SQL Server死锁产生的原因及解决办法
本文举例说明SQL Server死锁产生的原因，并提出了解决的办法。
自动杀掉SQLSERVER中的死锁进程
本过程自动捕捉SQLSERVER中的死锁进程并且自动杀掉
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章