SQL Server – Understanding Deadlock XML

deadlocksql server

Recently a clients website has been receiving many deadlocks.

The website and CMS is custom built on asp.net MVC 3. The database is SQL Server 2008.

I believe this started occurring when there was an increase in CMS traffic making updates to records. An update to one item in our CMS results in updates across several tables which is all contained in a plain new TransactionScope()

I have looked at the deadlock XML but I am unsure exactly why the deadlock is occurring. I am looking for any help in explaining why the dead lock is occurring and any suggestions to resolved the deadlocks.

 <deadlock-list>
 <deadlock victim="processa94e3948">
  <process-list>
   <process id="processa94e3948" taskpriority="0" logused="0" waitresource="KEY: 7:72057602921267200 (e70029cc925c)" waittime="2413" ownerId="122355021476" transactionname="INSERT" lasttranstarted="2015-02-17T17:00:57.210" XDES="0x3b46199f0" lockMode="RangeS-S" schedulerid="5" kpid="344716" status="suspended" spid="109" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-02-17T17:00:57.090" lastbatchcompleted="2015-02-17T17:00:57.090" clientapp=".Net SqlClient Data Provider" hostname="SWIWEB02" hostpid="8560" loginname="TravelWisconsinWeb" isolationlevel="serializable (4)" xactid="122355021476" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="TravelWisconsinWeb.dbo.BlogArticleGetBlogArticlesByFiltersWithPagingCount" line="75" stmtstart="4086" stmtend="4958" sqlhandle="0x030007005488c563185701010da300000100000000000000">
insert into @taggedBlogs
                select distinct v.BaseContentId
                from Tags t 
                inner join ContentPages cp on cp.Id = t.ContentPageId
                inner join BlogArticles ba on ba.ContentPageId = cp.Id and ba.BlogArticleTypeId = @BlogArticleTypeId
                inner join Versions v on (v.ContentId = ba.Id or v.BaseContentId = ba.Id) and v.ContentTypeId = @blogContentTypeId
                where t.Tag in (SELECT Data FROM dbo.Split(@Tags, &apos;,&apos;));     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 7 Object Id = 1673889876]    </inputbuf>
   </process>
   <process id="processd734c8" taskpriority="0" logused="34628" waitresource="OBJECT: 7:1179111937:0 " waittime="2692" ownerId="122355020379" transactionname="user_transaction" lasttranstarted="2015-02-17T17:00:56.370" XDES="0x3031623b0" lockMode="IX" schedulerid="3" kpid="345824" status="suspended" spid="54" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-02-17T17:00:57.290" lastbatchcompleted="2015-02-17T17:00:57.290" clientapp=".Net SqlClient Data Provider" hostname="SWIWEB01" hostpid="9128" loginname="TravelWisconsinWeb" isolationlevel="serializable (4)" xactid="122355020379" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="TravelWisconsinWeb.dbo.VersionInsert" line="21" stmtstart="892" stmtend="1468" sqlhandle="0x03000700d168c661f25401010da300000100000000000000">
insert into Versions 
        (BaseContentId, ContentId, StatusId, SiteId, IsPublished, ModifiedByUserId, ModifiedDate, ModifiedMessage, ContentTypeId)
    values (@BaseContentId, @ContentId, @StatusId, @SiteId, @IsPublished, @ModifiedByUserId, GETDATE(), @ModifiedMessage, @ContentTypeId);     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 7 Object Id = 1640392913]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057602921267200" dbid="7" objectname="TravelWisconsinWeb.dbo.ContentPages" indexname="index_treenodeid" id="lock101eedd80" mode="X" associatedObjectId="72057602921267200">
    <owner-list>
     <owner id="processd734c8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processa94e3948" mode="RangeS-S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <objectlock lockPartition="0" objid="1179111937" subresource="FULL" dbid="7" objectname="TravelWisconsinWeb.dbo.Versions" id="lock188faa980" mode="S" associatedObjectId="1179111937">
    <owner-list>
     <owner id="processa94e3948" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processd734c8" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

BlogArticleGetBlogArticlesByFiltersWithPagingCount SP:

DECLARE @mostRecentlyPublished TABLE (Id int , BaseContentId int)

;WITH CategoryTree AS 
(
    SELECT Id, ParentId
    FROM Categories
    WHERE Id = @Category
    UNION ALL
    SELECT c.Id, c.ParentId
    FROM Categories c
    INNER JOIN CategoryTree as ct ON ct.Id = c.ParentId
)

