Question about the behavior of copy data activity in Azure Data Factory

Question

Question about the behavior of copy data activity in Azure Data Factory

Calvin LI 20

May I know which method does the copy data activity in Azure Data Factory use - 'insert into' or 'direct-path insert'?

Calvin LI 20 Reputation points

2023-07-19T18:46:38.7233333+00:00

Thanks for your reply.

Got it, so right now i’ve encountered an issue of exclusive lock in Oracle table when running concurrent pipeline of using copy data activity to copy data from azure sql database to oracle database. Does it mean that the approach of data movement from azure sql database to oracle database use the “direct path insert”?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-07-19T19:20:07.08+00:00
I don't think the issue you are seeing is something to do with the methods used in the copy activity,

The exclusive lock issue is likely caused by the concurrent pipelines attempting to write to the same Oracle table simultaneously. This can cause contention and result in an exclusive lock being placed on the table.

Can you try the below and see if it helps?

Modify the pipelines to write to different tables in the Oracle database to avoid contention.

Modify the pipelines to write to the same table sequentially instead of concurrently.

Increase the lock timeout value in the Oracle database to allow for longer contention periods.

Optimize the Oracle database schema and queries to reduce contention

Change the isolation level in the Oracle database to a higher level as SERIALIZABLE to provide more guarantees about data consistency. This will reduce contention.

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-E8CBA9C5-58E3-460F-A82A-850E0152E95C

https://www.ibm.com/docs/en/imdm/11.6?topic=tdi-ora-00054-resource-busy-acquire-nowait-specified-timeout-expired-error
Calvin LI 20 Reputation points

2023-07-19T20:00:34.2833333+00:00

So, if i understand correctly, when running multiple pipeline with copy data activity at the same time to copy data to oracle database. It does cause contention and result in an exclusive lock (the no wait issue), right?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-07-19T20:11:47.53+00:00

Yes, you are correct. If multiple pipelines with copy activity run and try to write the same table concurrently, it causes contention.

You need to look into the Optimization methods at Oracle end to reduce the contention and performance issues.

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

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

Accepted answer

0 additional answers

Your answer

Calvin LI 20 Reputation points

2023-07-19T18:46:38.7233333+00:00

Thanks for your reply.

Got it, so right now i’ve encountered an issue of exclusive lock in Oracle table when running concurrent pipeline of using copy data activity to copy data from azure sql database to oracle database. Does it mean that the approach of data movement from azure sql database to oracle database use the “direct path insert”?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-07-19T19:20:07.08+00:00

I don't think the issue you are seeing is something to do with the methods used in the copy activity,

The exclusive lock issue is likely caused by the concurrent pipelines attempting to write to the same Oracle table simultaneously. This can cause contention and result in an exclusive lock being placed on the table.

Can you try the below and see if it helps?

Modify the pipelines to write to different tables in the Oracle database to avoid contention.

Modify the pipelines to write to the same table sequentially instead of concurrently.

Increase the lock timeout value in the Oracle database to allow for longer contention periods.

Optimize the Oracle database schema and queries to reduce contention

Change the isolation level in the Oracle database to a higher level as SERIALIZABLE to provide more guarantees about data consistency. This will reduce contention.

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-E8CBA9C5-58E3-460F-A82A-850E0152E95C

https://www.ibm.com/docs/en/imdm/11.6?topic=tdi-ora-00054-resource-busy-acquire-nowait-specified-timeout-expired-error
Calvin LI 20 Reputation points

2023-07-19T20:00:34.2833333+00:00

So, if i understand correctly, when running multiple pipeline with copy data activity at the same time to copy data to oracle database. It does cause contention and result in an exclusive lock (the no wait issue), right?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-07-19T20:11:47.53+00:00

Yes, you are correct. If multiple pipelines with copy activity run and try to write the same table concurrently, it causes contention.

You need to look into the Optimization methods at Oracle end to reduce the contention and performance issues.

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

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

Answer 1

Hello Calvin LI,

Welcome to the Microsoft Q&A forum.

The exact method used for data movement depends on the source and sink datasets.

Ex: when copying data between relational databases, it uses 'INSERT INTO' SQL statements.

PolyBase(like direct-path insert) will be used for bulk data movement.

To copy data from a source to a sink, the service that runs the Copy activity performs these steps:

Reads data from a source data store.
Performs serialization/deserialization, compression/decompression, column mapping, and so on. It performs these operations based on the configuration of the input dataset, output dataset, and Copy activity.
Writes data to the sink/destination data store.

Reference document: https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview

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

Share via

Question about the behavior of copy data activity in Azure Data Factory

0 additional answers

Your answer