sql server deadlock error - Shared (S) and Intent-Exclusive (IX) locks under Serializable isolation

Deadlock errors such as below happen frequently on our 3rd party vendor database:

- 
  - 
    - 
      - 
        - 
          DELETE FROM ArchiveSummary
        
        Proc [Database Id = 13 Object Id = 711673583]
      
      - 
        - 
          DELETE FROM ArchiveSummary
        
        Proc [Database Id = 13 Object Id = 711673583]
      
    
    - 
      - 
        - 
          
        
        - 
          
        
      
      - 
        - 
          
        
        - 
          
        
      
    
  

This is the relevant code within uspci_AggregateReports that causes the issue:

```
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
--All records will be moved out of the ArchiveSummary table in a
--serializable transaction to prevent updates to the table during consolidation.
--When the data is moved out of ArchiveSummary into the cache table, ArchiveSummary
--is truncated and the transaction committed to enable normal activities on the table

INSERT ArchiveSummary_Cache
([VaultIdentity]
,[ArchivePointIdentity]
,[SavesetIdentity]
,[ArchivedDate]
,[OriginalSize]
,[ArchivedItemSize]
,[FileTypeID]
,[RefCount]
,[IsFSA]
,[MissingOriginalSizeRefCount]
,[IncludeInTimeSummaries]
,[ContentProviderIdentity]
,[RoundedOriginalSize])
SELECT [VaultIdentity]
,[ArchivePointIdentity]
,[SavesetIdentity]
,[ArchivedDate]
,[OriginalSize]
,[ArchivedItemSize]
,[FileTypeID]
,[RefCount]
,[IsFSA]
,[MissingOriginalSizeRefCount]
,[IncludeInTimeSummaries]
,[ContentProviderIdentity]
,[RoundedOriginalSize]
FROM ArchiveSummary

SELECT @retVal = @@ERROR,@totalSourceRecords = @@ROWCOUNT

IF( @retVal = 0)
BEGIN
DELETE FROM ArchiveSummary
SELECT @retVal = @@ERROR
IF( @retVal = 0)
BEGIN
COMMIT TRANSACTION
SET @CurrentCount = 0
END
ELSE
R

I agree with @Kin - why is Enterprise Vault running the same query at the same time from two different sessions? Is it possible you have more copies of EV running than you thought?

Here is how SQL Sentry Plan Explorer PRO visualizes the deadlock (click to enlarge):



Yes, they are both stuck trying to delete from ArchiveSummary. It's possible this is because there are other locks taken in an outer transaction that we can't see (or because EV is issuing it's own client-side transaction scope).

But before you even start to troubleshoot the code itself, I'd be interested in figuring out why this stored procedure is being called multiple times concurrently in the first place. That seems problematic no matter how you eliminate the deadlocks.

StackExchange Database Administrators Q#112859, answer score: 2