Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Unanswered

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

Posted on by 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;

  • 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 225,588 Super User 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 225,588 Super User 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 225,588 Super User 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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,986 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans