为什么我的聚集索引会导致死锁(Keylock和Pagelocks)

Question

我们在Server中每天都会经历2-3次死锁。通过分析，我们发现死锁是由同时发生的密钥锁和分页锁组合造成的。

我们实际上使用EF获取数据，查询涉及6个以上的外键关系。

但在某些日子里，由于死锁，系统每天挂起超过6次。主表中有一个聚集索引列(主键字段)

我共享死锁图供您参考。

          unknown
        
        
          unknown
        
      
      
        (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT
        [Project4].[C3] AS [C1],
        [Project4].[C59] AS [C2],
        [Project4].[C60] AS [C3],
        [Project4].[C61] AS [C4],
        [Project4].[C62] AS [C5],
        [Project4]
      
    
    
      
        
          unknown
        
        
          unknown
        
      
      
        (@0 int,@1 datetime2(7),@2 int,@3 datetime2(7),@4 int,@5 int,@6 int)UPDATE [dbo].[Mapping]
        SET [AssToId] = NULL, [MapStatus] = @0, [Updated] = @1, [Umag] = @2, [MappingCompleteDate] = @3, [DActive] = @4, [Old_Umag] = @5
        WHERE ([Id] = @6)
      
    
    
      
        
          unknown
        
        
          unknown
        
      
      
        (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT
        [Project4].[C3] AS [C1],
        [Project4].[C59] AS [C2],
        [Project4].[C60] AS [C3],
        [Project4].[C61] AS [C4],
        [Project4].[C62] AS [C5],
        [Project4]
      
    
    
      
        
          unknown
        
        
          unknown
        
      
      
        (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT
        [Project4].[C3] AS [C1],
        [Project4].[C59] AS [C2],
        [Project4].[C60] AS [C3],
        [Project4].[C61] AS [C4],
        [Project4].[C62] AS [C5],
        [Project4]

Answer 1

最有可能是

您有一个使用写查询读取查询死锁，这意味着一个或两个查询计划中都有一个或多个密钥查找。

您可以通过使用服务提供商_BlitzCache跟踪查询计划并寻找调优机会：

/*select query*/
EXEC sp_BlitzCache 
    @OnlySqlHandles = '0x020000000d43691b40a21b2caa8564509985cf4d5ae9c3f30000000000000000000000000000000000000000';

/*update query*/
EXEC sp_BlitzCache 
    @OnlySqlHandles = '0x020000002d65c221bca694a98771b8d919bc9686028cea870000000000000000000000000000000000000000';

其他需要审查的事项：

• 您提到的外键索引，特别是如果他们有任何级联操作分配给他们。
• 使用乐观隔离度避免读写器阻塞和死锁