Stored Procedure is getting executed twice simultaneously in ADF pipeline

Mounika Moni Patro 0

I have an ADF pipeline in which a stored procedure is triggered. Sometimes the sp is executed twice almost simultaneously with difference of few seconds for single adf run. First sp execution is getting successful but the second one is failing with deadlock error. Not sure why this duplication is happening. Need support here.

Smaran Thoomu 16,005 Reputation points Microsoft Vendor

2024-08-09T13:06:06.0233333+00:00

@Mounika Moni Patro We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mounika Moni Patro 0 Reputation points

2024-08-12T09:18:04.8566667+00:00
Hello Smaran,

Retry policy is 0. First execution of the sp is successful every single time. Issue is with the duplicate one which is starting 10-20 seconds after the first execution in the same trigger.

No concurrent runs. In our pipeline we check before sp if there are any concurrent runs of the same pipeline.

This is a scheduled trigger. There are no external dependencies nor sp is used in other pipeline.

In past 6 months out of many runs this happened less than 10 times. But when it happens it is failing even though data is loaded properly due the duplicated sp execution. This is misguiding due to incorrect status of the pipeline run.

Let me know if there are any other possibilities other than the below mentioned.

1 answer

Smaran Thoomu 16,005 Reputation points Microsoft Vendor

2024-08-08T13:45:49.2933333+00:00
Hi @Mounika Moni Patro
Thanks for the question and using MS Q&A platform.

As I understand you are experiencing an issue with duplicate stored procedure executions in your ADF pipeline, which is causing a deadlock error. Here are some steps you can take to troubleshoot and resolve the issue:

ADF Pipeline Retry Policy: ADF has a built-in retry policy for failed activities. If the stored procedure execution fails, ADF might retry the activity, causing the duplicate execution.

Concurrent Execution: If your pipeline is configured to run concurrently, multiple instances of the stored procedure might be executed simultaneously, leading to the deadlock error.

Triggering Mechanism: The way you're triggering the stored procedure might be causing the duplication. For example, if you're using a trigger-based approach, it's possible that the trigger is firing multiple times.

Check the ADF Pipeline Run History: Go to the ADF portal, navigate to the pipeline run history, and check if there are any retry attempts or concurrent runs that might be causing the duplicate execution.

If you're still struggling to resolve the issue, please provide more details about your pipeline, stored procedure, and triggering mechanism, and I'll do my best to help you troubleshoot!

I hope this helps! Let me know if you have any further questions.
Please sign in to rate this answer.
Smaran Thoomu 16,005 Reputation points Microsoft Vendor

2024-08-12T06:42:36.4833333+00:00

@Mounika Moni Patro Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Mounika Moni Patro 0 Reputation points

2024-08-12T09:11:43.4933333+00:00

Hello Smaran,

Retry policy is set to 0 for the pipeline. So there is no way it would execute again. And in my scenario duplicate trigger is not due to failure of first execution. First time execution of stored proc is successful every single time. Issue is with the duplicate run which is starting 10 to 20 seconds after the first one has started. I need help in understanding why the same sp is executing again when it is already running for that trigger.

There is no concurrent execution as well. My pipeline is pretty straightforward. There is an ADF pipeline within which a sp trigger is scheduled. No dependencies nor this sp is scheduled elsewhere.

It is not trigger based. Just a sequential activity in an ADF pipeline.

My ADF pipeline is a scheduled trigger. It contains some sequential activites. In fact before this sp trigger we have condition to check if there are any concurrent runs for the same pipeline. And we have logged the timestamps of the stored procedure runtimes. Difference is less than 20 seconds.

In the last 6 months out of many runs this happened to less than 10 runs where the same sp is executed twice in the same trigger without any external dependencies or inputs.

Smaran Thoomu 16,005 Reputation points Microsoft Vendor

2024-08-12T14:02:29.21+00:00

Mounika Moni Patro Thank you for sharing the details. Could you please check below once. And share updates to understand issue better. Thank you.

Is your Stored procedure activity inside for each activity?

If yes, could you please check, how may items you have in array which you are passing to For each activity?

Mounika Moni Patro 0 Reputation points

2024-08-13T06:53:08.2233333+00:00

Hello Smaran,

SP is not within for each loop. It is in a data pipeline directly as an activity.

Smaran Thoomu 16,005 Reputation points Microsoft Vendor

2024-08-13T08:17:25.3133333+00:00

Hi Mounika Moni Patro

Thanks for the clarification. Could you please check out this similar thread that might help you: https://learn.microsoft.com/en-us/answers/questions/464688/adf-stored-procedure-executed-multiple-times

I hope this helps. Please let me know if you have any questions.

Mounika Moni Patro 0 Reputation points

2024-08-13T12:40:17.3166667+00:00

I don't think that will solve the issue. Because my stored proc doesn't call any other sp. It simply loads data from one table to another. According to the solution in the above link there is nothing to split in my stored proc. But I did find similar question in the forum -https://learn.microsoft.com/en-us/answers/questions/1759842/azure-data-factory-executes-stored-procedure-twice
According to the above it could also be a glitch in ADF.

Is it a possibilty?

Smaran Thoomu 16,005 Reputation points Microsoft Vendor

2024-08-13T13:29:57.13+00:00

Mounika Moni Patro We are checking with our internal team regarding your request and will get back to you as soon as possible with an update. Thank you for your patience and understanding.

Smaran Thoomu 16,005 Reputation points Microsoft Vendor

2024-08-14T07:46:31.3566667+00:00

Mounika Moni Patro I agree that this issue looks strange, and I wasn't able to reproduce this issue. If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.

phemanth 10,635 Reputation points Microsoft Vendor

2024-08-19T13:58:14.0366667+00:00

Mounika Moni Patro We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Stored Procedure is getting executed twice simultaneously in ADF pipeline

1 answer

Your answer