Deadlock due to concurrent updates (with SIU lock)

We have 2 different wcf services which get invoked by a windows service. The methods in those wcf services are updating the same table inside different stored procedures. One thing is for sure: records which are getting updated in these 2 procs are different. Here is the deadlock graph xml:

        
            
                
                    
UPDATE Table1 WITH (ROWLOCK)
    SET Column1 = @Column1
    WHERE Id = @Id    
                
                
Proc [Database Id = 11 Object Id = 1557580587]    
            
            
                
                    
UPDATE Table1 WITH (ROWLOCK)
    SET Column1 = @Column1,
    Column2 = @Column2
    WHERE Id = @Id     
                
                
Proc [Database Id = 11 Object Id = 1669580986]    
            
        
        
            
                
                    
                
                
                    
                
            
            
                
                    
                
                
                    
                
            
        
    

Here is the index we have which is one of the culprits in the deadlock:

CREATE NONCLUSTERED INDEX [IX_Table1_Column1] ON [schema].[Table1] 
(
    [Column0] ASC,
    [Column1] ASC
)
INCLUDE
( 
    [Id], 
    [Column2]
)
WITH 
(
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
)
ON [PRIMARY];

We have a primary key nonclustered index on column Id.

The deadlock diagram is:

The particular issue here is that (4) IX is not compatible with (1) SIU, whereas it is compatible with the more usual IU lock seen in this situation.

The SIU lock occurs because somewhere between selecting the row in question with the UPDLOCK hint (which takes IX at the object level, IU at the page level, and U at the row level), a statement in the transaction is acquiring a shared (S) page-level lock on the nonclustered primary key index structure.

The existing IU page lock is converted to SIU. Although shared locks are usually released very quickly under the read committed isolation level, that does not occur when the shared lock is merged into a combination lock like SIU. The lifetime of that lock is the same as the IU component (the life of the transaction here).

You can avoid this specific deadlock by ensuring page-level shared locks are not taken on the same access method after the UPDLOCK statement (row-level shared locks would be ok). This will avoid the SIU page lock. The page granularity explains why operations on two different id values can deadlock.

That said, unless the question is missing some detail, you will still have an update statement that is prone to deadlock in another way. The base table appears to be a heap, and the update plan will likely feature an RID lookup:

This is a well-known pattern that often results in a conversion deadlock under high concurrency. The reading side of the plan accesses the nonclustered index first, then the base table. The update side of the plan accesses the base table first, then the nonclustered index. By touching the same resources in reverse order, multiple concurrent executions of the same plan can deadlock.

The RID lookup is needed for the query processor to check if the value of Column1 is being changed. If it is not changing, a redundant nonclustered index update can be avoided. You can remove this RID lookup by including Column1 in the nonclustered primary key, or by changing the primary key from nonclustered to clustered.

StackExchange Database Administrators Q#89460, answer score: 9