1 00:00:00,060 --> 00:00:07,680 ‫Hello and welcome to another Q&A session for the month of March 20, 22. 2 00:00:08,220 --> 00:00:11,340 ‫Today, I have a wonderful question from our tour. 3 00:00:12,150 --> 00:00:13,560 ‫Arthur, Arthur. 4 00:00:13,740 --> 00:00:14,250 ‫Arthur. 5 00:00:15,150 --> 00:00:17,280 ‫Yeah, fantastic question. 6 00:00:17,790 --> 00:00:18,780 ‫Fantastic question. 7 00:00:18,780 --> 00:00:19,800 ‫Absolutely fantastic. 8 00:00:19,830 --> 00:00:20,940 ‫The question goes like vol 9 00:00:23,550 --> 00:00:27,630 ‫Lockwood's serialize Zabel isolations, generalizable isolation. 10 00:00:28,220 --> 00:00:30,930 ‫I say saying at three 3123. 11 00:00:30,960 --> 00:00:36,330 ‫You mentioned that the realizable isolation level transaction are run one after another. 12 00:00:37,020 --> 00:00:42,810 ‫So this is regarding two one on the isolation lecture. 13 00:00:43,680 --> 00:00:48,180 ‫So what's the point of lock with pessimistic implementation with select for updates? 14 00:00:48,180 --> 00:00:48,490 ‫So. 15 00:00:48,810 --> 00:00:49,070 ‫All right. 16 00:00:49,080 --> 00:00:50,500 ‫So fantastic question. 17 00:00:50,520 --> 00:00:51,840 ‫So two things here. 18 00:00:52,590 --> 00:00:54,700 ‫Let's talk about serialize the ability. 19 00:00:54,720 --> 00:01:02,520 ‫This idea of serializing things is you have ten concurrent transactions that came to your database at 20 00:01:02,520 --> 00:01:10,590 ‫the same time to make them realizable, which means each transaction should execute one right after 21 00:01:10,590 --> 00:01:11,040 ‫the other. 22 00:01:11,070 --> 00:01:13,260 ‫That's the feel that needs. 23 00:01:13,530 --> 00:01:15,360 ‫That's the final result that you need to get. 24 00:01:15,840 --> 00:01:22,020 ‫And you can implement that if you will want to implement that for serialization isolation level for 25 00:01:22,020 --> 00:01:22,350 ‫a second. 26 00:01:22,380 --> 00:01:29,460 ‫Now, if you want to implement that, you can use pessimistic concurrency control by using this select 27 00:01:29,460 --> 00:01:30,780 ‫for update and. 28 00:01:31,930 --> 00:01:35,150 ‫That order mentioned here I talked about many times in this course. 29 00:01:36,160 --> 00:01:41,440 ‫Literally, each transaction tries to obtain an exclusive lock right on a certain row. 30 00:01:42,040 --> 00:01:49,510 ‫And since only one transaction can obtain that lock the rest of the transaction, the nine transactions 31 00:01:49,510 --> 00:01:53,440 ‫will be blocked and waiting for that lock to be freed. 32 00:01:54,310 --> 00:01:57,970 ‫So the first transaction will finish, commit, and then that lock will be gone. 33 00:01:58,330 --> 00:02:04,750 ‫The second transaction immediately, whoever wins by this fraction of a microsecond will get that lock 34 00:02:04,930 --> 00:02:08,800 ‫and that it the rest eight transaction will be blocked, obviously. 35 00:02:09,010 --> 00:02:12,370 ‫And then until all of them will finish one right after, although. 36 00:02:12,370 --> 00:02:13,320 ‫So what's the problem with this? 37 00:02:13,960 --> 00:02:17,230 ‫The problem with this is you lost concurrency. 38 00:02:17,410 --> 00:02:20,410 ‫You killed your parallelism and concurrency. 39 00:02:20,740 --> 00:02:21,940 ‫You don't have throughput. 40 00:02:22,270 --> 00:02:28,660 ‫But the good thing about this is it's so simple to implement it this way and you won't get have any 41 00:02:28,660 --> 00:02:29,410 ‫side effect. 42 00:02:30,030 --> 00:02:30,230 ‫Right. 43 00:02:30,580 --> 00:02:32,020 ‫So there are tradeoffs here. 44 00:02:32,050 --> 00:02:33,460 ‫So it's very simple to implement. 45 00:02:34,660 --> 00:02:37,600 ‫But you slowed down your transactions. 46 00:02:37,990 --> 00:02:39,580 ‫You slowed down your queries. 47 00:02:39,790 --> 00:02:42,730 ‫If you have many, many couldn't get its queries at the same time. 48 00:02:43,660 --> 00:02:47,980 ‫So so that's that's basically how serialization supposed to work. 49 00:02:48,400 --> 00:02:53,710 ‫Serialized XBL isolation level is smarter than that is not really. 50 00:02:54,250 --> 00:03:04,360 ‫It gives you the final result as if all of these transactions have executed one after the other without 51 00:03:04,360 --> 00:03:10,270 ‫actually blocking them, they are executed concurrently. 52 00:03:10,600 --> 00:03:13,030 ‫So my say I was saying how that's the magic. 53 00:03:13,630 --> 00:03:18,010 ‫The database will detect if these transactions are in isolation. 54 00:03:18,490 --> 00:03:20,200 ‫In their serialized isolation. 55 00:03:20,650 --> 00:03:21,130 ‫They will. 56 00:03:21,670 --> 00:03:22,720 ‫They will see. 57 00:03:22,750 --> 00:03:23,050 ‫Okay. 58 00:03:23,320 --> 00:03:29,410 ‫Did I read something that someone just wrote or did something did someone read something that I just 59 00:03:29,410 --> 00:03:29,770 ‫wrote? 60 00:03:30,610 --> 00:03:34,900 ‫And it detects these by using different kind of lock that are none, none blocking. 61 00:03:35,650 --> 00:03:44,860 ‫And if those are detected, if there was a conflict in the sense that there was a serialize able and 62 00:03:44,860 --> 00:03:52,060 ‫or the serialization error, then the transaction that is about to commit will actually fail. 63 00:03:52,300 --> 00:03:55,570 ‫It says, hey, you know what, you did touch? 64 00:03:55,570 --> 00:03:57,400 ‫You touch something that someone read. 65 00:03:57,550 --> 00:03:59,500 ‫And as a result, this will. 66 00:04:01,320 --> 00:04:06,480 ‫Affect the final result right off of the entire transaction series. 67 00:04:06,900 --> 00:04:09,270 ‫And as a result, sorry, you have to fail. 68 00:04:09,720 --> 00:04:11,790 ‫And this is called Optimistic Concurrency Control. 69 00:04:11,790 --> 00:04:14,580 ‫So optimistic and classic control. 70 00:04:14,580 --> 00:04:20,910 ‫And I talked about this, doing that in the course where, hey, let both of them try to change and 71 00:04:20,910 --> 00:04:22,080 ‫will be optimistic. 72 00:04:22,080 --> 00:04:27,030 ‫We'll try to be optimistic in a sense that, okay, nobody will step on each other, so nobody will 73 00:04:27,030 --> 00:04:33,510 ‫actually touch, read something that someone is changing or write something that someone else was read. 74 00:04:34,110 --> 00:04:35,850 ‫So that will be fine. 75 00:04:35,970 --> 00:04:36,360 ‫Right. 76 00:04:36,690 --> 00:04:42,600 ‫So you go with the idea that optimism, a little bit of optimism that nobody will step on each other 77 00:04:42,600 --> 00:04:43,020 ‫at all. 78 00:04:43,290 --> 00:04:48,240 ‫And as a result, you can you may continue. 79 00:04:48,270 --> 00:04:56,820 ‫But if as a realizable error is detected in a sense that someone read someone else's data or someone 80 00:04:56,820 --> 00:05:03,360 ‫read someone else's write, that is when an optimistic concurrency control error will be thrown. 81 00:05:03,570 --> 00:05:08,790 ‫And you're going to get this error that says, okay, serialization failed and retry you transaction. 82 00:05:08,790 --> 00:05:11,370 ‫So the good thing about it is you still got a concurrency. 83 00:05:11,730 --> 00:05:17,880 ‫The bad thing about selling the visible isolation level is you transactions might fail. 84 00:05:19,140 --> 00:05:20,220 ‫This is not true. 85 00:05:20,250 --> 00:05:22,290 ‫What the pessimistic concurs, if at all. 86 00:05:22,410 --> 00:05:27,300 ‫They will never fail as long as they don't have time, obviously, that they will not avail. 87 00:05:27,330 --> 00:05:30,720 ‫They'll just take a little bit longer time to do it, but they'll never fail. 88 00:05:30,990 --> 00:05:34,680 ‫So it's it's at the end of the day, it's it's a trade off. 89 00:05:34,920 --> 00:05:35,940 ‫What do you want? 90 00:05:36,150 --> 00:05:42,810 ‫Do you want faster, potentially faster rights with multiple transactions if you have so many concurrent 91 00:05:42,810 --> 00:05:43,440 ‫transactions? 92 00:05:43,950 --> 00:05:44,370 ‫Right. 93 00:05:44,430 --> 00:05:52,950 ‫Or do you want a simpler back end design where we don't really have complex cases where, oh, this 94 00:05:52,950 --> 00:05:58,460 ‫transaction won't fail, I have to retry it later and do all this silly logic. 95 00:05:58,470 --> 00:06:00,210 ‫You know, it's all depends up to you. 96 00:06:00,390 --> 00:06:07,620 ‫So these tools exist for you and it really is up to you to use them or not. 97 00:06:08,300 --> 00:06:12,740 ‫I personally prefer to use select for update in certain cases. 98 00:06:12,750 --> 00:06:20,220 ‫Obviously not all the time for for use case of what I know this this operation will not be executed 99 00:06:20,220 --> 00:06:21,210 ‫concurrently a lot. 100 00:06:21,210 --> 00:06:27,660 ‫I use select for update if I want to ensure they are realizable, you know, but if there are many possible 101 00:06:27,660 --> 00:06:33,810 ‫concurrency and I need to, you know, I need a lot of transactions to be executed at the same time 102 00:06:34,320 --> 00:06:36,840 ‫and I know that nobody will like it. 103 00:06:37,350 --> 00:06:42,630 ‫If you think about this as someone editing some some some data, you know that nobody will edit the 104 00:06:42,630 --> 00:06:44,580 ‫same data if you are sure about that. 105 00:06:44,580 --> 00:06:50,160 ‫And you can redesign your client such in a way that nobody will step on each other still. 106 00:06:50,430 --> 00:06:55,870 ‫Then you can use optimistic on classical drone and I sorry, I said realizable isolation level. 107 00:06:55,890 --> 00:06:56,370 ‫All right, guys. 108 00:06:56,580 --> 00:06:58,440 ‫That's it for today. 109 00:06:58,500 --> 00:06:59,250 ‫Got to see you in the next one.