Hi Crm Folks,
I stuck in a condition when I run a report (incident table report with join activities and others tables), another query automatically updates activities and creates a deadlock condition.
My report query contains incident table with several joins.
Query execution time of 330.0 seconds exceeded the threshold of 30 seconds. Thread: 51; Database: abc; Server:xyz; Query:
SET DEADLOCK_PRIORITY LOW
BEGIN TRANSACTION
UPDATE ActivityPointerBase
SET
DeliveryAttempts = COALESCE(DeliveryAttempts, 0) + 1,
DeliveryLastAttemptedOn = '03/22/2016 11:33:39',
PostponeActivityProcessingUntil = '03/22/2016 12:03:39'
OUTPUT
INSERTED.ActivityId,
INSERTED.ActivityTypeCode,
INSERTED.Subject,
INSERTED.Description,
INSERTED.PriorityCode,
INSERTED.EmailAttachmentCount,
INSERTED.SenderMailboxId,
INSERTED.DeliveryAttempts,
INSERTED.ConversationIndex,
INSERTED.MessageId,
INSERTED.InReplyTo
WHERE
ActivityId
IN
(SELECT
TOP(25) ActivityPointerBase.ActivityId
FROM
ActivityPointerBase WITH (READPAST, READCOMMITTEDLOCK, UPDLOCK)
LEFT OUTER JOIN
MailboxBase WITH (NOLOCK) ON (ActivityPointerBase.SenderMailboxId = MailboxBase.MailboxId)
WHERE
(
(
MailboxBase.EnabledForOutgoingEmail = 1 AND
MailboxBase.PostponeSendingUntil <= '03/22/2016 11:33:39' AND
1 = 0
)
) AND
ActivityPointerBase.ActivityTypeCode = 4202 AND
ActivityPointerBase.StateCode = 1 AND
ActivityPointerBase.PostponeActivityProcessingUntil IS NOT NULL AND
ActivityPointerBase.PostponeActivityProcessingUntil <= '03/22/2016 11:33:39'
ORDER BY
ActivityPointerBase.DeliveryPriorityCode DESC,
ActivityPointerBase.ActualEnd ASC
)
COMMIT.
May i know how can I resolved this issue. If someone knows about it.
Thanks & Regards,
Vivek Gupta
Vivek gupta