I had a question from my blog (thanks Alex!) on SQL deadlocks and error messages like the following on a busy server.
System.Data.SqlClient.SqlError: Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
You will also see error id 7747 in the application event log.
This can be an issue with systems that are quite stressed and in all cases I have seen relates to the process that selects the queue jobs for processing. It does not break anything as such and no data is lost – but processing of queue jobs is delayed (but as the system is very busy they probably wouldn’t have processed quickly anyway!).
Deadlocks occur when two transactions interact in such a way that one requires a resource that the other has locked, and vice versa. Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists. SQL Server selects one of the transactions as the victim and ends it – and posts the above error. See the SQL Server Books Online for more details.
In Project Server 2007 you can monitor activity using perfmon, and the counters include SQL retries per minute for both the Project and Timesheet queues. You can also modify the queue settings which can reduce the occurrence or behavior of the deadlocks. We don’t have any prescriptive guidance yet on suggested changes, but certainly reducing the number of threads, increasing the polling interval, or increasing the SQL retry intervals would likely reduce the number of deadlocks you see. However, these changes will also reduce the throughput of your queue – particularly when processing light weight jobs. If you see the deadlock behavior at specific time of day only – and want to change queue settings to suit workload you could even use the QueueSystem web service to change the settings (using the SetQueueConfiguration method).
I’m not sure if anyone will really want to micro-manage their queue in this way – or what the overall throughput benefits would be – but the option is there.
Technorati Tags: Project Server 2007
I have tried all this but it is not solving the issue. However I have found on web that changing Maximum Degree of Parallelism from 0 to 1 can resolve the problem. I cannot try it before i have some certainty that this can resolve the problem.
Have experienced something like this before? and does it work?
I don't have a crystal ball - so cannot give any such assurance. I would suggest opening a support incident and then we can help to isolate the problem. My gut feeling is that changing MAXDOP from 0 to 1 could help as it is then constricting the parallel queries that might deadlock - but it really depends what is getting deadlocked.