Deadlocks using shared model

MBBWMBBW Posts: 16
SSC 3.1.0.4583
SQL Server 10.50.4000

Hello,

using SSC shared model with two users there are a lot of deadlocks even if the users do not perform any action. At least every 4 minutes the following event is logged. The referred object is dbo.RG_AllObjects.
System.Data.SqlClient.SqlException: Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    The RG_AllObjects table is the solution in SQL Source Control 3.1 that tries to put right the problems with the "unknown" user audits in the shared model of SQL Source Control. It persists the default trace to the tempdb database.

    I can't see how you would get a deadlock, though - SQL Source Control should assign a unique ID to every object (the AllObjectsId column).

    Maybe it would be a good idea to set trace flag 1204 and get SQL Server to print the deadlock information into the SQL Server error log. Then at least you can see what type of deadlock it is and what query was being executed.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi,

    Apparently this is not an isolated issue - the RG_ database and the SPs that clear unused objects have been causing problems, so we are looking at a better way of implementing the shared model.
  • Hello,

    Thank you for the answer.

    Yes, it is the procedure dbo.RG_WhatsChanged processed by each user causing the deadlocks.

    I am getting the deadlock information by WMI events, so I will not blow up my SQL Server error log by using the trace flag. And this is the problem of the current shared model version: Anyone who logs deadlock information will get thousands of log records (or emails like me) due to the SSC deadlocks.

    So, please let me know, when you will deploy a new version of the shared model. We want to use the shared model.

    Thank you.
  • SSC 3.1.0.4829
    SQL Server 10.50.4000

    The deadlock problems within the shared model database still occur with the new minor release 3.1.0.4829. We also get timeout messages opening the Commit Changes tab and for some objects (tables, procedures) the Changed By column shows "Unknown".

    So we need a solution for the shared model as soon as possible. We are not using the trial version, we have paid for the product.
  • I'm sorry to hear you're still having trouble with this. I've had a quick look through our ticketing system and we're not really seeing reports of deadlocks from other users. I wonder if you could confirm a couple of things so I can check with the development team for some things to try?

    - can you confirm ALL users have updated to the new build? A mixture of versions will cause problems.

    - is the SQL Instance you're working on local to you (i.e. on the same network) or are you running over a WAN link to a server out on the internet somewhere?

    - how many users do you have using SQL Source Control typically?

    - how many objects are there in your database in total?
    Systems Software Engineer

    Redgate Software

  • Here are my answers:

    - can you confirm ALL users have updated to the new build? A mixture of versions will cause problems.
    Yes, all developers use build SSC 3.1.0.4829.

    - is the SQL Instance you're working on local to you (i.e. on the same network) or are you running over a WAN link to a server out on the internet somewhere?
    The SQL instance is on the same local network.

    - how many users do you have using SQL Source Control typically?
    Only two developers.

    - how many objects are there in your database in total?
    We have deadlocks with both a small test databases (10 objects) and big databases (hundreds of objects).
  • Thanks for your reply. Hitting deadlocks with such a low number of users/objects is rather unusual, however could you try increasing the polling interval to see if it helps? To do this, you'll need to edit "RedGate_SQLSourceControl_Engine_EngineOptions.xml" which you'll find in c:\users\<username>\appdata\local\red gate\sql source control 3.

    Set the trace interval time as follows:

    <DefaultTraceMinimumInterQueryTimeInMillis>60000</DefaultTraceMinimumInterQueryTimeInMillis>

    (you may need to add that line if it's not already there).

    Once done, ensure you restart SSMS; also, you will need to perform this change on each PC / for each user.
    Systems Software Engineer

    Redgate Software

  • After adding the node to the configuration file it is acceptable. There are still deadlocks once in a while but I think that we can work with this configuration.

    What is your default polling interval, 1000 ms?

    Thank you.
  • I'm not sure off the top of my head- but I believe the next release will default it to nearer the setting you are now using. Even if you don't encounter deadlocks, a lot of people like to lower it simply to reduce load on their servers.
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.