‫Hey, guys, what's up and this video I want to talk about, how can you build an online booking system
‫and not just the actual act of building an online booking system, but the most important feature,
‫which is prevent double booking or prevent race condition when two people try to book the same seat
‫at the same exact millisecond?
‫OK, I've seen a lot of systems that actually don't have this problem solved.
‫Right.
‫So I'm going to talk about it.
‫I'm going to talk about how to prevent this condition, can talk about exclusive locks, ride.
‫And I'm not going to show you how I built this application because it's very straightforward, not really
‫magic.
‫I might I might make another video to actually show you how the how I built the application from scratch.
‫It's it's it's really straightforward.
‫I'm using Express, Norges and Postgres as the back.
‫I'm going to go through the application right now.
‫The idea here as we are building an online system that books, I don't know, seats.
‫Right.
‫You're you're buying tickets at a concert.
‫And this is one user and this is just another instance, maybe another user.
‫Right.
‫And trying to open that.
‫And these are seats.
‫Seats are green.
‫I mean, they are available.
‫Red are booked.
‫OK, how about we actually try to implement this thing?
‫So if I click on this seat number three and I say, hey, my name is Ali, I know in a book this seat,
‫I'm going to get back a message, say, hey, this was booked successfully.
‫Right.
‫And we're going to go through the logic.
‫And now what what exactly am I doing here?
‫And if I do this and I'm going to fresh here, I can see that this now is booked by Ali.
‫You can see there is a tooltip here.
‫I didn't I really it's a bad front.
‫And I know it's just does the trick.
‫And if I go to the other using a refresh, you can see that this has taken effect or so of that user
‫like, say, book jack, book number nine.
‫And you can see that I am now actually debugging this and I'm going to show you exactly what happened.
‫So how about we go through the code and I made a put request here.
‫And what we're doing here is we get the ID, which is the seat ID and the name from the prompt.
‫And I am connecting through a Posterous pool.
‫So I get to pick up one client from the pool.
‫And we talked about Norges Postgrads and how we actually make transactions.
‫I'm going to reference the videos here.
‫If you want details this, I'm going to go quickly through them.
‫I'm going to begin a transaction because obviously this is a transaction.
‫Right?
‫And the first thing we're going to do in the transaction is this.
‫I'm going to select Star from Seeds.
‫I need to actually query this seat and see if it's actually booked or not.
‫That makes sense.
‫Right.
‫A lot of booking system actually does that.
‫And so where ID is a good one and is booked equals zero.
‫That means it's not it's not booked.
‫And that's perfect.
‫When I do this query, I'm going to get the result back.
‫And the real count is one that means, hey, you actually found this seat and it's available for you.
‫All right.
‫So far, so good.
‫But if that seat is not available, you're going to get an error.
‫We're going to send back in our seats already booked.
‫This is to avoid this double booking.
‫But we're going to show that this is not enough in a minute.
‫Right.
‫A lot of people actually do this.
‫This party, Aquarian, check that thing.
‫But this is not enough.
‫So I'm going to go and once we actually pass this, that we are safe to actually execute an updated
‫statement and said to the go away, please, is his book the equal one and the name equal, then where
‫it equal to one?
‫We just physically set that statement.
‫So let's go ahead and just run this thing and we commit the transaction.
‫We release the client back to the pool.
‫Right.
‫And then we send the result till we get back.
‫We get back also here booked successfully.
‫Right.
‫And then if for a year.
‫Right.
‫And I'm not going to refresh actually, if I actually click this nine now and I'm going to book it for
‫which is already book, we know that it's book.
‫Right.
‫If I say I don't know, saying that, OK, we get an error failed.
‫Couldn't book because it's already booked and the reason is because we have queried and that Kuwait
‫actually failed.
‫Returned.
‫There's no record on gonorrhea.
‫All right, Hosain, what are you doing that all of the stuff, y'know, here is the floor of this design.
‫The problem with this design is.
‫Double booking can easily happen, not on my single machine, obviously, but if you have a flux of
‫requests coming at the same time, you will get into a situation where both statements actually at the
‫same time you can imagine them executing impera.
‫They query the seat is available right.
‫The first transaction query and says, oh, the seat is available and it reaches here.
‫And the second query also queries and also finds out that the seat, the same seat is also available.
‫Right.
‫So both of them will succeed on this if statement.
‫They will pass it and both of them will execute the update.
‫And that's the problem in this case last to an end.
‫But incorrectly, that client will get the message that actually your booking has been succeed.
‫And we saw a lot of back back maybe 10 years ago when online cinema became a thing.
‫Right.
‫We always get double booking because of this problem, because it's a popular system.
‫Right.
‫A lot of people quit at the same time.
‫So we're going to show how to fix that.
‫First of all, let's reproduce this.
‫Let's show you actually this is going to happen, but I'm going to do this with the power of debugging
‫is what I'm going to do.
‫I am going to book I'm going to book seat number 15 now, and I am going to sign him to wreck.
‫All right.
‫And this will essentially go to the debugging statement, passes all of that stuff.
‫And I'm going to post that here.
‫I'm going to intentionally not resume the code to go to continue.
‫But guess what?
‫It actually passed the select statement that actually checks whether whether the it exists or not.
‫So the seat is available.
‫Here's what I'm going to do.
‫The seat is available.
‫But that guy did an updated.
‫Yet Rick did not actually reserve the seat because he didn't reach the statement.
‫Right.
‫Now, if I go to the other client and I also try to book this guy and I say, Edmund, want to book
‫this guy this, I don't have a debugger.
‫This guy will completely succeed because there's no debugging.
‫Right.
‫We'll find out this is available and it will actually commit the transaction or this is book.
‫Now, here's what I do now.
‫I'm going to just release the code, go ahead and continue.
‫And you can see that both of them actually booked the same seat.
‫And that's the problem.
‫This is what it's called, race condition in online system or double booking.
‫And here is how we can fix this problem, guys, using the concept of exclusive locks, how about we
‫jump into it and fix it?
‫The problem here, guys, is.
‫Both of these guys are attempting to edit the same row technically, right?
‫So the solution, one solution at least as.
‫The first person or process to get to the role should obtain an exclusive lock on that row so that the
‫other process, when it tries to execute it, it will have to wait until the other transaction actually
‫completed.
‫And how do we do this in postgrads?
‫It is very simple.
‫The same thing you select that you're selecting this role, right.
‫The only thing you need to do is add two things at the end.
‫So for update, that's it.
‫When you do select star from Select where ID and you query that role.
‫You also say for update and that is a call, the role level luck and few databases actually support
‫that.
‫Not all databases.
‫Right.
‫So this is a very important feature in some databases, I suppose.
‫I think MySQL also supported.
‫Oracle definitely supported.
‫And you do it for update.
‫And the syntax 692 is almost identical in all of them.
‫So you do select staff for updates.
‫Wow.
‫All right.
‫The moment you do that, the database will have an exclusive lock on that role.
‫That means if someone tries to update that rule or selected for update as well, they will have to wait.
‫And this is what I don't like about progress in Oracle, because I use Oracle for 15 years and an oracle.
‫That's why Oracle is expensive.
‫One of the reasons, as in Oracle, you can actually specify a time out here, says, hey, wait four
‫or five seconds right here is actually an indefinite wait.
‫I'm going to show that now in a minute.
‫All right.
‫So let's go ahead and try to do now that we have fixed that, let's refresh this thing and let's also
‫rerun our my second application right on board, eight, eight, eight, eight.
‫And let's check this up.
‫Right.
‫And for fun, let's just clear all that box.
‫Right.
‫I'm going to update Sytek will push back.
‫What a double this.
‫No, no one is looking at anything just like that.
‫All right.
‫Everything is now unbooked.
‫Sweet guys.
‫All right, here's what I'm going to do.
‫I'm going to do the same thing.
‫Going to book seat fifteen.
‫I'm going to one, for example, booking seats fifteen.
‫We go there, hopefully the debugger anytime soon now.
‫OK, we selected the same stadium, but now it's for update.
‫There is a little bit of difference in the database.
‫It's freaking out now, right?
‫We got it now.
‫Here's the thing.
‫This guy will always be the winner no matter what, I'm going to find out how.
‫So the other process now will try to book 15.
‫Was it 15?
‫I think it's 15.
‫Let's just make sure I think it's 15.
‫Yep, 15 or so.
‫15.
‫If I click 15 and I say, Melissa, I want to book 15 is what we're going to do.
‫Look at that, we didn't receive anything is just waiting.
‫Now, the process is just stock ride and this is what I don't like, how our policy there is no option.
‫Am I wrong?
‫Correct me if I'm wrong.
‫There is no option to actually give up and time out after after a certain amount of waiting.
‫Right.
‫So this guy is waiting and this is a bad user experience.
‫That's the limitation of this now.
‫And this is where your database actually shines.
‫Now, if I go and say, release that cracken here, go back this guy.
‫This is what happened.
‫Something happened in your right book successfully is the guy who actually the person who actually made
‫that transaction book successfully, but this guy was waiting technically right here.
‫They were stuck.
‫And the moment this process actually released the transaction, what happened is that Locke got unblocked
‫and now we selected the brand new committed change, which was what it was actually committed, which
‫was the rate is already booked.
‫I mean, this result is zero.
‫That means this either already booked.
‫All right.
‫And that's what happened, essentially, guys.
‫So this is now a bulletproof online booking system that is essentially race safe or or you cannot get
‫double booking with them, obviously comes with a price.
‫But that price, remember, nobody in their right mind will put a debugger, obviously, and this will
‫take a finite amount of time.
‫Milliseconds is very fast.
‫So the wait will almost be unnoticeable.
‫Right.
‫So that's only a good encoding book already booked, right.
‫All right, guys, that was a short video talking about this.
‫Let me know, guys, if you actually want me to build this application from Scott showing you how to
‫highlight what all the species we explained all that stuff before everything.
‫Right.
‫That gate requires.
‫This is a port also for fun.
‫I added a delete message to move it, to delete that booking, to unblock that thing.
‫And obviously, this is a very simple I'm going to reference the source code below for you guys if you're
‫interested.
‫And the database also, it's just a Bosco's on Docker, very straightforward.
‫And all the tutorials, all this post Christodoulos, we have done them before.
‫Let me know if you have any questions.
‫Let me know if I missed anything.
‫I'm going to see you on the next one.
‫You guys stay awesome.