Azure data factoty - stored procedure executed by a lookup activity ran successfully but failed to commit to DB on 13 Jan 2025

Bill Chie 0

Hi,

I have several Azure Data Factory pipelines that run stored procedures using lookup activities. On January 13, 2025, these activities executed successfully without any errors, but the records were not committed to the database. The same activities could be rerun successfully without any changes after 9 AM on January 14, 2025.

I checked across different clients (i.e., different ADFs and DBs) and found the same symptoms. When the lookup activity isolation level is blank, there are no issues. However, the problem occurs when the isolation level is set to "read committed."

Would like to know what can be the possibly cause and how to prevent this from happening again?

Regards

Bill

Bill Chie 0 Reputation points

2025-01-17T10:26:46.9833333+00:00

Hi Chandra,

Thank you for getting back to me. I have a few questions regarding how the "read committed" isolation level could lead to scenarios where a stored procedure appears to execute successfully but fails to commit changes to the database.

I understand that "read committed" uses shared locks on SELECT statements, which may cause blocking and lead to transaction failure. However, the issue we are facing is that, in such cases, the transaction ends successfully without committing data to the database, and no error is raised for us to handle.

This behavior renders solution like error handling, implementing retry logic, or database monitoring ineffective since there are no errors or long-running transactions detected.

I believe migrating to the Stored Procedure activity in ADF might resolve the issue. However, I would like to better understand the exact conditions under which this issue occurs. The fact that this behavior is consistently observed across multiple clients at specific time intervals, and that I can reproduce it in our test environment (even without other concurrent transactions), makes it challenging to attribute the issue solely to shared locks.

Best regards
Bill
Bill Chie 0 Reputation points

2025-01-17T10:34:21.7466667+00:00

deleted as duplicate
Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-01-17T19:28:40.6366667+00:00

@Bill Chie

Thank you for your detailed explanation, you are observing a scenario where a stored procedure completes without raising errors, yet the expected data changes are not committed. This can occur due to specific conditions in transaction management, shared locks, or stored procedure logic.

Key Insights on "Read Committed"

Shared Locks - Under "read committed," shared locks prevent data being read from being modified until the read completes. However, these locks are short-lived and released once the read operation finishes, unless explicitly held longer by an ongoing transaction.

Risk of Silent Rollbacks - If a stored procedure encounters an issue (e.g., constraint violation or implicit rollback) without proper error propagation, the transaction can silently fail without committing changes.

Why Stored Procedures Might Not Commit Changes

Implicit Rollbacks - If the stored procedure relies on implicit transactions, errors (e.g., constraint violations or deadlocks) might trigger an automatic rollback without explicitly notifying the caller.

TRY...CATCH Blocks - Error handling in the procedure may catch errors internally but fail to re-raise them for the caller to handle. This creates the illusion of successful execution.

Deadlocks or Blocking - Even in low-concurrency environments, resource contention or lock escalation can cause transient blocking, delaying or preventing commits.

Why Error Handling and Monitoring May Not Help

No Raised Errors - Silent failures (e.g., implicit rollbacks) do not trigger error-handling mechanisms in ADF, such as retry logic or failure paths.

Transient Blocking - Without tools monitoring live locks or contention, short-lived issues might go unnoticed in logs or dashboards.

Conditions Leading to the Issue

Given that you can reproduce this behavior consistently across multiple clients and at specific times, consider the following potential conditions:

Database Load- High load on the database at specific times may lead to increased contention for resources, causing blocking scenarios that affect transaction commits.

Stored Procedure Logic - Review the logic within your stored procedures to ensure that they handle all potential error conditions and explicitly commit transactions.

Isolation Level Effects - While "read committed" is generally safe, consider testing with different isolation levels (like "snapshot isolation") to see if it changes the behavior.

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-01-20T06:32:07.5533333+00:00

@Bill Chie

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.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Bill Chie 0 Reputation points

2025-01-20T10:12:05.0133333+00:00

Hi Chandra,

