1 00:00:00,060 --> 00:00:01,860 ‫Is going on, guys. 2 00:00:01,880 --> 00:00:08,760 ‫My name is Hossein, and this video I would like to discuss to phase locking with a practical example, 3 00:00:08,790 --> 00:00:18,270 ‫guys, so Two-Faced locking is the idea of having a acquiring database locks and releasing them in phases, 4 00:00:18,450 --> 00:00:22,340 ‫basically to phase the first phases, acquire, acquire, acquire, acquire. 5 00:00:22,560 --> 00:00:25,440 ‫And the second phase is release, release, release, release. 6 00:00:25,650 --> 00:00:28,540 ‫And the trick here, once you release, you cannot acquire. 7 00:00:28,800 --> 00:00:35,850 ‫How about we translate all of this stuff to an actual good example, a practical example with a very, 8 00:00:35,850 --> 00:00:37,950 ‫very common problem calls that double booking. 9 00:00:38,200 --> 00:00:43,520 ‫So a double booking is when we're trying to build as, let's say, a cinema booking system. 10 00:00:43,530 --> 00:00:43,810 ‫Right. 11 00:00:44,190 --> 00:00:51,870 ‫And two users are trying to book the exact same seat at the exact same millisecond. 12 00:00:52,200 --> 00:00:58,290 ‫And when that happens, you will end up with a double booking problem if you don't have to face locking 13 00:00:58,290 --> 00:00:59,840 ‫or another implementation. 14 00:01:00,180 --> 00:01:00,510 ‫Right. 15 00:01:00,630 --> 00:01:05,400 ‫And what happens here is that two transactions will get executed at the same time. 16 00:01:05,700 --> 00:01:10,770 ‫They will both check the database and they will both check that the oh, the seat is available, let 17 00:01:10,770 --> 00:01:11,310 ‫me book it. 18 00:01:11,460 --> 00:01:16,470 ‫And they will all both commit to the same time and leading in problem with double booking it. 19 00:01:16,620 --> 00:01:19,020 ‫That's actually extremely undesirable. 20 00:01:19,020 --> 00:01:25,860 ‫Right, because you had two people paying for the same seat and they the last one went essentially in 21 00:01:25,860 --> 00:01:27,660 ‫this case, which is absolutely bad. 22 00:01:28,260 --> 00:01:35,870 ‫So what do we want to do as the first person the who pays will immediately reject the second person? 23 00:01:35,880 --> 00:01:36,710 ‫That's what we want to do. 24 00:01:36,720 --> 00:01:41,550 ‫How about we actually jump into it and show you that at the database level? 25 00:01:41,610 --> 00:01:41,830 ‫Right. 26 00:01:42,120 --> 00:01:46,410 ‫So, guys, I talked about the double booking problem from a Web application point of view. 27 00:01:46,410 --> 00:01:55,320 ‫If you want to see more and more hands on Web application framework thing, check out the video I made 28 00:01:55,320 --> 00:01:55,590 ‫here. 29 00:01:55,860 --> 00:01:59,700 ‫But I'm going to talk from a database level perspective here. 30 00:01:59,730 --> 00:02:04,620 ‫How about we actually go ahead and start a transaction here and we're going to go ahead and start a 31 00:02:04,620 --> 00:02:05,480 ‫transaction here? 32 00:02:06,540 --> 00:02:06,860 ‫All right. 33 00:02:06,870 --> 00:02:12,990 ‫So what I'm going to do here, this user is about to book seat number thirteen. 34 00:02:13,710 --> 00:02:18,660 ‫So what I'm going to do a will ask to check of the seat is available or not. 35 00:02:18,670 --> 00:02:18,940 ‫Right. 36 00:02:19,230 --> 00:02:26,400 ‫So let's start from seats where ID equal 13 and says, hey, it's it's available. 37 00:02:26,910 --> 00:02:33,030 ‫And the second transaction concurrently at the same time does the same exact thing from seats where 38 00:02:33,030 --> 00:02:34,200 ‫it equals thirteen. 39 00:02:34,710 --> 00:02:35,790 ‫Hey, awesome. 40 00:02:35,790 --> 00:02:36,530 ‫It's available. 41 00:02:36,750 --> 00:02:42,930 ‫So what this transaction does I update update seats, it is booked equal one because it's now booked 42 00:02:43,170 --> 00:02:49,800 ‫and the name is now Hassane where ID is equal thirteen also. 43 00:02:50,190 --> 00:02:53,580 ‫So and the same thing happens on this transaction. 44 00:02:53,790 --> 00:03:01,230 ‫So they update seats set is booked equal one name equal edman. 45 00:03:01,260 --> 00:03:06,240 ‫That's the user here where already equal thirteen also. 46 00:03:06,390 --> 00:03:06,810 ‫Right. 47 00:03:06,990 --> 00:03:09,930 ‫And you'll start seeing that this transaction is waiting. 48 00:03:09,960 --> 00:03:12,440 ‫This is a specific behavior to Posterous. 49 00:03:13,200 --> 00:03:19,140 ‫However, what we happen here is like the moment I commit here, this transaction also commits. 50 00:03:19,290 --> 00:03:19,740 ‫Right. 51 00:03:19,980 --> 00:03:25,440 ‫And when I do that, I'm going to to go ahead and and let's say select star from before we commit the 52 00:03:25,440 --> 00:03:30,480 ‫other transaction, I'm going to select Star from seats here where I'd equal thirteen. 53 00:03:30,750 --> 00:03:32,760 ‫Hey, looks good. 54 00:03:33,660 --> 00:03:36,780 ‫Send an email to the user that oh this seat is yours. 55 00:03:37,110 --> 00:03:45,360 ‫But at the same time, the other transaction now just committed the transaction and the moment you select 56 00:03:45,360 --> 00:03:49,200 ‫again now the transaction belong to someone completely different. 57 00:03:49,350 --> 00:03:52,860 ‫So that's the double booking problem, guys, that we're trying to avoid. 58 00:03:53,420 --> 00:03:53,820 ‫All right. 59 00:03:54,030 --> 00:03:58,940 ‫So one way to avoid this is to do two phase locking guys, right? 60 00:03:59,100 --> 00:04:04,770 ‫So the idea here is how about we start again and do the two phase locking here? 61 00:04:04,980 --> 00:04:10,230 ‫So I'm going to do here is going to do select begin transaction, begin transaction. 62 00:04:11,040 --> 00:04:13,830 ‫And let's assume I'm going to book. 63 00:04:14,870 --> 00:04:21,140 ‫Seat number 14, because it's not booked and here's where I'm going to I am going to book seat number 64 00:04:21,140 --> 00:04:24,010 ‫14 here and I'm going to also book seat number 14. 65 00:04:24,020 --> 00:04:26,330 ‫But this time I'm going to implement two phase locking. 66 00:04:26,670 --> 00:04:27,860 ‫So what is two phase locking? 67 00:04:28,010 --> 00:04:35,450 ‫I'm going to obtain an exclusive lock on the row that that I am about to update. 68 00:04:35,450 --> 00:04:36,400 ‫And here's how I do it. 69 00:04:36,740 --> 00:04:42,530 ‫I'm going to do select start from seats where I'd equal 14. 70 00:04:43,070 --> 00:04:46,550 ‫But what I add is for update. 71 00:04:46,730 --> 00:04:53,990 ‫And when I do for update, select for update that rule, that object that tuple will have an exclusive 72 00:04:53,990 --> 00:04:54,200 ‫lock. 73 00:04:54,200 --> 00:04:55,910 ‫So I am now in phase one. 74 00:04:55,910 --> 00:04:57,260 ‫I'm growing my locks. 75 00:04:57,260 --> 00:04:57,590 ‫Right. 76 00:04:57,860 --> 00:05:02,090 ‫And the moment I do that, nothing seems different here. 77 00:05:02,120 --> 00:05:05,300 ‫I stuck on my result, but there is a something change here. 78 00:05:05,720 --> 00:05:09,770 ‫We acquired an exclusive lock on the roll. 79 00:05:09,890 --> 00:05:17,900 ‫So that means if the second person tries to do the exact same thing where I'd equal 14, whatever they 80 00:05:17,900 --> 00:05:21,470 ‫do for update or not, look where they happen, they're stuck. 81 00:05:22,220 --> 00:05:29,270 ‫That's because they also tried to obtain an exclusive lock on the row itself. 82 00:05:29,570 --> 00:05:35,390 ‫And that operation is now stuck because you cannot obtain exclusive lock because that lock has already 83 00:05:35,580 --> 00:05:37,130 ‫been obtained by another transaction. 84 00:05:37,320 --> 00:05:39,800 ‫That's the rules of the exclusive lock. 85 00:05:39,980 --> 00:05:43,790 ‫And if you want to learn more about exclusive locks, go check out the video I made here. 86 00:05:44,600 --> 00:05:45,110 ‫All right. 87 00:05:45,320 --> 00:05:47,450 ‫So it says, OK, this transaction is happy now. 88 00:05:47,450 --> 00:05:57,170 ‫Says, OK, update seats set is booked equal one and Namik all housing where I'd equal fourteen and 89 00:05:57,170 --> 00:05:58,250 ‫then boom. 90 00:05:58,400 --> 00:06:04,190 ‫And once I'm done, I'm going to commit and look what will happen here the more I commit. 91 00:06:06,960 --> 00:06:14,620 ‫This transaction is immediately unlike what happened when I committed I entered phase two of the phasing 92 00:06:14,880 --> 00:06:21,210 ‫of the two phase lock, which is the release phase, the shrinking phase, the moment I committed or 93 00:06:21,210 --> 00:06:25,650 ‫I lower back, what happened is I released that lock that I obtained here. 94 00:06:25,770 --> 00:06:26,430 ‫So it's OK. 95 00:06:26,670 --> 00:06:27,690 ‫I changed it. 96 00:06:27,750 --> 00:06:30,390 ‫Now it's safe for other people to read it. 97 00:06:30,630 --> 00:06:31,890 ‫And that's what we want. 98 00:06:32,280 --> 00:06:40,140 ‫The moment that transaction now reads this says oh oh, this road, this road blog to Hosain, this 99 00:06:40,140 --> 00:06:44,580 ‫seat belong to her saying I cannot book it so the user will get an error on this case. 100 00:06:44,580 --> 00:06:47,580 ‫So your application will throw in it on that case. 101 00:06:47,700 --> 00:06:48,160 ‫All right, guys. 102 00:06:48,180 --> 00:06:52,200 ‫So that was like two phase locking in a very short video to explain that. 103 00:06:52,200 --> 00:06:53,910 ‫Again, guys, check out the other videos. 104 00:06:54,480 --> 00:07:00,360 ‫What I took, I put this within an actual application with a user interface and all that jazz, our 105 00:07:00,360 --> 00:07:00,690 ‫guys. 106 00:07:00,690 --> 00:07:01,290 ‫That's it for me. 107 00:07:01,290 --> 00:07:05,820 ‫A very quick video today to show you about two phase locking I'm going to see in the next one. 108 00:07:06,000 --> 00:07:07,870 ‫You guys stay out.