How can we help you today? How can we help you today?

Deadlocking on tempdb.dbo.RG_AllObjects_v4

I've got two developers both connected to the same SQL instance and I'm seeing fairly frequent deadlocking from the Redgate connections on tempdb.dbo.RG_AllObjects_v4. Figured I'd check to see if there's a recommendation for this before I start making changes.

Example:

Deadlock victim
    <frame procname="tempdb.dbo.RG_WhatsChanged_v4" line="275" stmtstart="24182" stmtend="25836">
UPDATE tempdb.dbo.RG_AllObjects_v4
SET    EntryDateTime = GETUTCDATE(),
       [Matched]     = 0,
       UserName      = NULL,
       ObjectName    = NewObjects.ObjectName,
       SchemaName    = NewObjects.SchemaName,
       TypeOfAction  = 'Modified',
       ModifyDate    = NewObjects.ModifyDate
FROM   tempdb.dbo.RG_AllObjects_v4 CurrentObjects
       INNER JOIN #CurrentDatabaseObjects NewObjects
         ON CurrentObjects.DatabaseID = NewObjects.DatabaseID
            AND CurrentObjects.ObjectID = NewObjects.ObjectID
            AND CurrentObjects.ObjectType = NewObjects.ObjectType
WHERE  CurrentObjects.ModifyDate &lt; NewObjects.ModifyDate


-- and any child objects that when created or deleted
-- there is no corresponding update to the parent modification date
-- currently only EP, but you wait!    </frame>

Deadlock winner
    <frame procname="tempdb.dbo.RG_WhatsChanged_v4" line="522" stmtstart="47136" stmtend="48048">
UPDATE Parent
  SET    UserName  = Child.UserName,
         [Matched] = 1
  FROM   tempdb.dbo.RG_AllObjects_v4 Parent
         INNER JOIN tempdb.dbo.RG_AllObjects_v4 Child
           ON Parent.DatabaseID = Child.DatabaseID
              AND Parent.ObjectID = Child.ParentObjectID
  WHERE  Parent.TypeOfAction IN ('Modified', 'Deleted')
         AND Parent.[Matched] = 0 AND Child.[Matched] = 1


  -- and sometimes it is the other way around.    </frame>
RandomUser
0

Comments

3 comments

Sort by
  • MarkDavis
    This is a old post but we are also experiencing this as a indication today 15 times
    Medium 24 Aug 2020 at 12:41
    Medium 24 Aug 2020 at 12:15
    Medium 24 Aug 2020 at 12:10
    Medium 24 Aug 2020 at 12:10
    Medium 24 Aug 2020 at 11:40
    Medium 24 Aug 2020 at 11:37
    Medium 24 Aug 2020 at 11:01
    Medium 24 Aug 2020 at 10:07
    Medium 24 Aug 2020 at 09:35
    Medium 24 Aug 2020 at 09:28
    Medium 24 Aug 2020 at 08:30
    Medium 24 Aug 2020 at 08:20
    Medium 24 Aug 2020 at 08:09
    Medium 24 Aug 2020 at 08:01
    Medium 24 Aug 2020 at 04:44
     without having to filter out alerts are there any other options
    MarkDavis
    0
  • BHughes
    Has anyone found a solution to this?  We've recently opened up monitor to other users and are encountering the same issue.

    BHughes
    0
  • RandomUser
    Sorry, I thought I updated this previously but it looks like that's not the case. We asked our developers to uncheck the "Indicate changed objects in the Object Exporer and update indicators every n seconds" setting in the SQL Source Control options and that seemed to help. It's more of a workaround than a solution.
    Obviously, with Source Control not polling for changes, the developers need to manually check more frequently so they know what's been modified by others. 
    RandomUser
    0

Add comment

Please sign in to leave a comment.