Regarding your point about "reproducing this behavior consistently across multiple clients and at specific times," I’d like to clarify that the issue is not tied to any particular time interval during the day or week. The system had been functioning correctly as far as I can recall, but we suddenly started encountering errors across all environments, including our Test environment—which, notably, should have no active processes on the specified day. Moreover, we have been unable to reproduce the issue since.

The primary concern is understanding the root cause of the "risk of silent rollbacks." In our TRY...CATCH logic, we’ve implemented error rethrows and logging to a table. However, neither mechanism captured an error, and the data was not committed, which points to a "silent rollback." This behavior is unexpected and concerning. I’d appreciate your insights into what might be causing this issue and how we can prevent it from happening again in the future.

Looking forward to your thoughts.

Best regards
Bill
Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-01-20T19:37:25.2833333+00:00

@Bill Chie

Thanks for clarifying the details. The fact that the issue appeared suddenly across all environments, including your idle test environment, and then disappeared strongly suggests a transient issue affecting the commit of implicit transactions due to ADF's connection pooling.

The "silent rollback" is not an explicit ROLLBACK due to an error in your stored procedure. Instead, it's the implicit transaction simply not being committed because a transient disruption occurred after the stored procedure completed but before the commit could be finalized. This disruption could have been a very brief network interruption, a transient issue within the Azure platform, or a short-lived resource contention within the Azure infrastructure.

Your TRY...CATCH blocks are working correctly; the problem is occurring outside their scope, during the commit phase.

The only reliable solution is to migrate to the Stored Procedure activity in ADF. This provides explicit transaction management, eliminating the reliance on implicit transactions and preventing these silent non-commits.

Given the cross-environment impact, we strongly recommend checking the Azure Service Health dashboard for any reported incidents around the time of the issue. This could confirm a platform-level transient problem. We also recommend enhanced network monitoring between ADF and your database server.

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-01-21T21:01:53.24+00:00

@Bill Chie

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.
Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-01-22T21:58:42.7533333+00:00

@Bill Chie

Following up to see 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.

1 answer

Chandra Boorla 7,960 Reputation points Microsoft Vendor

2025-01-16T19:32:50.49+00:00
@Bill Chie

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

As I understand that the issue you encountered with the Azure Data Factory lookup activity may be related to the isolation level set to "read committed." When using this isolation level, transactions may not see uncommitted changes made by other transactions, which could lead to situations where the stored procedure appears to execute successfully but does not commit the changes to the database due to locks or other transaction-related issues.

To prevent this from happening in the future, consider the following approaches:

Use Stored Procedure Activity - The most recommended approach is to migrate from using Lookup activities to the dedicated Stored Procedure activity in ADF. This activity is designed specifically for executing stored procedures and provides better control over transaction management and error handling.

Error Handling - Implement robust error handling and logging in your ADF pipelines to capture any anomalies during execution. This can help identify if there are specific conditions leading to the failure to commit.

Implement Retry Logic - Add retry logic in your ADF pipelines. If a stored procedure fails to commit due to blocking, you can retry the execution after a short delay. This can help mitigate transient issues.

Use Snapshot Isolation - If your database supports it, consider using snapshot isolation. This allows transactions to read a consistent snapshot of the data without being blocked by other transactions, reducing the likelihood of blocking issues.

Database Monitoring - Monitor your database for locks and long-running transactions that might interfere with the execution of your stored procedures. Tools like SQL Server Profiler or Azure SQL Analytics can provide insights into transaction behavior.

For further details, please consult the following Microsoft documentation, which may provide valuable insights:

Transform data by using the SQL Server Stored Procedure activity in Azure Data Factory or Synapse Analytics

Lookup activity in Azure Data Factory and Azure Synapse Analytics

If the issue persists after switching to the Stored Procedure activity and implementing monitoring, please provide the database logs from the affected time period. We will assist you further.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

Azure data factoty - stored procedure executed by a lookup activity ran successfully but failed to commit to DB on 13 Jan 2025

1 answer

Your answer