ConcurrentAppendException Liquid Clustered Table Different Row Concurrent Writes

[georgecalvert]

I have multiple databricks jobs performing a MERGE command simultaneously into the same liquid clustered table but for different rows of data and I am receiving the following error message:

 [DELTA_CONCURRENT_APPEND] ConcurrentAppendException: Files were added to the root of the table by a concurrent update.

I am using databricks runtime 15.3. My understanding is with liquid clustered tables and Databricks runtime > 13.3 concurrent row-level MERGE / UPDATE operations should be possible. DeletionVectors and RowTracking are enabled in my config. 

[Walter_C]

This exception often occurs when concurrent operations may be physically updating different partition directories, but one of them may read the same partition that the other one concurrently updates, thus causing a conflict.

Even though you're working with different rows of data, the condition in your MERGE command might not be explicit enough and can scan the entire table, which can conflict with concurrent operations updating any other partitions.

To avoid this, you can make the separation explicit in the operation condition. For example, if your 'deltaTable' is partitioned by date and country, you can add specific date and country to the merge condition. Here's an example:

deltaTable.as("t").merge(  
  source.as("s"),  
  "s.user_id = t.user_id AND s.date = t.date AND s.country = t.country AND t.date = '" + <date> + "' AND t.country = '" + <country> + "'")  
  .whenMatched().updateAll()  
  .whenNotMatched().insertAll()  
  .execute()

This operation is now safe to run concurrently on different dates and countries. Please replace <date> and <country> with your specific values.

[Rishabh_Tiwari]

Hi @georgecalvert ,

Thank you for reaching out to our community! We're here to help you. 

To ensure we provide you with the best support, could you please take a moment to review the response and choose the one that best answers your question? Your feedback not only helps us assist you better but also benefits other community members who may have similar questions in the future.

If you found the answer helpful, consider giving it a kudo. If the response fully addresses your question, please mark it as the accepted solution. This will help us close the thread and ensure your question is resolved.

We appreciate your participation and are here to assist you further if you need it!

Thanks,

Rishabh