Why Convex Limits Transactions and How Concurrency Control Shapes Your Database

Why does Convex limit my transaction length? Why do I get right conflicts or OCC errors? Can't convex just solve this for me? I mean like Postgress doesn't have these problems. Well, I'm here today to tell you that unfortunately Convex can't solve these problems and Postgress does have these problems. So, if you've ever been curious to know more about things like record contention, database consistency, isolation levels, and just kind of how to keep databases fast, I'm going to try to give you everything you need to know in this one video. If you watch this, you should be good to go, whether using Convex or to be honest, any other system. So, let's start out with the most basic case, the happiest database, the easiest thing to explain. So, meet our castle. This is going to be our over wrought metaphor to talk about databases today. So there's a vault in this castle. We can see on the left and it has pedestals and each pedestal contains a ledger. So there are five nobles, Ashcraftoft, Waverly, etc. here. And then there's the king and his special gold pedestal. We also have a number of accountants, each one that works for the a particular noble that will be conducting transactions on their behalf. Let's start with the simplest possible thing to do, a low contention transaction set to run here. So on this day, the nobles are all harvesting uh on their farmland and selling those crops. So they have some deposits to make into their accounts. So, their accountants each go into the vault, head to the pedestal that belongs to their uh employer, and then they add $10 to the account to represent the wheat or whatever that was sold that day. Very straightforward. When they're done updating that ledger, they head back to their desk and kind of wait until there's something else to do. Um, you might notice that they're taking a few seconds to do work when they're at the pedestal. And that's because it's like the 1400s or whatever, right? So, like, let's just say they're using abacuses or something. We can't add as quickly as back then as we can now. And so, it takes a little time to conduct the transaction, but they're all happy. Everybody's transactions are getting finished. There's no contention on any record. That's going to come soon. This situation is one in which we'd call this load embarrassingly parallel. It just means everyone is able to work independently, right? No one depends on anything else. No ledgers depend on anyone else. And so we can run this for a lot of nobles all at the same time. SPOF is in some respects the kind of opposite of embarrassingly parallel which means like a single point of failure or a single point of contention which means there's one resource that everyone sort of depends on and then that has can both have problems in terms of reliability of the system and contention meaning if everyone wants to get to it at the same time that might become a problem. This kind of pattern we saw here, very straightforward parallel editing of ledgers independently. What database systems behave that way? Every single database system, every database system looks like a champ uh including Postgress and Convex and Reddus and everything when you have no contention. So let's move on to where things get interesting. And there's no headache worse as we all know than tax day. So on tax day, everyone has £100 in their account and it's time to pay the king. In this case, all the accountants rush into the vault and then they all grab their ledger. They carry it over to the king's pedestal where the king's ledger is and they have their abacus and they sit there and they do the calculation. So, why do they do this? Why are they right in front of the pedestal? Why are they blocking the other accountants from accessing it? It's because it because it takes a little time to add things up on the abacus. They need to make sure the king's ledger doesn't change while they're working. And they also don't want their nobles ledger to change while they're working either. So they hang on to their nobles one in their hand and they sort of block everybody out from the king's ledger. And I don't know, they manipulate the abacus with their other hand. I don't know how many hands we have here. And then once they come up with the right value, they write it down in the king's ledger and they write it down on on their nobles ledger and go put it back. Right? They're like, "That's it. We did it. I did the transaction. I took money out of my noble's account and I put it into the king's account." And you can see that's what's happening here. But you'll also notice a little bit of a queue forms, right? So, um, because people have to wait for that abacus work to be done one at a time. So, it might even start to get like kind of crowded in the vault, right? It might be hard to push past people and uh conduct unrelated transactions, right? What if there's a war somewhere and the Navy needs to pull some money out of the king's account, right? And it has to wait in line behind a whole bunch of thing and it's like, "We need warships. We're losing. You got to wait." Right? So contention is a problem in databases just like it's a problem in this vault here. Uh if you everyone is trying to access the same record and it they all take a little time to conduct their transaction, you can easily kind of pile up work. It's effectively kind of waiting on a lock is what's happening here. So even trickier is this scenario. Some of you may have heard of deadlocks and this is a real problem that can happen to systems where you're trying to make sure a transaction happens correctly that involves two different records. So in this case Ashccraftoft and Hartwell, two nobles are probably trying to transfer money to each other, but the accountant grabs uh their their employers first and then walks over to the other table. I guess in this case, Hartwell's accountant is sitting there looking at the empty table going, "Oh, someone must be using Ashcraftoft's leather ledger right now. I'll just wait a minute until they're done." But the problem is that's the exact same reasoning that Ashcroft's accountant is using about Hartwell's ledger. So, they're never going to make any progress until they suddenly give up and start wandering around trying to see, is something going on here, and I will never get this back. So, this is a deadlock. This approach is pessimistic concurrency control. Why is it pessimistic? Well, because you assume contention will happen and so you lock everything ahead of time to prevent it. Right? That's what the accountants were doing by blocking everyone from using the ledgers is they make sure only they can touch ledgers right now so that they know when they do the math that they're writing the correct next value into both ledgers at the same time. And that blocking on access can tie up resources, right? Like obviously no one else can get to the king's ledger that they all need. So they have to wait. And you have to be careful about deadlocks. If you need two ledgers to conduct an operation and you grab them in the wrong order, then you could end up kind of tying up all the transactions related to either of those two accounts. And fundamentally, the problem we have here is that any operation that takes n seconds, you can only do one over n of those a second, right? So even if I only take half a second to add stuff up because I'm the fastest abacus user in the world like okay now we can do two transactions a second there's no way around it right because we need to make sure no one else changes we cannot parallelize the work on these ledgers what systems is this like this behavior we saw in this simulation this is pretty close to using something like Postgress if you're very careful to use select for update which is like locking your dependent records while you um conduct your transaction. Now, you don't have to manage this problem this way to have correct transactions. Just as there's pessimistic concurrency control, so is there optimistic concurrency control. So now the king says to everybody, "I don't like it how crowded it is in here. My navy is trying to get money. Nobody could get through. There's too many bodies in the room. I need way less people in the vault so that I can conduct the king's business." So, let's just say the accountants get together and they come up with a new scheme. This new scheme involves this kind of window they make into the vault, right? Where the accountants can line up along this window and they can just look in and they have really good eyesight and they can just look right now. How much money does the king have and how much does Ashcroft have? They just take those two numbers and then they go back to their desks and they do the calculation there. Let's see what that looks like. So they go and they read the values and then they go back to their desks and they do the work there. So they're not tying up a ledger anymore while they do the long expensive part of their jobs which is doing all the aacus stuff. They do the aacus stuff at their desk. But you'll probably notice that some of them when they go over right where it restarts here, the first one succeeds, but then there's a red check marks next to the other one, right? So those ones fail because they notice that the ledger has changed since they look through the window, right? And because the ledger's changed, they decide, oh, I'm going to need to go back and try again. So you can see up here in the corner, it says king right retries seven. So eventually we get all the transactions done. If we watch up here eventually the tax payments we will get all five tax payments in but what will happen is a few people will have to try again because they kind of lost the race right by the time they got to the ledger to write out their calculation. They realized that the ledger had changed. So they needed to just repeat the process, take another look from the window at what the current values are and then go back to their desks and take the time to calculate. This means that they're going to waste some work, right? That's what this trade-off is making. The trade-off is saying sometimes you're going to have to calculate multiple times because you kind of lost the race and someone else changed ledger before you finished. But the vault is no longer packed full of people waiting, right? So, if uh um someone needed to come in here and get to some other nobles ledger, you're not pack you haven't packed the vault full of uh people. Um you can get the job done. So you might say this feels like well this metaphor doesn't really apply to databases but but it really does. So it ends up that if the on the left the vault really represents kind of the database server and on the right those are all the like application servers that are calculating things. The d if the database server has just a ton of pending work all piled up waiting for finite amount of resources. It can eventually be that there's like no threads available to conduct the database transaction. So there is a benefit to this optimistic concurrency control even though it is hypothetically and actually less efficient because you do extra CPU work and that is that the database server which is the rarest scarcest resource that's stateful um doesn't deal with all the back pressure of all the jobs waiting to run. The application servers deal with that back pressure and that is often a very good trade-off in real systems. So yeah, in summary, optimistic systems usually work better when contention is rare, when most rights to the database aren't all trying to update the same record. In practice, in most systems, contention is usually kind of rare. Most transactions are related to us users personal data or maybe a small chat room they're in. They're not normally on the order of everyone using the app, for example. Optimistic concurrency control is willing to waste work on contention, right? So um you will maybe have to go try the transaction again if you lose the race. Um but it doesn't bog down the scared the the scarce resource the database primary. It keeps all that back pressure about work waiting to happen in the kind of application servers which um are can be scaled up infinitely and don't have this kind of single point of failure element to them. Um what systems work this way? Convex works this way. Foundation DB, TAKV. A lot of newer systems are powered by optimistic concurrency control under the covers because it often works out better in real systems even though it's less ideal in a perfect system. Postgress can be made to work this way in serializable isolation level, which it's not set by default, and we're going to talk more about that soon, but it's pretty awkward to use that way. So, it's very rarely uh configured to have serializable isolation level. Let's dive even more into why optimistic concurrency control is quite good in real systems. So imagine there's a new noble in our kingdom, Dukeington. This is our new money dude. And correspondingly, as you would expect, the infrastructure at the castle doesn't have a lot of respect yet for Duke Washington. So they go hire a new accountant, a junior accountant that is not very proven yet. and they say, "Uh, Duke, we got a great person for you. They're going to take care of your account." Now, in a situation where we're using pessimistic concurrency control, here's what could happen. So, all the nobles rush out. They're all trying to pay taxes, but Duke Ashington maybe isn't that fast yet at doing abacus work. They're new on the job. or maybe even they're pretty unreliable and they just fall asleep at the pedestal while they are uh working on the ledger, but they're blocking everyone else from making progress. As you can imagine, pretty much the whole kingdom will grind to a halt in this case. having one actor in this case uh Duke Cashington's young accountant who is able to effectively stop the entire system from making progress is an extremely serious problem that most real uh backend systems sometimes have if they're not very careful. You might say once again this doesn't have much to do with real code but it does. So the bigger your system gets, the more careful everyone needs to be to not write a query that is takes too long to run in a pessimistic system. The more servers you have, the more likely you are to have a process that pangs for some reason or a thread that runs into a problem. And if any of those things happen, then the lock will kind of be held potentially indefinitely. Your entire site can be down. In fact, I would go as far as to say if you read SEV reports from public companies, especially as they're growing, like going through their kind of first three to five years, some of their first SEVs are related to this problem. Uh, a query that was too expensive, held a lock open too long, and either failed or suddenly became extremely slow, maybe because of query planner change, and it effectively tied up all of the throughput in the system waiting on that lock. Now let's see how that same scenario plays out in a system that uses optimistic concurrency control. So in this case the purple uh new accountant there goes and reads a snapshot and starts working at uh his desk and he falls asleep at his desk. That's not great because it means that uh Duke Washington is maybe not paying his taxes today. But you will notice that despite that accountant sleeping, all the rest of the business of the kingdom gets conducted and the vault is not tied up at all. In fact, um, Ashccraftoft right here actually has an additional transfer to do, transfer some money from Duke Washington to Ashcraftoft. Probably Ashcraftoft sensed an opportunity, offered Duke Washington some pretty bad farmland for too much money. uh and Duke Cashington knew money was happy to pay it. Um so even though in this case Duke Cashington's ledger was even involved, the transaction was still able to be done by Ashcraftoft's accountant because no lock was held not only on the king but there's not even a lock held on Duke Washington. So there's no locks in this system. And in real systems that get more and more code, more and more developers working on them, maybe people start to lose track about what things are doing or what are best practices. You kind of want to isolate problems from becoming systemwide problems. Um, and optimistic concurrency control is excellent at that. So pessimistic concurrency control, pessimistic locking, it is definitely more efficient in ideal conditions. If there is contention and everyone implements things optimally, then you will spend less total time in the system um than you would in an optimistic system which allows itself to repeat work. But in real conditions, some things take much longer than other things. Sometimes new code you just wrote is both less hardened than existing code because it doesn't have any production time on it yet and it's supporting features that don't matter as much yet because there's not the core of your pro project and it's written by people newer to the team. There's all these correlations with introducing new things as code bases grow and you want to be able to isolate work from other work and have it fail gracefully. You also want to make it so that you can't deadlock the system, which you don't have to be careful with lock ordering with optimistic concurrency control. There's never any locking. For this reason, the compositional power of optimistic concurrency control tends to be much much more favorable to keep a system online as it grows. And that's why most new database systems and backend systems are preferring optimistic occurrency control if it's at all possible. And that's why Convex does as well. All right. So I've said a lot of caveats throughout here about Postgress like wait till later hold on I'll explain. You might ask how does Postgress really work out of the box and this is pretty close to how my SQL works out of the box too. So how do mainstream traditional RDBMS's how do they fit into this picture and is that why they don't have the problems or the constraints that convex has? The short answer is yeah they are making trade-offs differently than convex does. Here's how they actually work. You might be surprised. So, by default, the world's darling legacy databases actually use a mode called read committed. This is an isolation level. The isolation level where all the records are definitely correct is usually called serializable, which means the world kind of feels singlethreaded. And that serialization is kind of why only one person can be working with the king's uh journal at a time, right? The king's ledger is access to it is is serialized. But because that is performance problems and because it doesn't interact very well with traditional programming language environments, most databases use something called read committed out of the gate which has pretty surprising data correctness characteristics. And let me show you exactly how it works. So just like in convex the accountants do not take locks by default. They do just sort of read the values they need and then write later. But what they actually read is they just read whatever the latest committed value was at the time of the select query. Um so that's why it's called read committed. So what that can mean though is if a bunch of them read and then they do some work and then a bunch of them write, they can basically make it so that they lost rights. Let's uh let's step through that real quick. So in this case, we're going to see three accountants line up and they all read the current values of all the journals. Just like in the optimistic and currency control case, they go back to their desk and they begin working. So, in this case, they each observe that the king had $1,000 and that their employers each had $100. They use the abacus. They determine that the king uh needs $1,010 because they're making a $10 tax payment. The issue is that when they go to the king, they each write $1,010. Once all three are done and they consider the transaction finished, the king's balance is only 110 even though $30 has been subtracted from the nobles journals. They don't check that the king's ledger has changed when they issue their right. They don't know that the accountant right before them already wrote 1 and that they need to go back and recalculate because the basis of their calculation is different. Now, you can see up here in our little thing, we've expected at this point 30 pounds of tax paid. There's only 10 that's been posted to the ledger and $20 literally dropped out of the system. Despite the fact that this simulation should be zero sum, the kingdom just lost $20 out of the books. As we run this, you can see the next group of two, the first one succeeds, the second one silently loses data, and we just lost another $10 from the system. So, we're up to $30. Now this is actually how Postgress works out of the box. So by default systems like Postgress and my SQL they just grab the last committed value of a record when the select runs. This is a little bit of a detail but they're not even in snapshot isolation level out of the box. Meaning that if the two selects run after two different commits, they could actually read the committed values from two completely different transactions from the two records they depend on. This is a very loose isolation level to be honest. And most developers are surprised when they hear this. If you ever run into weird values in your database that you thought were your fault, there's a chance that it's actually attributable to this if you were relying on acid to like actually work. Double check your app. And the way around this is to switch to select for update which even in readcommitted isolation level Postgress will use that to start locking rows. But see all the previous notes about being very careful about pessimistic locking because those are pessimistic locks and you can deadlock your database or start tying up your database's threads or processes. You have to be very careful. So now you might be saying so is this all just hopeless? Is there no way to speed up contended transactions? You know, let's say that there's thousands of nobles inside the king's kingdom and there's a lot of tax collecting going on all the time. How can I accurately add all those up if I can't process all of you know a thousand transactions in whatever time period in a minute, an hour, however often they're paying tax, right? How do you break this speed limit when it does feel so fundamental hopefully by now that it's just a physics thing? Well, there's a few strategies, but they're mostly variations of a couple of patterns because to be honest, there's not that much you can do. One is you just make the operation faster, right? So, like if you're using a calculator instead of an abacus, then you'll be able to do a lot more transactions per second because the kind of critical section is shorter. Regardless of whether you're doing optimistic concurrency control or pessimistic concurrency control, the system throughput will go up if you speed up the operation. That's always one thing you could do. But if you can't speed up the operation, the most common strategy is to leverage staleness, which means kind of like to relax consistency. So let's look at an example of it here. So in this scenario, we have a lot more nobles, but the nobles don't directly pay the king. The nobles pay a tax collector. And we have four tax collectors that each of them are assigned to. And so those tax collectors are managing like four or five relationships. And then the king's ledger, only those tax collectors are allowed to update it. And that means there's only four people, four accountants in this case that are trying to update the king's ledger, which keeps the contention down. The key thing is those tax collectors, they put the entire sum of all the transactions they've conducted in as one transaction, right? So if they take $10, $10, $10, then when they go to the king, they just put 40 at once. And that lowers the total number of transactions the king has to take. Let's look at this simulation for a second. So all of those nobles up above, they're all racing in and they're they're conducting transactions with the tax collector. The tax collector is occasionally moving over and taking their whole balance. They're waiting in the queue for the king and they're adding the the ledger entry for all of the proceeds that they've collected. Um and so this is a kind of tree of computation, right? The the thing that makes it stale is that let's say from Ashcroft's perspective, the noble in the upper left, the second they've paid their taxes, they're pretty darn sure they've paid, right? Because their account, the money's come out of their account. But the king wouldn't say yet that they've gotten that $10, right? The king won't see the $10 until after the second transaction happens where the tax collector um goes and moves money from their intermediate fund into the king's ultimate ledger. And you'll notice we also have this new entity on the right side, the Navy, building warships, doing war stuff, right? Doing what navies do because the king isn't too contended because they only have to deal with the tax collector. The Navy has no problem getting in there and taking sizable chunks of money out of the king's ledger. We can see the vault's balance is going down over time, but it's not it's zero sum in terms of the correctness of all of the tax payments in this big um network we have here. Um the only money that's going out is the money net to the Navy who was able to get their transactions done as well. As you can imagine, this tree of transactions could be many levels deep if it needed to be. and we would just basically roll up aggregates over time, but we would never have too much contention on any one record. So yes, you can work around most contention problems by introducing staleness, selective staleness in a way that's eventually consistent and in a way that your application can tolerate. There are levels of aggregation and/or periodic batch work that's done by a smaller number of actors than all the actors that want to conduct a transaction. You can always speed up the operation if you can. That's the easiest thing to do. But we put so much work in optimizing systems these days that normally that is harder to do than to come up with some data structural strategy that involves staleness. And this is what most convex components do for you. You just call the function. You don't have to worry about how to implement all this. Um, so if you're worried about these performance things, make sure to go check out convex components. There's often a data structure in there that will make the OCC problem go away. So, contention, consistency, isolation levels. Why won't my database go faster? Why is convex only letting me run for one second? Why can I only write one megabyte of data? What is an OCC error? Right? Um, you know, hopefully you have a better sense about why we're surfacing that information to you and some things you could do about it and how we are faced fundamentally with the same trade-offs any database system is. There's nothing slower or faster about convex. This is just physics stuff. There is a fundamental speed limit on consistent operations on a contended record. Um you can introduce staleness. You can willingly lose data right like Postgress does out of the box or you can speed up the operation itself if possible. Pessimistic is theoretically more efficient. You don't waste time computing stuff and recomputing it. Um but is definitely more fragile as projects get bigger and teams get bigger. And uh it's very hard to reason about isolation levels other than serializable. So I am implicitly making an argument here that post out of the box it's actually very hard to build correct applications with it and if you're on convex use convex components and most of the time you won't have to think about these things. Hopefully that was useful for you all. It's probably the point where I say like like or subscribe I do this it's down there somewhere. Thanks for watching.