Skip to main content

Reducing Transaction Conflicts in Databricks—Fundamentals and Applications at Asana

Summary

  • Concurrent transaction fundamentals: This video explains how ACID-guaranteed transactions in Databricks handle concurrent INSERT, UPDATE, and MERGE operations, including when conflicts occur and how isolation levels impact behavior.
  • Optimistic concurrency control: Learn how Databricks uses optimistic rather than pessimistic concurrency, meaning transactions proceed without locks but may be cancelled if conflicts are detected, requiring careful architecture for high-concurrency scenarios.
  • Asana's high-concurrency solution: Discover how Asana handled 700 concurrent writers to a single table using an intermediate blind append stage, demonstrating practical patterns for managing transaction conflicts at scale on the Databricks Data and AI platform.

Reducing Transaction Conflicts in Databricks—Fundamentals and Applications at Asana

Watch: Reducing Transaction Conflicts in Databricks—Fundamentals and Applications at Asana
Talk by Dima Kamalov, Software Engineer, Asana
When using ACID-guaranteed transactions on Databricks concurrently, we can run into transaction conflicts. This talk discusses the basics of concurrent transaction functionality in Databricks—what happens when various combinations of INSERT, UPDATE and MERGE INTO happen concurrently. We discuss how table isolation level, partitioning and deletion vectors affect this. We also mention how Asana used an intermediate blind append stage to support several hundred concurrent transaction updates into the same table.
Here's more to explore:
Databricks named a leader in the 2024 Gartner® Magic Quadrant™for Cloud DBMS: https://www.databricks.com/resources/analyst-paper/databricks-named-leader-by-gartner
An open, unified approach to your data, BI and AI workloads: https://www.databricks.com/product/databricks-sql
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

FAQs

How does Databricks handle concurrent transactions differently from traditional databases?

This video explains that Databricks uses optimistic concurrency control (no locks), allowing all transactions to proceed. If conflicts are detected, some transactions may be cancelled and must be retried. This differs from traditional databases using pessimistic locking, making concurrency behavior different to understand and tune.

What causes transaction conflicts in Databricks?

Conflicts occur when concurrent transactions try to write overlapping data. The video uses examples where two processes might try to UPDATE or MERGE the same data simultaneously. Depending on isolation levels, partitioning, and table features like deletion vectors, conflicts may or may not occur.

How did Asana solve the problem of 700 concurrent writers?

The video demonstrates that Asana used an intermediate blind append stage pattern. Instead of all 700 processes writing directly to the final table (causing conflicts), they write to a staging table, then a separate process consolidates the appended data into the final table, dramatically reducing concurrency conflicts.

What factors affect transaction conflict rates?

