Skip to main content

Notifications

Microsoft Dynamics AX forum
Suggested answer

Need help with a blocking SQL statement

Posted on by 5

(@P1 int)UPDATE T1 SET SHIPMENTSERIES=T2.SHIPMENTSERIES,ORIGIN=T2.ORIGIN,STONECOUNT=T2.STONECOUNT,WEIGHT=T2.WEIGHT,POTVALUE=T2.POTVALUE,RECVERSION=@P1 FROM IMXSSRSTASKHISTORYLAST T1 CROSS JOIN IMXTASKHISTORYTABLE T2 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'dtc')) AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'dtc')) AND ((((((T2.POT=T1.POT) AND (T2.LINENUMBER=T1.LINENUMBER)) AND (T2.TASK=T1.TASK)) AND (T2.MILESTONE=T1.MILESTONE)) AND (T2.PRODUCTSTATUS=T1.PRODUCTSTATUS)) AND (T2.SIZE_=T1.SIZE_)))

This query creates a blocking every time it runs. How to resolve this blocking issue so that it never occurs again.

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 290,782 Super User 2024 Season 2 on at
    RE: (@P1 int)UPDATE T1 SET SHIPMENTSERIES=T2.SHIPMENTSERIES,ORIGIN=T2.ORIGIN,STONECOUNT=T2.STONECOUNT,WEIGHT=T2.WEIGHT,POTVALUE=T2.POTVALUE,RECVERSION=@P1 FROM IMXSSRSTASKHISTORYLAST T1 CROSS JOIN IMXTASKHISTORYTABLE T2 WHERE ((T1.PARTITION=5637144576) AND

    Hi Nidhi

    This query looks like it is updating records in a partner add-on or customization. Can you contact the partner or developer who created this? I can't map the table and fields with a standard process in AX 2012.

    Can you also try to use a more meaningful title for your question instead of copying a SQL statement?

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: (@P1 int)UPDATE T1 SET SHIPMENTSERIES=T2.SHIPMENTSERIES,ORIGIN=T2.ORIGIN,STONECOUNT=T2.STONECOUNT,WEIGHT=T2.WEIGHT,POTVALUE=T2.POTVALUE,RECVERSION=@P1 FROM IMXSSRSTASKHISTORYLAST T1 CROSS JOIN IMXTASKHISTORYTABLE T2 WHERE ((T1.PARTITION=5637144576) AND

    Hi Nidhi,

    First, you have to understand why blocking issues occur.

    If it's because you are trying to update the same record in several sessions, you need to think about how to avoid it and don't run an update for the same record in several parallel sessions.

    If it's because you are trying to update a lot of records in the same transaction you have 2 options

    1. Split update to small transactions if possible

    2. Disable lock escalation for table and disable page locks for all indexes for IMXSSRSTASKHISTORYLAST. You can check on the Internet for script how to do it for SQL, but keep in mind if you change index or create a new one, you should re-do this step again

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,782 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,067 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,150

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans