Dead lock comes when using Redgate source control.
amit987
Posts: 2 New member
Hi,
I am using your source control product in our organisation. We have issue with this product deadlock alerts are coming after using this product on our server. Please find the below script that are the cause of dead lock on our server. and also suggest how we can prevent this dead lock.
UPDATE Child
SET UserName = Parent.UserName,
[Matched] = 1
FROM tempdb.dbo.RG_AllObjects_v4 Child
INNER JOIN tempdb.dbo.RG_AllObjects_v4 Parent
ON Parent.ObjectID = Child.ParentObjectID
AND Parent.ObjectID = Child.ParentObjectID
WHERE Child.[Matched] = 0 AND Parent.[Matched] = 1
UPDATE tempdb.dbo.RG_AllObjects_v4
SET EntryDateTime = GETUTCDATE(),
[Matched] = 0,
UserName = NULL,
ObjectName = NewObjects.ObjectName,
SchemaName = NewObjects.SchemaName,
TypeOfAction = 'Renamed',
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.ObjectName <> NewObjects.ObjectName
OR COALESCE(CurrentObjects.SchemaName, '') <> COALESCE(NewObjects.SchemaName, ''))
I am using your source control product in our organisation. We have issue with this product deadlock alerts are coming after using this product on our server. Please find the below script that are the cause of dead lock on our server. and also suggest how we can prevent this dead lock.
UPDATE Child
SET UserName = Parent.UserName,
[Matched] = 1
FROM tempdb.dbo.RG_AllObjects_v4 Child
INNER JOIN tempdb.dbo.RG_AllObjects_v4 Parent
ON Parent.ObjectID = Child.ParentObjectID
AND Parent.ObjectID = Child.ParentObjectID
WHERE Child.[Matched] = 0 AND Parent.[Matched] = 1
UPDATE tempdb.dbo.RG_AllObjects_v4
SET EntryDateTime = GETUTCDATE(),
[Matched] = 0,
UserName = NULL,
ObjectName = NewObjects.ObjectName,
SchemaName = NewObjects.SchemaName,
TypeOfAction = 'Renamed',
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.ObjectName <> NewObjects.ObjectName
OR COALESCE(CurrentObjects.SchemaName, '') <> COALESCE(NewObjects.SchemaName, ''))
Tagged:
Comments
This problem is a result of our polling query, which regularly runs for each user to look for changes to your database objects. This runs for two purposes:
1- to display the 'blue blob' next to an altered object
2- to display the name of the user who made the change, if you're working in shared mode.
Unfortunately, once a user clicks on a linked DB, the poller will continue to run for that DB until SSMS is closed.
There are couple of things you can try: Reducing the polling interval - this will mean the blue blobs will take longer to appear but a refresh of the commit tab will bring them right up; Also if you don't check changes to static data will also help. To change the settings, you'll need to go to global options:
Also making sure only people that need rights to the commit history have rights to the trace DB could also limit the amount of deadlocks.
Tianjiao Li | Redgate Software
Have you visited our Help Center?