‫What is going on, guys?
‫This is a follow up lecture on the double booking prevention lecture that I made on this course.
‫And you guys had a lot of other, you know, workarounds to solve the double booking problem, you know,
‫and most of them are actually fine.
‫But one in particular that I actually liked and I want to, you know, explore.
‫I already answered this question many times in the in the Q&A section.
‫But, you know, wards are very hard, you know, to express everything.
‫It's always better to make a video and explain these in details.
‫You know, the the the reason I want to talk about this particular approach is, is that, you know,
‫to understand how things actually work when you when you issue this update statement to book a seat,
‫right?
‫So what we're talking about, obviously, I want you to obviously watch the double booking video before
‫jumping into this video, right?
‫Otherwise, you don't have context.
‫But if you if you notice what we did is we are trying to book a seat effectively, right?
‫And these are my seats effectively, right?
‫And let's clear up this thing so as to update seats set is booked equal zero, you equal now.
‫So it just cleared out everything.
‫So we don't we don't have any other anything booked, right?
‫So the idea we're building a system that is, I don't know, Airbnb cinema, you know, we don't want
‫to book a particular seat and we want to prevent the problem with it where two people book the same
‫seat effects of it effectively in a way that last when and which is the most dangerous kind of a situation,
‫right?
‫P2 people pay for a seat and two people, you know, kind of get this seat, but the last one actually
‫gets the seat.
‫That's very bad.
‫So the way I proposed to solve this is using this statement, which is select blah blah blah.
‫Four Update, right?
‫Which effectively creates takes an exclusive lock on the roll so that this transaction is free to do
‫all sorts of things until it commits so that it releases the lock.
‫Right?
‫And then the other transaction effectively, you know, Kent can resume.
‫Right?
‫So this way you have full control to kind of isolate the transaction.
‫You have full control to serialize the transaction.
‫This is a very common way.
‫But other people said it was.
‫And what if you did, you know, did another approach where you didn't select to check the seat of Steakin
‫and then to acquire the lock?
‫But no, just immediately do an update, right and then check the result of the update.
‫If the if the if the seats take it or not, this approach will definitely work.
‫But I wanted to explain why it is actually working and it's not actually guaranteed that is going to
‫work on all database systems.
‫That's why I want you guys to understand the fundamentals.
‫If you understand the fundamental fundamentals, you guys are basically unstoppable, right?
‫Because you understand the basics through any database that you're on, you and you basically understand
‫everything.
‫So let's take an example.
‫So I'm going to go ahead and begin this transaction here, and I'm going to begin another transaction
‫here and in this transaction.
‫I want to book this seat for Hussain, right?
‫Hassan is going to book seat one, right?
‫And in this transaction, let's say Rick is going to book the same seat CET1.
‫So we're going to do that.
‫A lot of people suggested this right.
‫Update seats set is booked equal, one the user is saying for which seat?
‫Right.
‫Sit sit for this seat, where I.D., which is the seat, I'd equal one.
‫And not only that, only one is booked is actually zero when it's not booked and the seat is the one,
‫go ahead and update their seat.
‫So go ahead and now updated the seat.
‫So I'm going to go ahead and do that.
‫And you can see them.
‫The transaction now successfully updated the rule.
‫Keep in mind, we did not commit the transaction yet.
‫So now in the other end, someone exactly made that almost exact identical statement because it is executed
‫from the application line, but is booked equal one by the user equal.
‫What Rick Ryan, where ID equal one and is booked equal zero.
‫And here's what I want you to to think about what is actually happening right now.
‫So here's what happened.
‫We updated the transaction we have did in a row.
‫Technically, we.
‫Acquired an exclusive lock on the role.
‫It's an update lock, so the rule is technically lock seat one is locked by the transaction implicitly,
‫not explicitly like the way we did it.
‫It's a different way.
‫Explicitly when you say, Oh, I want to select for update, I don't.
‫I'm going to do stuff I don't want to do.
‫I don't want anyone to touch my stuff for you, right?
‫So you're assuming the database will acquire this and will not do anything for you.
‫In this case, the database actually acquired a lock on the role for you.
‫All right.
‫So the role is now look.
‫That is why when I hit enter here that the transaction number two for REQ will be blocked.
‫Now, why is it blocked?
‫What exactly happened here?
‫I want you to answer these kind of questions and before you kind of pause the video and then answer
‫these questions now that you're in lecture, I don't know, 65 or 70.
‫You must understand these fundamentals.
‫You must understand the isolation level Reid committed.
‫You know, Reid uncommitted, uh, raw locks all these stuff.
‫You must understand it before.
‫If you're here, you probably do because you watched the previous thing.
‫So now what happened?
‫We did a work loss because we want to update something we did as an index search idea.
‫Call one and his book Equal Zero.
‫So what did what does the database do?
‫Well, it depends.
‫I I believe we have a primary key index.
‫Will Bosca's don't have primary keys, but you get my point.
‫It's a secondary key that points to the heap directly, but it's an index.
‫So Postgres Chris did a B tree traversal on the ID index to find ID number one.
‫It found the tuple I.D. and now it detected that this tuple is locked because of this transaction,
‫lock management of post-crisis say, Hey, wait a second, this this this thing is locked.
‫I'm not supposed to touch it, so I'm going to pull this until because I'm about to update it or I can't
‫update it because someone is actually, uh, locking it right?
‫So you can't even you can read it, which we did, right, but we cannot update.
‫So now the question is, if we read this right, we didn't go to the heap.
‫We're still in the index.
‫If we did go to the heap, right?
‫What do you think the value will be of the booked right?
‫The value of the book here is actually.
‫Zero.
‫It's not booked yet.
‫You might say I say no.
‫We just excuse the transaction.
‫No, yes, we executed the transaction, but this transaction is isolated.
‫It started the same time.
‫Right.
‫So this guy still sees the role as unblocked.
‫So that's why it managed to actually fetch that role.
‫So now we fixed that role, we found that this role is actually, you know, an booked so I can actually
‫fidget.
‫But when I tried to update it, I failed because there is a low lock on it.
‫Now what happened if I commit my transaction here?
‫When I commit my transaction, two things will happen, right?
‫It's like it really depends on where you are at the mercy of the database implementation.
‫I'm sorry you're at the mercy of the database implementation at this end.
‫Right?
‫Because the more they do commit, the roll will be locked.
‫I mean, there will be released, right?
‫I'm going to change these values and make them committed.
‫So the rule now will belong to Hussein and the book value is going to set to one.
‫So what will happen on this transaction, right?
‫Will that transaction?
‫Because remember the transaction already seen the roll on booked?
‫Is that even a word on what not booked, right?
‫So what will happen here?
‫Well, let's see what happened.
‫We committed that and immediately on the posters on the other end, it did not updated which which is
‫exactly what we want.
‫That's the desired behavior, but why?
‫If you really think about it, Paul here did a trick, right?
‫Slip off.
‫Yes, it is a little bit earlier, right?
‫So Bosca's did the trick here.
‫It actually refreshed the rule to look at the final result.
‫Right?
‫And then when it detected that, oh, the final results actually is booked equal zero.
‫No, it's not right.
‫Why?
‫Because when we did that at the world that actually was running the the the row was actually, in fact,
‫not booked.
‫But the moment this transaction committed now, the isolation level changed because this is read, committed,
‫right?
‫I'm allowed to read committed values that are here.
‫So now only if your isolation level is read committed and the database actually refreshes the values
‫of the row up uncommitted.
‫So now you can actually read the values here and make the decision.
‫As a result.
‫Right.
‫So and what I'd like to add is something I forgot to add here is I, I remembered after editing the
‫video as post-cruise actually stores.
‫The knowledge about lock information in the role itself is just another field in the row.
‫And you know, these columns live where obviously in the heap, right?
‫So when we go to the heap to fetch the is book value, that's when we looked at the lock information
‫and we obviously jump to another sort of data structure to fetch the value that is actually locked or
‫not.
‫Right.
‫That's when the that the database, the transaction decided to to block itself, right?
‫So that's that's that jump is very critical.
‫And when the row has been released, what happened here is Bosca's refreshed the heap value.
‫It actually said, OK, yeah, I'm own, I'm blocked, but I cannot just resume.
‫I have to kind of re execute the query in a sense, right?
‫To see the actual values?
‫Yeah, I have at a given point.
‫It was not book, but now how about now?
‫Is it actually booked, right?
‫And yeah, depends on the implementation.
‫And that's what I wanted to clarify, right?
‫This is how postscript that is it.
‫My sequel does it completely differently, right?
‫SQL Server does it completely differently.
‫They look in memory.
‫And can you guarantee the same result?
‫Absolutely not.
‫You might get lucky, right?
‫If you execute this query and you see that SQL Server Michael does it this way.
‫But I wouldn't unless you absolutely sure that this is how the database implemented this stuff.
‫Now you're you're at the at the bowels of the database implementation.
‫And basically, this is the power that I want you to have as an engineer, as someone who actually is
‫learning database engineering, just understand that this is all code.
‫You know, it's all written by human.
‫They make decisions, they get emotional, they write one piece of code, they get emotional, they
‫write another piece of code that gets broken.
‫You know, nothing's perfect.
‫Whatever we're looking at is written by human, you know?
‫And this is what I wanted to break, because when I first learned database engineering around 10 nine
‫years ago, I was always, you know, getting intimidated by all these terminologies.
‫But once you really, really, really understand what's going on, you're basically unstoppable because
‫you understand you're going to ask the right questions, guys.
‫And when you ask the right questions, if someone actually understand, they can reply back to you and
‫that will guide you to the answer.
‫You know, don't take things as surface value.
‫Always understand, always ask questions and the nature of the questions you ask.
‫And I start to see some people in this course actually ask these kind of questions, you know, dig
‫deep.
‫They kind of dig deep and try to, OK, why is it doing this way?
‫Why is it not done this way?
‫This is the great questions.
‫You know, nothing is perfect.
‫Nothing, absolutely nothing is perfect, though.
‫You see no anything that is there has been done by human and human or imperfect by nature, right?
‫So I know this got philosophical, but I really want you to understand this, you know, just any ask
‫the right questions, you're going to get the value.
‫And as a result, you're going to find the answer, right?
‫All right back to the video in that case, detective, that oh, the role is actually, oh yeah, it's
‫ID number one.
‫That's true, but it's actually it's booked, Ryan.
‫So that that that statement is false, which will break that condition.
‫I cannot guarantee what will happen in Bosca's if, for example, both of these are actually indexed.
‫You created a combined index where those two guys are actually index and you search index and you found
‫the values indexed right?
‫And you find that you did a search before that right and you found that the the row is actually not
‫booked.
‫The Bosca's might decide to say, Hey, it's not booked, let's go ahead and go ahead and commit.
‫You will never go to the heap to see the actual updated value.
‫Or maybe a whoa.
‫Right?
‫So that's why I'm saying that this will definitely work, but you really need to understand why it actually
‫worked.
‫Not because, uh, we're filtering on this.
‫No at work because of the database semantics and the acid transactions.
‫And there are so many other things that you really need to understand in order to get this.
‫So, yeah, definitely you can use this.
‫But the down the down side of using this approach is, first of all, you need to be in committed mode
‫to do that.
‫Second of all, you really don't have.
‫Much control when it comes to this transaction, you only you're at the mercy of that particular update
‫statement.
‫But what if you, for example, want to do multiple things right?
‫I want to.
‫I want to book that.
‫I want to.
‫I want to take a look on that role.
‫All right.
‫And then do multiple things in this case before I commit.
‫So you can with explicit looking, you have a little bit more lenient way, right?
‫You don't your database doesn't do like implicit things for you to behind the scenes that you don't
‫understand, right?
‫So I kind of prefer the for update approach, but this definitely work.
‫And obviously, as as some people said, this is kind.
‫More efficient.
‫I'm hesitant to say that, but uh, it's just that in this approach, we're we're also taking a lock,
‫but the lock is is is is a database internal lock, not a user lock that we have maintained, right?
‫So there will be a lot of cases where you take a lock that you did actually didn't need to take, right?
‫But again, at the end of the day, Postgres and other database system is a pessimistic, you know,
‫pessimistic concurrency control, which means they always take a look.
‫Right?
‫They always take the umbrella with them as as I always say that they always they can look.
‫And at the end of the day, yeah, maintaining managing locks are expensive.
‫But when you manage the locks right, you take that you'll be protected from these kind of situations.
‫Other approach where you become optimistic on cancer control, where, hey, I'm not going to take a
‫lock, right?
‫But at the end of the transaction, I'll detect the change and feel the transaction.
‫I prefer pessimistic personally because, you know, I don't want to retry my transactions.
‫I know NoSQL databases always prefer that approach.
‫Nothing wrong with that.
‫I just a personal preference.
‫At the end of the day, I don't like my transaction if I want to.
‫If my transaction will fail, I want it to fail for the right reasons.
‫I want to.
‫I don't want it to fail because of a concurrency problem.
‫Because that's just another condition in your application that you have to take care of.
‫All right.
‫I think that's it for me today.
‫Hope you enjoyed this video Uh.
‫And uh, enjoy the course.
‫You may.