Red Gate forums :: View topic - Deadlocks using shared model
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 3
SQL Source Control 3 forum

Deadlocks using shared model

Search in SQL Source Control 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
MBBW



Joined: 08 Mar 2011
Posts: 12

PostPosted: Thu Sep 27, 2012 1:37 pm    Post subject: Deadlocks using shared model Reply with quote

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:
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. 
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Tue Oct 02, 2012 10:22 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Mon Oct 08, 2012 4:01 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
MBBW



Joined: 08 Mar 2011
Posts: 12

PostPosted: Mon Oct 08, 2012 4:41 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
MBBW



Joined: 08 Mar 2011
Posts: 12

PostPosted: Tue Oct 30, 2012 1:00 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Oct 31, 2012 4:38 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
MBBW



Joined: 08 Mar 2011
Posts: 12

PostPosted: Wed Oct 31, 2012 6:46 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Oct 31, 2012 6:54 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
MBBW



Joined: 08 Mar 2011
Posts: 12

PostPosted: Fri Nov 02, 2012 5:10 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Mon Nov 05, 2012 12:47 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group