INSERT INTO @mostRecentlyPublished
SELECT DISTINCT pba.Id as Id, pba.BaseContentId
FROM BlogArticles i
INNER JOIN PublishedBlogArticleIds pba ON pba.Id = i.Id
inner join ContentPages cp on cp.Id = i.ContentPageId
LEFT JOIN BlogArticle_Categories pc ON pc.BlogArticleId = i.Id
WHERE i.BlogArticleTypeId = @BlogArticleTypeId
ANd pba.SiteId = @SiteId 
AND 
(
    (
        @Category IS NULL
        OR
        (
            pc.CategoryId IN (SELECT Id FROM CategoryTree)
        )       
    )
    AND
    (
        (@ArchiveDate IS NULL AND GETDATE() BETWEEN cp.PublishStartDate AND COALESCE (cp.PublishEndDate, CONVERT(DATETIME,'9999-12-31')))
        OR
        (
            Year(cp.PublishStartDate) = YEAR(@ArchiveDate)
            AND
            MONTH(cp.PublishStartDate) = MONTH(@ArchiveDate)
            AND
            GETDATE() >= cp.PublishStartDate            
        )
    )
)
declare @blogContentTypeId int = (select id from ContentTypes where Name = 'Blog' and SiteId = @SiteId);
declare @taggedBlogs table (id int);

insert into @taggedBlogs
    select distinct v.BaseContentId
    from Tags t 
    inner join ContentPages cp on cp.Id = t.ContentPageId
    inner join BlogArticles ba on ba.ContentPageId = cp.Id and ba.BlogArticleTypeId = @BlogArticleTypeId
    inner join Versions v on (v.ContentId = ba.Id or v.BaseContentId = ba.Id) and v.ContentTypeId = @blogContentTypeId
    where t.Tag in (SELECT Data FROM dbo.Split(@Tags, ','));

SELECT count(p.id)
FROM BlogArticles p
INNER JOIN @taggedBlogs d ON d.Id = p.Id
INNER JOIN ContentPages cp on cp.Id = p.ContentPageId
INNER JOIN TreeNodes tr On tr.Id = cp.TreeNodeId
LEFT JOIN BlogArticle_Categories bc on bc.BlogArticleId = d.Id AND bc.IsPrimary = 1
LEFT JOIN Categories c ON bc.CategoryId = c.Id
LEFT JOIN MediaFiles mf ON mf.Id = p.MediaFileId
LEFT JOIN Users u on u.Id = p.AuthorId
WHERE cp.SiteId = @SiteId;

Version Insert Query:

insert into Versions 
        (BaseContentId, ContentId, StatusId, SiteId, IsPublished, ModifiedByUserId, ModifiedDate, ModifiedMessage, ContentTypeId)
    values (@BaseContentId, @ContentId, @StatusId, @SiteId, @IsPublished, @ModifiedByUserId, GETDATE(), @ModifiedMessage, @ContentTypeId);

    SET @Id = @@IDENTITY;

    IF @IsPublished = 1
        BEGIN
            -- make sure all basecontent is not marked as published except the newly published one
            UPDATE [Versions] SET [IsPublished] = 0 
            WHERE [BaseContentId] = @BaseContentId AND [ContentTypeId] = @ContentTypeId AND [SiteId] = @SiteId AND [Id] != @Id
        END
    ELSE
        BEGIN 
            DECLARE @StatusName nvarchar(255)

            SELECT @StatusName = [CodeName] FROM [VersionStatuses] WHERE [Id] = @StatusId 

            IF (@StatusName = 'Archived' or @StatusName='Deleted') 
                BEGIN
                    -- make sure all basecontent is not marked as published
                    UPDATE [Versions] SET [IsPublished] = 0 
                    WHERE [BaseContentId] = @BaseContentId AND [ContentTypeId] = @ContentTypeId AND [SiteId] = @SiteId
                END
        END

The call the VersionInsert Stored Procedure is one of the last calls from the TransactionScope. The TransactionScope starts when a web form is posted to the server. During the one post, all in the same TransactionScope, there could 10 – 20 different calls into the database including selects and inserts to and from several different tables.

Best Answer

BlogArticleGetBlogArticlesByFiltersWithPagingCount selects from ContentPages three times, and from Versions in the middle query. VersionInsert ran first, though, and for some reason already has a lock on ContentPages - it's impossible to tell if that's code from the procedure but that you haven't shown, code from a trigger somewhere, or code called from within the same TransactionScope() out in the app code. In any case, one transaction is trying to lock ContentPages and then Versions, and the other is trying to lock Versions and then ContentPages. This is like chasing your tail - it's not possible to resolve this without one of them giving up their attempt; in this case the longer procedure name about the BlogArticles is the one that has been chosen as a victim.

Here's how SQL Sentry software visualizes the deadlock graph (click to enlarge):

Click to enlarge (click to enlarge)

Since I can't see all of the code (and don't want to!), I can't give you specific advice on how to resolve this, but generally - and as I suggested in a comment - you don't want to control a large number of individual operations or procedure calls from a large, over-encompassing transaction. Transactions should be small, fast interactions that don't rely on affecting a large number of tables - this is bad for logging, bad for locking, and terrible for concurrency. You say:

all in the same TransactionScope, there could [be] 10 - 20 different calls into the database

But I really doubt that this is actually necessary. You should validate whether all of those calls really need to succeed or fail as a single unit, and consolidate code where you can. The CTE and a bunch of table variables in the BlogArticles procedure, for example, look like candidates for turning 3+ queries into one.

Related Question