Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Need help with a block...
Finance | Project Operations, Human Resources, ...
Suggested answer

Need help with a blocking SQL statement

(0) ShareShare
ReportReport
Posted on by Nidhi Singh 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... 291,280 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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans