Deadlocks using shared model

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace, andy.campbell.smith

Deadlocks using shared model

Postby MBBW » Thu Sep 27, 2012 1:37 pm

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.

Code: Select all
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. 
MBBW
 
Posts: 12
Joined: Tue Mar 08, 2011 1:36 pm

Postby Brian Donahue » Tue Oct 02, 2012 10:22 am

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 Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Mon Oct 08, 2012 4:01 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby MBBW » Mon Oct 08, 2012 4:41 pm

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.
MBBW
 
Posts: 12
Joined: Tue Mar 08, 2011 1:36 pm

Postby MBBW » Tue Oct 30, 2012 1:00 pm

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.
MBBW
 
Posts: 12
Joined: Tue Mar 08, 2011 1:36 pm

Postby james.billings » Wed Oct 31, 2012 4:38 pm

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?
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby MBBW » Wed Oct 31, 2012 6:46 pm

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).
MBBW
 
Posts: 12
Joined: Tue Mar 08, 2011 1:36 pm

Postby james.billings » Wed Oct 31, 2012 6:54 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby MBBW » Fri Nov 02, 2012 5:10 pm

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.
MBBW
 
Posts: 12
Joined: Tue Mar 08, 2011 1:36 pm

Postby james.billings » Mon Nov 05, 2012 12:47 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Source Control 3

Who is online

Users browsing this forum: No registered users and 0 guests