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