none
deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Question

  • I am not sure what causing this issue but I do see some deadlocks in CRM Trace. I guess this is caused when the users using Advanced Find in CRM. I verified the SQL Event logs but I didnt find anything. Is any one have an idea about this issue. Is this the application side issue or the SQL?

    >Exception when executing query: select  DISTINCT  top 51 activitypointer0.Subject as 'subject', activitypointer0.ActivityId as 'activityid', activitypointer0.ScheduledStart as 'scheduledstart', activitypointer0.RegardingObjectId as 'regardingobjectid', activitypointer0.PriorityCode as 'prioritycode', activitypointer0.ScheduledEnd as 'scheduledend', activitypointer0.ActivityTypeCode as 'activitytypecode', activitypointer0.RegardingObjectIdDsc as 'regardingobjectiddsc', activitypointer0.RegardingObjectIdName as 'regardingobjectidname', activitypointer0.RegardingObjectIdYomiName as 'regardingobjectidyominame', activitypointer0.RegardingObjectTypeCode as 'regardingobjecttypecode' from ActivityPointer as activitypointer0 join ActivityParty as aa on (activitypointer0.ActivityId  =  aa.ActivityId and (aa.PartyId = '349da56e-d8c4-df11-a2c8-005056b3631f')) where ((activitypointer0.StateCode in (0, 3)) and (activitypointer0.ActivityTypeCode != 4406 or activitypointer0.ActivityTypeCode is null) and (activitypointer0.DeletionStateCode in (0)) and (activitypointer0.OwningUser = '349da56e-d8c4-df11-a2c8-005056b3631f' or activitypointer0.OwningBusinessUnit in (select SubBusinessId from BusinessUnitMap where BusinessId = '81389c80-3cb2-df11-9197-005056b3631f') or activitypointer0.ActivityId in (select POA.ObjectId from PrincipalObjectAccess POA join SystemUserPrincipals sup on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = '349da56e-d8c4-df11-a2c8-005056b3631f' and POA.ObjectTypeCode = 4200 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1))) order by activitypointer0.ScheduledEnd asc Exception: System.Data.SqlClient.SqlException: Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.HasMoreRows()
    at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
    at Microsoft.Crm.BusinessEntities.QueryProcessObject.CreateResultEntitiesFromDataReader(IDataReader reader, EntityExpression entityExpression, ResultEntityDescription resultEntityDescription, ExecutionContext context, Boolean& moreRecords)
    at Microsoft.Crm.BusinessEntities.QueryProcessObject.RetrieveDataQueryAsResultXml(EntityExpression entityExpression, QueryStrategyType strategyType, ExecutionContext context)

    Friday, March 04, 2011 9:52 PM

Answers

  • Deadlocks can be almost anything.  It could be a hardware resource bottleneck on your SQL Server.  Could be configuration of SQL Server.  Could be poor design of the application (i.e. creation of conflicting transactions that the block each other).  Could just be long running queries with escalated locks that get in each others way (i.e. a large enough select will automatically escalate to a shared table lock internally in SQL Server which may then conflict with exclusive record locks set for long/slow running updates/inserts elsewhere).  This is something that you really need your DBA involved in.  At its heart this will start with analyzing what's going on inside SQL Server.  It MAY be that you end up making app level changes to address but you need to identify the problem via SQL Server first.

    Note: what you have above is only half the problem, that's the deadlock victim but there was a second process involved that contributed to the deadlock and survived.

    Some additional information (note that this really should be approached by a DBA, novice SQL Server users will pull their hair out trying to troubleshoot deadlocks)

    http://www.mssqltips.com/tip.asp?tip=1036

    http://msdn.microsoft.com/en-us/library/aa937573(v=sql.80).aspx

    Friday, March 04, 2011 9:59 PM