Flow gets stuck when multiple instances are triggered

[Conax]

Hi, we have a process requirement that when data in an Excel file is received, the rows need to be copied to a SharePoint list (let's call this List 1). This can be several hundreds of rows at a time.

After the rows are copied to the SharePoint list, some transformation, plus calculations needs to happen, and the result be inserted into another SharePoint list (let's call this List 2).

So an automated flow was created to be triggered by new item created in List 1, to transform the data into List 2.

Another automated flow was created to be triggered by new item created in List 2, to query some data from yet another SharePoint list for performing the calculations and then update the item in List 2.

Because several hundres of rows are being created in List 1, multiple instances of the List 1 flow are triggered to create items in List 2, which in turn caused multiple instances of the flow for List 2 to trigger. Eventually some instances ended up in Running status for hours and appeared to be stuck.

I enabled concurrency for both flows, so each can have 5 instances running at the same time, but this only improved the situation to some extent, as some instances still appeared to be stuck for hours.

Then I re-configured the concurrency so that each flow can have only 1 instance running at a time. This seems to at least get rid of the Running instances getting stuck issue. However, it also means it takes hours for all the list items to complete the update. And sometimes I also get the following error:

Error from token exchange: Runtime call was blocked because connection has error status: Enabled| Error, and sharepointonline is in the block list. Connection errors: [ParameterName: token, Error: Code: Unauthorized, Message: 'Failed to refresh access token for service: sharepointonlinecertificatev2. Correlation Id={a correlation id here}, UTC TimeStamp={date and time here}, Error: Failed to acquire token from AAD: An internal error occurred.

Edit the flow and resaves it seems to solve that issue, but right now I'm not sure if was caused by the flow instances being triggered too many times, and if this error will happen again.

Does any one has any idea why flow instances can get stuck when multiple instances are running?

Thank you.

[leyburn19]

Unless you need the result immediately I would look at a schedule for the update to list 2 rather than trigger when a record is created.  un the schedule at intervals that reduce the clash in the API calls. The way I dot is to have an additional number field in List called something like status.  Its Default is set to 1 every time a record is created.  When my schedule runs,  it finds all records where Status =1 and the does the actions I need.  The last part of the process it updates records status to 2 so they are not picked in the next run

[Conax]

Thank you @leyburn19 for your suggestion. I have changed the flow to be scheduled, and picks up just 75 items to process on each run. I did a test run first to see how long it would take to process 75 items. It took nealy 15 minutes. So I scheduled it to execute every 30 minutes.

However, on first two subsquent runs, it took 9 and 10 minutes, and the third took 3 hrs 19 minutes, then 6 hours 47 minutes, then 2 hours 52 minutes. The current run has been 1 hour 13 minutes and still running.

I've also notice some odd things from the flow history, like a Condition action took 3 hours to execute, but all actions under it took just 0 to 2 seconds to execute.

Sometimes error message like the following would appear in Condition action history and SharePoint action history:

Unexpeccted error. Unable to fetch https://prod-03.australiasoutheast.logic.azure.com:443/workflows/<some guid>/runs/<some long numer>/actions/<action name>...

I've googled quite a bit tonight regarding flow stuck at Running. People had this problem before, but no concret answers to that. One suggested that it's due to flow limitation and upgrade the licensing will solve the issue. But I wish Microsoft could just include in the error message like 'You've exceeded your flow run threshold....', then we can be certain that is the issue, and convince the customer/boss to upgrade the license.

[Conax]

I am leaning towards believing the issue is caused by the limitation under the Power Automate licence that's being used now too, as it seems like when I save the flow as new and then remove the original flow, it will run fine for a while before slowing down again. This would mean that threshold had been reset for the newly saved flow, and started counting until it reaches the limit and then slow down the processing.