SQLSERVER2008 死锁问题
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:
SQLSERVER Deadlock
[解决办法]
TABLOCK可以解决问题，但是并发性就。。。快照隔离或许是不错的选择。
可以在索引方面考虑调整一下，两个都是聚集索引扫描，而且UPDATE是并行处理的，这个才是导致死锁的关键。
[解决办法]
上面的第一个SELECT加上with(nlock)就可以避免，一般都建议这么做。
[解决办法]
