Deadlocks using shared model
MBBW
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.
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
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.
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.
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.
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.
- 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?
Redgate Software
- 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).
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.
Redgate Software
What is your default polling interval, 1000 ms?
Thank you.
Redgate Software