SharePoint 2010 content database dead lock after opening excel file with thousands of hyperlinks

We encountered a SharePoint database dead lock last week and this seems to be a bug on SharePoint database side. Here is the information that we found about this dead lock.

1. Symptom of the issue -  After user opened an excel file or view the property of the file, the whole content database locked and users could not access anything inside this database on SharePoint 2010.
2. Root cause - After looking closely on the actions that are causing this database dead lock, we identify there is a Microsoft excel file contains over 40,000 entries that are hyperlinks to other system. This could be just http://www.google.com. When users open the file or view the property of the file, this will cause the whole content database locked. When we track the issue, we isolate the call to the SQL server listed below is the root cause that triggers the dead lock.    

<?query --

SELECT DISTINCT

        Links.TargetDirName AS LinkDirName,

        Links.TargetLeafName AS LinkLeafName, Links.Type AS LinkType,

        Links.Security AS LinkSecurity, Links.Dynamic AS LinkDynamic,

        Links.ServerRel AS LinkServerRel, Docs.Type AS LinkStatus,

        PointsToDir AS PointsToDir, NULL AS WebPartId, NULL AS LinkNumber,

        NULL AS WebId, NULL AS Search, NULL AS FieldId

    FROM

        TVF_Links_PId_DId_Lvl(@DocSiteId, @DocParentId, @DocId, @Level) AS Links

    LEFT OUTER JOIN

        AllDocs AS Docs WITH (INDEX=AllDocs_Url)

    ON

        Links.ServerRel = 1 AND

        Docs.SiteId = Links.SiteId AND

        Docs.DeleteTransactionId = 0x AND   

        Docs.DirName = Links.TargetDirName AND

        Docs.LeafName = Links.TargetLeafName

    UNION ALL

    SELECT DISTINCT

        Docs.DirName AS LinkDirName,

        Docs.LeafName AS LinkLeafName, Links.Type AS LinkType,

        NULL AS LinkSecurity, NULL AS LinkDynamic, NULL AS LinkServerRel,

        CAST(128 AS tinyint) AS LinkStatus,

        NULL AS PointsToDir, NULL AS WebPartId,

        NULL AS LinkNumber, NULL AS WebId, NULL AS Search, NULL AS FieldId

    FROM

        TVF_Links_TargetUrlNotPointsToDir(@DocSiteId, @DocDirName, @DocLeafName) AS Links

    CROSS APPLY

        TVF_Docs_CI(Links.SiteId, Links.ParentId, Links.DocId, Links.Level) AS Docs

    UNION ALL

    SELECT DISTINCT

        Docs.DirName AS LinkDirName,

        Docs.LeafName AS LinkLeafName, Links.Type AS LinkType,

        NULL AS LinkSecurity, NULL AS LinkDynamic, NULL AS LinkServerRel,

        CAST(128 AS tinyint) AS LinkStatus,

        NULL AS PointsToDir, NULL AS WebPartId,

        NULL AS LinkNumber, NULL AS WebId, NULL AS Search, NULL AS FieldId

    FROM

        TVF_Links_TargetUrlPointsToDir(@DocSiteId, @DocDirName, @DocLeafName) AS Links

    CROSS APPLY

        TVF_Docs_CI(Links.SiteId, Links.ParentId, Links.DocId, Links.Level) AS Docs

    OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1)

--?>

  

     This query is coming from SharePoint OOB stored procedure “proc_GetLinkInfoSingleDocInternal” which is called from “proc_FetchDocForRead”. This stored procedure is invoked to request the
metadata information and document stream of a document. We are still working with Microsoft to identify the solution and submit a potential bug fix.

    3. Workaround - After we identify this issue is caused by the so many links of the excel file, we were able to remove the links inside the file and upload the file to SharePoint again. This seems to resolve the issue at this time. However, we lost the links inside the file. We did not test the threshold of number of links inside one file that would cause SQL content database dead lock.

With new SharePoint 2010 BI capabilities, more SharePoint users are using BI tool such as excel services. We would expect the much larger excel files with more links inside. Before Microsoft resolves this issue, please be careful the number of links inside a single excel file.