This video covers how table isolation level, partitioning strategy, and deletion vectors affect concurrency behavior. Proper configuration and architectural patterns (like Asana's staging approach) enable Databricks to handle even extremely high concurrency scenarios reliably.

Full transcript

Yes. So I'm going to talk about having fewer transactions. Uh and the reason I was working on this problem is that I had a table downstream of 700 upstream tables. Anytime anything upstream changed, I wanted to propagate the changes to my downstream table. And so there could be 700 processes writing to the same table at the same time. They started writing into each other. Okay. Uh data brick supports transaction which is great which is basically saying okay do all of this operation or none of it.
So you're never going to have a write which says update these 10 rows and seven of them will be updated and three will not be. That would be terrible to debug. Uh so fortunately we can in fact do this when you have concurrent transactions. There's kind of two mechanisms various database like systems use to manage concurrency. The first is asking for logs. So saying okay I want to update this type of data and give me the exclusive permission to update this type of data. don't let anyone else update it while I'm updating.
The second is optimistic control, which is that we welcome everyone to try to do what they can, but we have some system limitations in place that will cancel some ongoing transactions if we feel like we can't do it safely. Uh, data bricks uses the second type. So, there are no locks. In many cases, this is really good because many times only one process writes to a datab bricks at a time because you're doing updates in batch.
It's not like you're going to have various data bricks processes writing one row at a time. You end up with millions of files plus three bad things that happen. Uh so in those cases there are few writers and so optimistic control saves us the hassle of having to get lost. However, in my case, I did have 700 writers and this led to many concurrency issues. Uh, data bricks has very good documentation on concurrency. Uh, I will just note that in my talk uh update will mean update or delete but I have some exceptions for merge engine.
Okay. Uh so the way this talk is going to be set up is I will have three notebooks for a lot of examples. One notebook will be about setting up some tables as a starting state. Uh that's going to be the notebook at the bottom. I know you can't read it. Don't worry for now. Uh then there's going to be two other notebooks uh which are going to represent two transactions I will try to run at the same time. uh and we will try to see what happens when I run those transactions at the same time.
For most of these, I've pre-recorded myself because trying to put something exactly at the same time with the internet and stuff is very annoying. Uh so rather than me having to try and fail many times, I avoid that. But for the first one, it's very easy. So I will just give you a sneak preview of the sort of thing that is going to each of these and that way you'll actually I can actually make it big enough such that you'll be able to look at the text.
Uh so really we just have a setup step and creating a table and replacing with a fresh state in all of my tables. There will be students with ids and names later they'll start adding colors colors and that's really set up for the first set where we're going to try to run two inserts at the same time. Uh so here's transaction one. Uh it inserts a new student. Here is transaction two. It also inserts the same student. And all we're going to do is run these at the same time approximately and see what happens. and you'll see that the written twice for each transaction.
Uh and that in the table history, we'll see that there were two rights. Uh uh this is the initial table creation. So these are the two rights from the transactions bit later. They happened at about the same time, but really it didn't matter. Uh so inserts are very safe is what you I want to want you to take away from this in terms of concurrency. They don't do any reads. They don't conflict. Uh life is good. Uh so Let's start getting a little bit more complicated once we get to uh in the second example.
We're again going to start with a table with two rows and the transaction on the left will try to again insert a row but the transaction on the right will try to change all existing rows uh change all the favorite colors to violet. So let's see what happens when we try to run these at about the same time. Uh so both of the transactions succeeded. Uh and you'll actually notice uh that this one only affected two rows. Uh so in sense you might think oh maybe this just happened before uh the transaction here and the table state would be consistent with that approach.
So these two were changed their color. Uh the added row was not. Uh but interestingly if we look in the table history sometimes it'll show that the update happened after the write. Uh so this is a bit of an odd state uh because uh it looks like what actually happened was that the read part of the update happened before uh this write but the right part of the update happened after this right. So the this idea of history becomes a little less well defined.
In this third example, we're going to try to use this idea of a serializable isolation level to remove the indeterminism we had in the previous example. Uh we have the same operations. So the transaction on the left tries to insert a row. The transaction on the right tries to update all existing rows. And we're going to try to run these at about the same time. Uh and in this case uh we see our first example of a concurrent append exception uh which is basically data bricks telling us I cannot do the thing that you wanted me to uh within the constraints of the current isolation level.
There is no uh way to do the thing that I was trying to do running the transactions one at a time uh based on the state that they were in uh together. And uh if we look at what happened in the table uh in fact the insert happened but the update uh did not happen at all. Uh so this is where we start having this idea of isolation level. Uh and the default one in data bricks is quote unquote right serializable. uh which basically means that the rights can be orchestrated in some order uh one at a time that produces the same result as if we ran them at the same time.
Uh but we can make it more strict so that the reads also start being subject to this constraint. Uh and that's what we see here. So you kind of have a choice with right serializable maybe your history will be a little messed up when there's some reads involved. Uh and with serializable you will sometimes get some crashes because we cannot run a transaction and you'll get this exception. In the fourth example we are going to have two updates running at the same time and in fact they will try to change my favorite color to two different things.
Uh so this is uh back to the base table with right serializable isolation level not serializable. Uh and you'll notice that this time we still get a concurrent append exception. Uh so there was no way uh for these two transactions running at the same time to have the same result as if they were run one at a time. And uh so you'll see that the transaction on the left does not do anything and my favorite color ends up being violet.
In this fifth example, we're again going to try to have two updates at the same time, but this time the two updates will be modifying two different rows in the table. Uh so let's see what happens when we try to run these at the same time. uh and in fact we still get a concurrent append exception. And what happened is uh when we created this table these two uh rows were written to the same data file. And uh the default operation for both of these operations is to read that file uh and change uh the uh value of this and rewrite the new file.
But the file has both data points. Uh so they were actually trying to both operate on the same file at the same time and that's how we still get this concurrent append exception and only one of the transactions goes through. Uh so in the first of these updates I would naturally expect a fail because there is very explicitly a set of things that conflict. So ability we are trying to change my favorite color and we're trying to change it to two different things.
That's a reasonable semantic complaint of internals of the system I probably don't care about. So because when we get the initial write we wrote to the same file and that data is in the same file to edit either row by default we're going to read that file and rewrite a new version of that file. Uh so we want some way to avoid that so that when we have updates which touch different rows they should still be able to succeed.
And there's two main tasks at that. The first is using partitioning. Uh what partitioning does is puts data in different files based on some field in them. Uh so that's what we're going to try to do uh first. In this sixth example, we're going to use the idea of partitioning to try to fix the file problem in the previous example. Uh when we partition a table, one of the things it does is guarantees that rows with different values for the partition will be written to different files.
Uh so let's see what happens when we try to update uh these two rows at the same time. And we'll actually notice that we still so you'll notice that I tricked you. Uh so even though we partitioned and wrote them to different files, uh these SQL statements don't know that. So they have to read both files in order to figure out what the data is. Uh because I didn't specify a partition column in my SQL query. Uh and so even though the data is partitioned, the SQL file doesn't know that one of them gets changed. we again have a confirmed exception.
So the way we actually need to fix it in this seventh example, we're again going to try to update these two different rows at the same time. Uh but this time we're actually going to specify in the SQL query what the value of the partition is. And now we'll notice that actually both of the updates do succeed. uh we can look at the table contents uh to see that in fact both of the colors were changed and the idea is that by specifying the value of the partition in the query we no longer need to potentially read both of the files in order to know what to update.
Okay, but uh so that was approach one basically use partitions and specify the value of partitions. uh partitions don't work well with some data bricks features for example liquid clustering. Uh so uh for about a year we had this solution in place and uh we wanted to speed up the reads on the table and so we looked for a different solution. Uh and to do that we need to start talking about deletion vectors. So that's going to be up next in this in this seventh example.
In this eighth example, we're going to try to use a different approach to solving the problem of not having to rewrite the whole file for a row. We're going to use deletion vectors. And what deletion vectors do is that updates no longer rewrite a whole file. Instead, they write some metadata about which rows should no longer be read from that file and should be read from a new file. Uh so again we're going to try to write two updates at the same time and we'll see that again this was able to succeed.
Uh so this is a different approach to being able to update different rows in a table at the same time uh without necessitating the use of partitioning. Uh this can be nice because maybe you want to use liquid clustering which is not compatible with partitioning and if you use deletion vectors you could do that in this ninth example. Okay, so now we're going to move into merge induce. Uh an interesting thing about this example uh is let me actually get the code so I can zoom in on this is that really we're only going to be having inserts again running but because the inserts are going to be parts of merge of merges uh they will no longer conflict.
Uh so the type of uh query we're going to be having here is uh to look at some source of data and but the only condition that we'll ever do is if there is a new row then we'll do an insert. Uh so before you might remember when we ran two inserts at a time we could not conflict. This type of insert can conflict uh because in the time that the statement is running some other data could have been written. So the read would confict and transactions.
Uh so this is uh I'm not going to bother playing video this one but the idea is that we can get a conflict between this identical transaction trying to insert a single row into the table. Uh so deletion vectors are not something that helps with this type of thing. And uh what we However, is to use both deletion vectors and in this 11th example, we again have deletion vectors enabled and we're going to try to have two simultaneous merge into uh but this time they're going to right so if we insert different row before we were still trying to insert the same row from true to two two transactions a deletion vector is going to allow us to do it when it's two different rows but there's a bit of a limitation which is that the single query itself needs to be able to know that the row we're trying to insert in one of the transactions is different from the row we're trying to insert in the other transaction So here this row is clearly different from this row and so the inserts uh can happen at the same time.
Interestingly uh if we make it a little bit more obtuse and we put them into an array you can no longer tell just from analyzing the SQL statements that these will not conflict. And so there's this very interesting behavior where in this 12th example, we are again going to have two concurrent merge into writing to a table with deletion vectors. Uh and they're going to insert two different rows, but this time unlike in the previous example, the way they get these rows is through exploding an array as opposed to an ID being explicitly specified.
Uh so this time one of them does fail because looking just at the outer SQL it is not obvious to the SQL uh parser that these two will not touch the same row. Now basically the idea is that a rowle concurrency needs to be able to tell just from the query itself rather than the result of the query that two of the same rows will not be touched. So we canot SQL first. Okay. So let's go back to the problem I was trying to solve where we had 700 upstream tables uh writing to the same downstream table.
Uh and what we had initially uh was 700 merge into statements and you saw how the merge into can conflict quite capricious capriciously uh where even if they don't conflict by rows because the result of the query there's a result of a query necessary to tell that uh we can't do it. However, uh inserts themselves are pretty cheap and don't conflict at all. So, what we really did was just write make a new table in between, write all the potential changes we would have to that table uh using insert and then use a merge into using the change feed from that table.
Uh so that also has the nice ability that we get to batch updates from multiple tables into the intermediate table when we try to merge them into uh so that's our current implementation of this problem. That's all I have.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.