3

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.

  • Need more info about the set of statements that each of these queries beings to. For example, do both transactions affect the same tables but in a different order? I have seen many problems caused by using TransactionScope and ad hoc SQL in .net when explicit transactions inside stored procedures would be better. – Aaron Bertrand Feb 18 '15 at 1:18
  • I added the stored procedures and some more details about our process. – Todd Schluechtermann Feb 18 '15 at 2:44
1

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.

1

The deadlock happens of course because one process is reading Versions table (to add rows into @taggedBlogs) and other is inserting new rows into it. You can figure out from the details if the deadlock happens in the table itself or in index.

Normal things I do when trying to reduce deadlocks is to check both of the processes if the amount of pages they access can be reduced which will decrease the chance of deadlock. Since the other process is insert, you can't really do anything to it, except to check if there's unnecessary indexes in Versions, because insert must also update them. For the select, you could check if there's a new index that could be added / columns that can be added to existing indexes etc. so that it would read less pages.

One thing I noticed there is this:

t.Tag in (SELECT Data FROM dbo.Split(@Tags, ','));

You should do that before the actual select, fill a temp, variable or temp. table with those values, because now optimizer doesn't know how many rows that will return (assumes always 1, which will happen with temp variable too) and it's probably a slow operation to split the string which will lock the pages longer. If that function is some kind of loop / recursive CTE and there's a lot of tags, you should take a look at DelimitedSplit8k by Jeff Moden: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Other thing that might help is splitting select into 2 parts for these 2 rules:

inner join Versions v on (v.ContentId = ba.Id OR v.BaseContentId = ba.Id) and ...

If that helps or makes it worse depends on your data and tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.