locked
Deadlock condition update activities problem when ran the report RRS feed

  • Question

  • 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

    Saturday, March 26, 2016 2:31 PM