Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX forum / SQL Blocking when batc...
Microsoft Dynamics AX forum
Unanswered

SQL Blocking when batch job is run in parallel in multiple legal entities

(0) ShareShare
ReportReport
Posted on by SarikaS 35

Hello,

So we have a requirement to delete inactive addresses from LogisticsPostalAddress table ,based on some parameter values,

But when i'm trying to run this batch job in multiple companies in parallel , it is causing blocking 

I want to run it in parallel to reduce the time taken by batch job to finish , as of now it is set to run in dependency 

Below is the sample code  

select firstonly RecId from logisticsLocationRole where logisticsLocationRole.Type == _dataContract.parmAddressPurpose();

while select validTimeState(validFromDT, validToDT) ValidTo, Location from logisticsPostalAddressLoc
where logisticsPostalAddressLoc.ValidTo != DateTimeUtil::maxValue()
join logisticsLocation index hint cnlRecIdDescIdx where logisticsLocation.Description == _dataContract.parmAddressName()
&& logisticsLocation.RecId == logisticsPostalAddressLoc.Location
join Location, IsPrimary, party, RecId from dirPartyLocation where dirPartyLocation.Location == logisticsLocation.RecId
&& dirPartyLocation.IsPrimary == NoYes::No
exists join custTable where custTable.party == dirPartyLocation.party
exists join dirPartyLocationRole where dirPartyLocationRole.PartyLocation == dirPartyLocation.RecId
&& dirPartyLocationRole.LocationRole == logisticsLocationRole.RecId

{
ttsBegin;
logisticsLocation.selectForUpdate(true);
if(logisticsLocation.validateDelete())
{
logisticsLocation.delete();
totalAddresssesDeleted++;
}
ttsCommit;

Categories:
Development / Customization / SDK
Supply Chain
  • SarikaS Profile Picture
    SarikaS 35 on at
    RE: SQL Blocking when batch job is run in parallel in multiple legal entities

    goshoom : Hello Martin , thank you for the reply , so if u check my query I'm checking whether a record exists in custtable and custtable is not a shared table , its basically I want to delete records from all legal entities I have in my project that is why we need to have it running in all legal entities ,

    My simple ask is why running this batch job in parallel in multiple entities is creating blocking and also not even a single record gets deleted, and when I'm setting it in dependency (one by one legal entity ) no blocking is caused .

    I need to get the root cause of it , is it because Logistics Location is a shared table?

  • Martin Dráb Profile Picture
    Martin Dráb 228,914 Most Valuable Professional on at
    RE: SQL Blocking when batch job is run in parallel in multiple legal entities

    I'm sorry, but this still doesn't tell us which query is blocked by which query and what is the locked resource.

    Your problems my suggest that running this process several times in parallel simply isn't a good idea. Consider again whether the benefit it worth the time that you'll need for further investigation.

  • SarikaS Profile Picture
    SarikaS 35 on at
    RE: SQL Blocking when batch job is run in parallel in multiple legal entities

     goshoom  : I can see there is a header block created but its coming from standard code which validates for Logistics Location delete , please find the screenshot below .

    Still I'm not sure what I can do to been able to run batch job in multiple legal entities parallel , since its a shared table.

    pastedimage1663052592014v1.png

  • Martin Dráb Profile Picture
    Martin Dráb 228,914 Most Valuable Professional on at
    RE: SQL Blocking when batch job is run in parallel in multiple legal entities

    You can see that the debugger can't give you any information about locks inside the database, therefore it's a wrong tool for your task.

    SQL Server can give you information about which query is blocked by which other query, which resources is locked, by which type of lock and this kind of things.

  • SarikaS Profile Picture
    SarikaS 35 on at
    RE: SQL Blocking when batch job is run in parallel in multiple legal entities

    goshoom  : Thank you so much for reply ,

    when I debugged I can see the blocking is caused by LogisticsLocation.delete() method (the super() of this method takes long time to execute ) ,i'm thinking it is because this table has lots of delete actions to be checked (restricted + cascade both) , also LogisticsLocation is a shared table

    I want this job to be run in parallel in all companies because it is taking a lot of time to execute (more than half a day ) for all companies.

    Business Requirement : we have an external Reltio interface used and  in some situation Customer address created by Reltio can be updated - then historic one needs to be removed out of historic table .

    Job is to be created to delete old addresses that are not active anymore.

    Please let me know how can I achieve this.

  • Martin Dráb Profile Picture
    Martin Dráb 228,914 Most Valuable Professional on at
    RE: SQL Blocking when batch job is run in parallel in multiple legal entities

    First of all, let me format your code. If you want to show code, it's good to have it easily readable.

    while select validTimeState(validFromDT, validToDT) ValidTo, Location from logisticsPostalAddressLoc
	where logisticsPostalAddressLoc.ValidTo != DateTimeUtil::maxValue()
	join logisticsLocation
		index hint cnlRecIdDescIdx
		where logisticsLocation.Description == _dataContract.parmAddressName()
		   && logisticsLocation.RecId == logisticsPostalAddressLoc.Location
	join Location, IsPrimary, party, RecId from dirPartyLocation
		where dirPartyLocation.Location == logisticsLocation.RecId
		   && dirPartyLocation.IsPrimary == NoYes::No
		exists join custTable
			where custTable.party == dirPartyLocation.party
		exists join dirPartyLocationRole
			where dirPartyLocationRole.PartyLocation == dirPartyLocation.RecId
			   && dirPartyLocationRole.LocationRole == logisticsLocationRole.RecId
{
	ttsBegin;
	logisticsLocation.selectForUpdate(true);
	if (logisticsLocation.validateDelete())
	{
		logisticsLocation.delete();
		totalAddresssesDeleted  ;
	}
	ttsCommit;
}

    You could optimize it a bit, such as by not fetching fields from DirPartyLocation table, because you don't need them. Also, test if the index hint really makes things better and not worse.

    I would expect your code to fail with pessimistic locking, because you set "selectForUpdate" after selecting data from database, therefore the lock couldn't be obtained.

    Anyway, the key question you need to answer is what is causing the blocking. It seems that a shared resource is locked and other instances of your code must wait before it's released. It might be the LogisticLocation itself, or a part of it, but also anything else used by delete() method or delete actions. You can't even start thinking about how to prevent the lock unless you know what's the lock that causes blocking.

    By the way, what is the business problem that you're trying to solve by deleting logistic locations?

  • SarikaS Profile Picture
    SarikaS 35 on at
    RE: SQL Blocking when batch job is run in parallel in multiple legal entities

    goshoom  : Any possible suggestions , I have also tried to use pessimistic lock on my logisticsLocation table but still no success.

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

Replay: Dynamics 365 Community Call ERP Edition | October 9, 2024

Another great gathering of our Community!

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

More Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 228,914 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,150

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans