Prevent RACE CONDITIONS! How to use Record Locking in Xano
Ray Deck shows how race conditions corrupt data when multiple requests edit the same record simultaneously in Xano. Wrapping your get-edit-save flow in a database transaction with record locking forces requests to wait their turn, but this slows down your API. Use background tasks instead if you don't need instant results.
Chapters
Let's talk about race conditions. One of the challenges of running a multi-user system, having lots of people and having lots of requests running around, is that you can have your database getting written to by multiple endpoints at different, you know, at different points in time, maybe even on behalf of the same person, creating inconsistent states. And this can be particularly challenging because you can have something along the lines of, I'm going to You're going to go get a record. Let's sort of pull it from my lockable table, which is going to be the example we'll use in just a second here. And I can go pull a piece of information. And then later I want to— maybe you're going to go do a web request. You're going to go get some AI stuff. You have figured out this is something that needs to be updated. And then a few seconds later, you're going to then edit that particular record. And you're going to take the same same table, same record that you're gonna go put in, and you're going to say, now I want to send that data back, and now you are done. See, the trick is what happens when someone calls the same endpoint or calls a different endpoint after you've done the call and you are in the middle of trying to update it, but then while you're updating it, somebody comes and gets it and starts their own edit process, which is based on that older state, right? Write of the, of the record. And this is why we call it a race condition, because then, you know, it's not like, hey, I, you know, I get the record, I make a change, then you get the record, you make a change. It's, you know, I get the record, I'm working on it, you get the record in that same old state, you start working on it, I make a change based on what I originally knew, you make a change based on what you originally knew, and the whole thing starts to get really messy. And so there's an idea called data locking. And you can click here where it says the Get Record, and you can see there's this thing called Lock. And so the, the objective then is to say that if you wanted to, you know, edit one of these records, uh, we could just say let's add a— add input, and we'll just call it an integer for a second. We'll call it, you know, ID. Uh, and the, um— and we want to get the record with an ID of ID, and then we're going to later edit— sorry, uh, you know, cancel. And then we want to lock it, right? We could say, yes, I want to lock it, true. And so the theory is that now you can get the record, and until you're done with it, it's not going to make any, won't let you make any changes. So let's just try this for a second. So if I've got this lockable table, and up here I've got a reference to my tables, I'm just gonna scroll on down to my lockable table. Here we are, and I click on lockable table, and you'll see I don't really have much in it yet, so I'm just gonna create a new record, and I'm gonna say the message is not yet started, and the last modified, I'm just gonna, you know, make it into, you know, right the heck now, right? So it's gonna say the last modified was immediately. Now, the cool thing I can do here is I can say, I can go back over to my DB lock test, and I have my get record, right? We're gonna get this particular record, And I have an edit record where I'm going to edit it, right? And I'm gonna also pick it based on the ID. Now maybe what I want to do is take the message and add a word to it, all right? So I'm going to take the message, which is going to be based on my, well, in fact, you know what, we'll put this in the middle here. We're gonna create a variable. Yeah, fine. And we'll say, we'll call it, you know, new message. Message, and the value is going to be equal to the lockable table, right,.message. And, um, and then we're just gonna, you know, concat onto it, um, say the message of again. All right, so what's going to be the idea here? The idea here is we're going to say, um, that whatever the original message was, we're going to add the word again to it. And now, and then if I come on to here, I can just say, well, I'm going to set the message to new message and the last modified to, uh, what we'll call it, um, oh, actually we'll just say that's equal to now, right? So it's gonna be whatever the new timestamp is. Uh, now if I were to go, uh, run this guy and I were to say run debug, you And let's see, this is saved. Yeah, it looks good. Uh-huh, okay. And then we'll say save and we'll run it. Okay, the 0 won't work, so I need to pick the ID number 1. And then it says missing field value because I did something wrong. And that seems fine. And this edit record is, didn't get saved because I didn't push the save button. And we'll say save and we'll run again. Great. So now you can see how it says not yet started. Um, and right now show me lockable table 1, which is the original one that I said, right? Not the one that I edited. And if I come over to here and I do a refresh, you'll see not yet started again. So the idea is that now I'm modifying the message, right? So it's based on the good current previous state. So I'll say initial, right? And now I come back over to here and if I were to run this again, In fact, I'm just gonna tell the concat to include a space in here. That way it'll just be a little bit easier to read. Save, run this again. And then if I come back over to here and I run this again, you'll see it says initial again. Now, let's talk about introducing, let's publish this for a second and let's run this from the outside world. Because publishing is usually where we start to see more of these race conditions. When you're just running things inside of run and debug, you often get it false view of how these things really work. The, you know, you think, oh, I ran it and then I run again, but that's not actually what happens here. What happens is something looks more like what we got over here in, you know, you know, if I were to just go grab the copy endpoint URL and I were to say, let's paste it in here, and I were to say, well, let's add some, you know, query parameters, and one will be ID of the value of 1, right? And I'm going to, you know, send that. And it comes back, says initial again. In fact, I'm finding a little annoying that it keeps on telling me what the original value that was. So one thing I find I often have to do is go tell the response to please give me what the new version is instead. Save. And then I, you know, and then in fact, you'll see over here, it already says initial again, again. And I would expect it if I were to publish this again, to show me whatever, initial again again, show me 3 agains, right? So initial again again again, great. If I were to run it from here, I'd expect 4 agains, initial again again again again. So it is working the way that it is supposed to. But now let's talk about this whole race condition problem. And the easiest way to demonstrate a race condition, and like the usual way that you get into these problems is gonna be when either things are running really, really fast on your system, or if you're using slow things in between. A slow thing in between would be like, you know, running an AI-based process, right? Like, you know, let's go to OpenAI, let's have it go create some text, and then we're gonna go modify on the basis of that or whatever. And that thing can take like 10, 20 seconds, and the chance that somebody else is gonna come along and try to modify this field is more than zero, right? So let's talk about what we're going to do about that. And that's gonna be where the whole lock thing comes in. So let's just say in the middle here, let's pretend this is gonna be more like an AI-type process., and the way we can pretend is by using the utility function of sleep, and that puts time into all of this. So let's just say this is going to be, you know, 10 seconds just for the sake of some fun here, and we're going to now run this again. See how long it takes? See, now it took more like 10 seconds. Great, now I have 5 of them. And now I can do this and I can say send. Oh, because it actually ran instantly because this isn't published yet. So I say publish, always publish when you're dealing with stuff on the outside of Xano. And now if I were to say send, see, it says sending a request, it's going to wait once again for 10 seconds, and then I'm going to get 7 of these. So many agains. Okay, so 1, 2, 3, 4, 5, 6, 7. So it works exactly the way it was supposed to. Now let's talk about the magic here. So the— let's just sort of say I've got this, and now I'm also going to just have a second copy of it, which I can do over here, where instead of using Postman for that, even though that sort of provides me with very nice formatting, I'm I don't have to use Postman, right? I could just say copy endpoint URL, and if I wanted to, I could just paste this into here and I could say question mark ID equals 1. If I ran it, you'll see it sort of circle circle circles for another 10 seconds and comes back with, uh, probably 8 agains, which is not the most exciting answer in the world, but you know, here we are. Okay, so 1, 2, 3, 4, 5, 6, 7, 8. So it did exactly what it's supposed to do. Now let's get to the problem here. The problem is that if I were to, you know, run, run this exactly the way that it is, I would expect that running this I would get 9, and running it again I should get 10, right? So if I do this, I say send, I should expect 9. If I do this, I refresh, I should expect to get 10. But that's not what's going to happen. What's going to happen instead is that one of them is going to give me 9, and then the other one is going to give me 9. 1, 2, 3, 4, 5, 6, 7, 8, 9, exactly. And then 1, 2, 3, 4, 5, 6, 7, 8, 9, exactly. See, they didn't actually change anything, even though the last modified changed between the two of them. Right, this one, you see the last modified here is different from the last modified here. So what happens is they each updated the last modified to their own current time, but they worked off the same initial base, right? So that what happened was they looked at the original version of lockable table, which had just initial and 8 agains, and then they proceeded to sleep for 10 seconds and then make their write. So the fact that the second one came in within the window of those first 10 seconds meant that it came in, saw it only having 8 agains, and it added 1, and then saved it. So they didn't actually do the changes we wanted to do. See, what we wanted to do is say, well, your first one comes in, and we'll reset this again just to illustrate the point. We'll just call it initial. And we'll say save here. And the effect of that will be that this thing will say, well, I'll spell it correctly. Right, and the, you know, if I were to say this, I want to do a send and give me, and I do this one, they will both give me initial again. They will not, it won't be the first one gives me initial again and the second one adds another again. It'll just be the same one both times, which is kind of a pain, right? That's what we call the race condition, and that's kind of the crux of the problem. Here. So what do we do about that? What we're going to do about that is we're going to be making use of lock. But wait, didn't we already introduce lock at the beginning of this video? It didn't work. And I see that so often, people say, hey, I tried lock and it didn't work. You know, it doesn't work like this. Lock doesn't actually lock it for like your entire function stack or anything like that. Locks, locks it from the point of view of what's called a database transaction, because under the covers, database locking, record locking, is a feature of the Postgres database, not really a feature of like Xano Function Stack. So in order for, you know, this to work, you kind of need to make the Xano Function Stack more aware of its relationship to the database. We do this with something called a database transaction. And if we scroll down here to where it says database requests, we have an opportunity to create something called a database transaction. A database transaction is kind of a group of functions. And if I were to drag this up here, I can now put my Get Record inside the database transaction and take basically the other actions I want to do, including the sleep, right? And say I'm doing them all during this transaction. Now, this is the crux of how a database transaction works. Basically, you are opening the door to the database, doing some stuff, and then shutting the door. So the, uh, there's a theory that like if you do like more edits into the database this way, they should go a little bit faster because they can be batched. I find that it only works okay. The bulk is really the way to go for speed. But the, um, but the big benefit in here is going to be through this locking idea. And then if I were to lock it at the beginning of a database transaction and then I do my stuff, it will basically say that this field, this record marked by this ID cannot be touched until— or can't, you know, in fact, if you, if you run a get record on it, it will block you from being able to use this set of code until such time as this whole thing finishes. And what that means is as soon as you've locked it, it will keep you from making any more changes or even reading from this particular table, because this table is now in a state you can't trust it, or this record's in a state that you can't trust it, it's locked. So the way that we would address this over in the, If I were to be running this again, well, actually, first let's take this idea and let's publish it for a second. Because once again, it's the kind of thing that like, the way it creates value is not obvious in the usual run and debug here in Xano. It's much more obvious when you publish it because it's about that race condition of having these two things that are going back and forth. So if I were to go over to say here, and well, let's figure out, okay, what does this thing say? I think it's initial messages, says initial again. All right, we're gonna call this, you know, lock test. Okay. And now what my expectation is, the first time you run it, it should say lock test again. And then the second time it should say lock test again, again. So the, if I were to run this from here, right, this is my browser version, which just says ID equals 1. I can run this and this will take like 10 seconds, right? It churns. Now if I kick this guy off, as I say, send, Now remember, each time we've run this in the past, it took like exactly 10 seconds for it to run because all the time was sitting inside the wait. But now we're gonna have something different going on, which is this says lock test again because it came back 10 seconds later, and then this guy came back. You see this says lock test again, again. That means that it correctly waited for this guy to finish before this guy finished. I'll do you one more. You see how over here it says it's 12.96 seconds? That's because in addition to the 10 seconds that it waited inside its own function stack, it waited 3 seconds for the previous one to finish. If I run this again, and I run this again, run that here, you'll again see that the second one to run takes longer because now in addition to doing whatever its own work was, it had to wait for the previous one to go, right? It's like you're in single-lane traffic and you're waiting for the previous car to go so that you can go. See, now it shows about 16 seconds because that's how long it had to wait relative to the first one being finished. And you can see how it now has 4 of them, right? Because we were— first we ran the, um, the web one, then we ran Postman, then we ran the web one, then we ran Postman. And like, even if I take this and I say I want to, you know, run this, you know, 1, 2, 3, 4. If I run it 4 times, it will actually not— it will keep on going, you know. And then now I have in the database— if I were to widen it a bit— See, now I've got 6. And basically, each time it does this, each time I sort of hit that refresh, if I say refresh, refresh, refresh. So now it ran it 3 more times. Right? So that I should expect that it goes from having 1, 2, 3, 4, 5, 6 of them to having, you know, 1, 2, 3, 4, 5, 6, 7. Good. 8. 9. So the— and you see how long it took for each of them to go because even though I said fire, fire, fire, when I was just running it off of the web request, it was taking those 10 seconds. Now, this time dimension is so critical because this is where we get to the side effects, the downside of using the database lock. It will slow down the train, right? That's the intentional thing. You're trying to slow down the train so you don't get the races. But at the same time, you're creating a situation where if you have something that's gonna take a little while and you back things up badly enough, well, if you get to the point where, you can easily actually get to the point where the browser's not gonna wait for it anymore. Because you have so many things that are backed up in line, you keep on hitting it hard, that it is, that has like 70 seconds worth of work to do, and the browser's not gonna wait for that. So even though it got kicked off, even though you're making the change on the backend, that change may not actually reflect in whatever web application you're using. This is something just to pay attention to. It's like, what are the dependencies you have for, that are upstream of making this work? And the— and do you require that this be able— this, this flow, right, like the API endpoint or whatever, be able to return correctly to the front end? This is a good reason, for example, to say that like if you're going to have something has a lock to it and your purpose is to be making writes to the database, and then you're— but it's really more fire and forget, you're not actually reporting that to the front end, uh, you might want to take something like this that has a potential— because even though it's only 10 seconds, it actually could take quite a long time if it's like backed not really in a queue. It's like the individual time for doing your order in the drive-through can be very, very fast, but if you've got to wait behind a whole big line of cars, then it can take a while. So often a good thing to mix into this is a utility function called basically do it later. It's called post-process, so that in the context of a situation like this, you might decide that what you really want to do is put it into a post-process. That way, the data won't be returned to the front end of what happened in here, right? Because basically lockable table 2 won't really be available to the response because it's going to respond before this stuff is done. Um, but it will make the same use of making sure that's not— these things aren't running into each other. So you've now separated the question of what's going to run slowly from what the responsiveness needs to be for the front-end browser experience. So these two things working can work in combination to give you kind of the best of both worlds, right? To both keep things going in sequence, as well as keep things going relatively fast in terms of the responsiveness of your application. The only reason to be getting into using database locks is because you have a relatively complicated situation to begin with. And so, you know, give yourself some, some forgiveness to be working it. But hopefully this is sort of showing you how to be making use of this particular tool that can be helpful. Remember that if you're going to be— if you're running into things that are running over each other, be looking for the database lock. Database locks only work when you're inside a database transaction, and the database transaction process, and like depending on how quickly these things run, you may well create problems in terms of your endpoints, and therefore you might want to consider, you know, putting into a post-process. Of course, if you're doing this from the context of like a background task or something, first you probably didn't have a race condition, but even if you did, you know, you don't need to worry about speed. So really, the, this kicking process or asynchronicity is going to be most important when you are, you know, dealing with API endpoints. Let me know if you got questions about this. And of course, this is the kind of, you know, more complicated stuff that we talk about all the time, especially the integration with like, you know, how to do this with more sophisticated AI systems that you might be linking to from your Xano. And we do that every day over in StateChange Office Hours.
