1 00:00:00,060 --> 00:00:04,380 ‫Hey, guys, in this video, we're going to describe the difference between a serializable isolation 2 00:00:04,380 --> 00:00:07,080 ‫level and unrepeatable isolation level. 3 00:00:07,260 --> 00:00:11,610 ‫I've searched the Web and they're very, very, very low resources talking about this. 4 00:00:11,620 --> 00:00:14,970 ‫So I wanted to make a video to talk about the difference between them. 5 00:00:14,970 --> 00:00:18,950 ‫And then why would you pick serializable isolation level over? 6 00:00:19,080 --> 00:00:20,030 ‫No repeatable. 7 00:00:20,160 --> 00:00:22,170 ‫Let's jump into it, guys. 8 00:00:22,290 --> 00:00:30,520 ‫So here's an example where you have a Reid committed or a repeatable isolation level. 9 00:00:30,820 --> 00:00:35,610 ‫OK, so let's say we have a table, we have A here values and B here. 10 00:00:35,660 --> 00:00:42,810 ‫I assume this is a table field and these are just for values, for rows with these kind of values. 11 00:00:43,050 --> 00:00:45,750 ‫Transaction number one changes A's to B's. 12 00:00:45,960 --> 00:00:49,600 ‫Transaction number two, the green one changes B's to A's. 13 00:00:49,620 --> 00:00:55,280 ‫So it's literally update table set field equal, B where field equal. 14 00:00:55,680 --> 00:00:57,640 ‫And this the other one is the reverse. 15 00:00:57,810 --> 00:01:03,210 ‫So what happens if they execute concurrently so that both of them start at the same time? 16 00:01:03,450 --> 00:01:08,250 ‫And then this transaction starts, obviously changes all the A's to B's. 17 00:01:08,250 --> 00:01:11,440 ‫And so it's only going to these two rows. 18 00:01:11,460 --> 00:01:12,690 ‫So it's going to become DB. 19 00:01:13,080 --> 00:01:19,860 ‫However, the second transaction, its view counterview as it's reading a B as in a query and find that 20 00:01:19,860 --> 00:01:22,480 ‫these are the two BS that's going to change into it. 21 00:01:22,680 --> 00:01:26,340 ‫So technically, this transaction changed only these two rows. 22 00:01:26,580 --> 00:01:28,410 ‫This transaction change only these terms. 23 00:01:29,040 --> 00:01:31,750 ‫So they did not step on each other's toe. 24 00:01:32,040 --> 00:01:36,200 ‫So there is technically no concurrency issue, if you thought about it. 25 00:01:36,720 --> 00:01:42,990 ‫So an unrepeatable read will not catch anything, says, hey, you guys didn't step on each other's 26 00:01:42,990 --> 00:01:47,520 ‫tails like nobody did anything to the same value on different transactions. 27 00:01:47,550 --> 00:01:52,560 ‫So that's fine when it comes to read committed in order to Hibito, but that leads to a result that 28 00:01:52,560 --> 00:01:56,310 ‫is as like this Vehbi and it's like almost flipped. 29 00:01:56,610 --> 00:02:04,630 ‫Maybe this is what exactly you want, but sometimes this is not what you want as a user in this current 30 00:02:04,680 --> 00:02:05,310 ‫domain. 31 00:02:05,370 --> 00:02:05,670 ‫Right. 32 00:02:06,060 --> 00:02:07,680 ‫This is what you want. 33 00:02:07,690 --> 00:02:10,130 ‫You want the transactions to be actually serialized? 34 00:02:10,140 --> 00:02:12,720 ‫No, I want no, no. 35 00:02:12,740 --> 00:02:20,160 ‫If there is dependency when it comes to reading and writing, and that can change the result, I need 36 00:02:20,160 --> 00:02:27,630 ‫you to be serialized as if, like each transaction are physically executed after after each other, 37 00:02:27,750 --> 00:02:29,130 ‫almost physically execute. 38 00:02:29,490 --> 00:02:31,200 ‫The databases don't implemented this way. 39 00:02:31,200 --> 00:02:34,020 ‫But this is how serializable look like, at least. 40 00:02:34,710 --> 00:02:41,610 ‫So in this case, when we start with T1, we change all the A's to B's and then, OK, we only touch 41 00:02:41,610 --> 00:02:42,480 ‫these BS. 42 00:02:42,480 --> 00:02:42,790 ‫Right? 43 00:02:42,810 --> 00:02:46,110 ‫So that will obviously transaction number one only touch. 44 00:02:46,260 --> 00:02:53,670 ‫So but when I went to start, it has a different view of the database if I have a different state. 45 00:02:54,060 --> 00:02:58,530 ‫So it looks at as oh, all of these guys are BS, so I'm going to read all of them. 46 00:02:58,920 --> 00:02:59,230 ‫Right. 47 00:02:59,260 --> 00:03:03,780 ‫So it started with a different version, if you will, and now all of them will be A's. 48 00:03:03,930 --> 00:03:08,970 ‫And based on where you start, if it's transaction to start, you're going to end up with B's. 49 00:03:09,450 --> 00:03:10,410 ‫So that's what I'm going to explain. 50 00:03:10,420 --> 00:03:14,910 ‫Let's jump into the actual PostgreSQL terminal and explain the stuff. 51 00:03:15,090 --> 00:03:15,900 ‫So are you guys. 52 00:03:15,900 --> 00:03:18,450 ‫I have my beautiful table that is called test. 53 00:03:19,020 --> 00:03:21,480 ‫And this is that maybe that's what I have today. 54 00:03:21,480 --> 00:03:21,770 ‫Right? 55 00:03:22,020 --> 00:03:27,660 ‫So what I want to do here, I'm going to begin a transaction that is, let's say, a non repeatable 56 00:03:27,660 --> 00:03:28,500 ‫read a transaction. 57 00:03:28,500 --> 00:03:38,850 ‫So I show you the problem first and let's begin transaction isolation level, non repeatable read. 58 00:03:40,250 --> 00:03:43,380 ‫Don't confuse those two and then do the same thing here again. 59 00:03:43,380 --> 00:03:47,280 ‫Transaction isolation level repeatable read. 60 00:03:47,310 --> 00:03:48,360 ‫Both of them are repeatable. 61 00:03:48,840 --> 00:03:57,930 ‫So those guys, this guy says, OK, update test set ti the field equal A where T equal B. 62 00:04:00,430 --> 00:04:06,670 ‫And then we're going to copy the exact same statements so we don't have to type it all the time and 63 00:04:06,670 --> 00:04:10,780 ‫then go to the other one and then change these two eights here. 64 00:04:12,450 --> 00:04:19,020 ‫If I do that, obviously, people will not unrepeatable, worried, so if I do select staff from tests, 65 00:04:19,710 --> 00:04:20,850 ‫I get all A's. 66 00:04:21,180 --> 00:04:25,980 ‫If I do a select staff from test, I get all these because I'm isolated. 67 00:04:26,280 --> 00:04:30,960 ‫But now let's say I commit here and then I commit here. 68 00:04:31,980 --> 00:04:34,230 ‫And then I do select start from a test. 69 00:04:34,770 --> 00:04:36,270 ‫Look where you get selected. 70 00:04:36,540 --> 00:04:42,930 ‫Abebe Flagstar from test we get the same state essentially because of the rules. 71 00:04:42,930 --> 00:04:44,670 ‫That change didn't overlap. 72 00:04:44,880 --> 00:04:48,240 ‫But this is not what we want as we should. 73 00:04:48,420 --> 00:04:53,880 ‫I want these puppies to be serialized so I can get all A's or Elbaz. 74 00:04:53,880 --> 00:04:55,530 ‫That's what that's what I want. 75 00:04:56,100 --> 00:05:02,910 ‫So let's start a transaction that is called isolation level. 76 00:05:02,910 --> 00:05:09,960 ‫Serializable, serializable, serializable. 77 00:05:10,250 --> 00:05:11,990 ‫Can't spell this thing then. 78 00:05:12,800 --> 00:05:13,280 ‫All right. 79 00:05:14,070 --> 00:05:18,960 ‫Begin transaction is the solution level. 80 00:05:19,380 --> 00:05:22,050 ‫Syria lies. 81 00:05:23,250 --> 00:05:23,730 ‫All right. 82 00:05:23,760 --> 00:05:25,860 ‫So now of serializable, right. 83 00:05:26,010 --> 00:05:29,330 ‫So now if I query my table, ABEBE Right. 84 00:05:29,640 --> 00:05:31,130 ‫So it's almost the same state. 85 00:05:31,530 --> 00:05:34,440 ‫So now I'm going to do a group of four is still there. 86 00:05:34,440 --> 00:05:35,790 ‫The query nice. 87 00:05:36,010 --> 00:05:38,640 ‫Going to change all my A's to B's boom. 88 00:05:38,940 --> 00:05:43,170 ‫And now I'm going to change all my B's to A's here and. 89 00:05:44,490 --> 00:05:46,080 ‫Now I'm going to commit. 90 00:05:46,380 --> 00:05:51,270 ‫Look what would happen committing here says, OK, that's fine, I commit you guys. 91 00:05:53,610 --> 00:05:58,800 ‫You could Albie's, but now the other party will want to come in and say, this is my I want to change, 92 00:05:58,800 --> 00:06:03,240 ‫but look at that, Puskás says, Nope, babe, nope. 93 00:06:03,900 --> 00:06:12,240 ‫Could not serialize access due to redraw dependencies among Transaction's reason code cancelled on ID 94 00:06:12,240 --> 00:06:15,750 ‫as a pivot during commit the transaction. 95 00:06:15,750 --> 00:06:16,970 ‫My taxes are free, right? 96 00:06:17,430 --> 00:06:18,450 ‫That is the case. 97 00:06:18,450 --> 00:06:25,740 ‫When you do uninterruptible and realisable, you have to be prepared as an engineer, a database engineer 98 00:06:25,740 --> 00:06:29,040 ‫or developer to retry the transaction in case of these failures. 99 00:06:29,250 --> 00:06:37,680 ‫Because what did do is doing more work is actually looking for dependencies and it's doing some magic 100 00:06:37,680 --> 00:06:38,070 ‫here. 101 00:06:38,100 --> 00:06:45,210 ‫That's like, OK, at TI you writing something that this guy is reading and you're writing something 102 00:06:45,210 --> 00:06:46,170 ‫that this guy is reading. 103 00:06:46,200 --> 00:06:48,840 ‫I cannot let you commit this stuff. 104 00:06:49,080 --> 00:06:49,590 ‫There is. 105 00:06:49,590 --> 00:06:55,350 ‫And since you are on a serializable isolation level to guarantee that serializable ability. 106 00:06:55,350 --> 00:06:59,130 ‫Sorry I cannot let you come in Faile do it again. 107 00:06:59,130 --> 00:07:06,390 ‫So this transaction now has been rolled back and that is as a result I get this state in this case, 108 00:07:06,390 --> 00:07:08,250 ‫which is almost what we want to do. 109 00:07:08,400 --> 00:07:11,850 ‫So now if I executed this again. 110 00:07:12,110 --> 00:07:12,290 ‫Right. 111 00:07:12,450 --> 00:07:18,660 ‫Let's go ahead and do begin transaction boom and I do boom, they do commit. 112 00:07:19,050 --> 00:07:23,760 ‫It's not going to yell at me because no other transaction are in in-transit. 113 00:07:23,760 --> 00:07:29,760 ‫Let's say I do that we get all A's and that's exactly how serializable works. 114 00:07:30,120 --> 00:07:36,080 ‫If that if you want to do is really serialize ability prepare to get failures. 115 00:07:36,090 --> 00:07:36,330 ‫Right. 116 00:07:36,480 --> 00:07:40,020 ‫There are other ways to achieve this by doing pessimistically. 117 00:07:40,230 --> 00:07:45,030 ‫Alptekin Laucke, I say hey hey this, this, this puppy's mine. 118 00:07:45,030 --> 00:07:49,950 ‫Now I'm going to paint for, I don't know, selectwoman update whatever I say. 119 00:07:50,340 --> 00:07:51,300 ‫Don't touch this stuff. 120 00:07:51,900 --> 00:07:52,140 ‫Right. 121 00:07:52,140 --> 00:07:59,730 ‫So all other times actually just waits but you have to be in a committed isolation level for that to 122 00:07:59,730 --> 00:08:00,210 ‫work. 123 00:08:00,630 --> 00:08:01,020 ‫Right. 124 00:08:01,590 --> 00:08:03,870 ‫So it's just a little bit complicated. 125 00:08:03,870 --> 00:08:06,620 ‫But I just wanted to explain this to Syria. 126 00:08:06,670 --> 00:08:09,690 ‫Isolation levels, guys that let me know in the comments section. 127 00:08:09,690 --> 00:08:10,260 ‫What you. 128 00:08:10,290 --> 00:08:13,930 ‫All right, guys, I just wanted to explain these to isolation levels. 129 00:08:14,250 --> 00:08:15,150 ‫You enjoy this. 130 00:08:15,150 --> 00:08:16,160 ‫I'm going to see in the next one. 131 00:08:16,180 --> 00:08:17,640 ‫You guys stay out on bail.