‫Hey, guys, in this video, we're going to describe the difference between a serializable isolation
‫level and unrepeatable isolation level.
‫I've searched the Web and they're very, very, very low resources talking about this.
‫So I wanted to make a video to talk about the difference between them.
‫And then why would you pick serializable isolation level over?
‫No repeatable.
‫Let's jump into it, guys.
‫So here's an example where you have a Reid committed or a repeatable isolation level.
‫OK, so let's say we have a table, we have A here values and B here.
‫I assume this is a table field and these are just for values, for rows with these kind of values.
‫Transaction number one changes A's to B's.
‫Transaction number two, the green one changes B's to A's.
‫So it's literally update table set field equal, B where field equal.
‫And this the other one is the reverse.
‫So what happens if they execute concurrently so that both of them start at the same time?
‫And then this transaction starts, obviously changes all the A's to B's.
‫And so it's only going to these two rows.
‫So it's going to become DB.
‫However, the second transaction, its view counterview as it's reading a B as in a query and find that
‫these are the two BS that's going to change into it.
‫So technically, this transaction changed only these two rows.
‫This transaction change only these terms.
‫So they did not step on each other's toe.
‫So there is technically no concurrency issue, if you thought about it.
‫So an unrepeatable read will not catch anything, says, hey, you guys didn't step on each other's
‫tails like nobody did anything to the same value on different transactions.
‫So that's fine when it comes to read committed in order to Hibito, but that leads to a result that
‫is as like this Vehbi and it's like almost flipped.
‫Maybe this is what exactly you want, but sometimes this is not what you want as a user in this current
‫domain.
‫Right.
‫This is what you want.
‫You want the transactions to be actually serialized?
‫No, I want no, no.
‫If there is dependency when it comes to reading and writing, and that can change the result, I need
‫you to be serialized as if, like each transaction are physically executed after after each other,
‫almost physically execute.
‫The databases don't implemented this way.
‫But this is how serializable look like, at least.
‫So in this case, when we start with T1, we change all the A's to B's and then, OK, we only touch
‫these BS.
‫Right?
‫So that will obviously transaction number one only touch.
‫So but when I went to start, it has a different view of the database if I have a different state.
‫So it looks at as oh, all of these guys are BS, so I'm going to read all of them.
‫Right.
‫So it started with a different version, if you will, and now all of them will be A's.
‫And based on where you start, if it's transaction to start, you're going to end up with B's.
‫So that's what I'm going to explain.
‫Let's jump into the actual PostgreSQL terminal and explain the stuff.
‫So are you guys.
‫I have my beautiful table that is called test.
‫And this is that maybe that's what I have today.
‫Right?
‫So what I want to do here, I'm going to begin a transaction that is, let's say, a non repeatable
‫read a transaction.
‫So I show you the problem first and let's begin transaction isolation level, non repeatable read.
‫Don't confuse those two and then do the same thing here again.
‫Transaction isolation level repeatable read.
‫Both of them are repeatable.
‫So those guys, this guy says, OK, update test set ti the field equal A where T equal B.
‫And then we're going to copy the exact same statements so we don't have to type it all the time and
‫then go to the other one and then change these two eights here.
‫If I do that, obviously, people will not unrepeatable, worried, so if I do select staff from tests,
‫I get all A's.
‫If I do a select staff from test, I get all these because I'm isolated.
‫But now let's say I commit here and then I commit here.
‫And then I do select start from a test.
‫Look where you get selected.
‫Abebe Flagstar from test we get the same state essentially because of the rules.
‫That change didn't overlap.
‫But this is not what we want as we should.
‫I want these puppies to be serialized so I can get all A's or Elbaz.
‫That's what that's what I want.
‫So let's start a transaction that is called isolation level.
‫Serializable, serializable, serializable.
‫Can't spell this thing then.
‫All right.
‫Begin transaction is the solution level.
‫Syria lies.
‫All right.
‫So now of serializable, right.
‫So now if I query my table, ABEBE Right.
‫So it's almost the same state.
‫So now I'm going to do a group of four is still there.
‫The query nice.
‫Going to change all my A's to B's boom.
‫And now I'm going to change all my B's to A's here and.
‫Now I'm going to commit.
‫Look what would happen committing here says, OK, that's fine, I commit you guys.
‫You could Albie's, but now the other party will want to come in and say, this is my I want to change,
‫but look at that, Puskás says, Nope, babe, nope.
‫Could not serialize access due to redraw dependencies among Transaction's reason code cancelled on ID
‫as a pivot during commit the transaction.
‫My taxes are free, right?
‫That is the case.
‫When you do uninterruptible and realisable, you have to be prepared as an engineer, a database engineer
‫or developer to retry the transaction in case of these failures.
‫Because what did do is doing more work is actually looking for dependencies and it's doing some magic
‫here.
‫That's like, OK, at TI you writing something that this guy is reading and you're writing something
‫that this guy is reading.
‫I cannot let you commit this stuff.
‫There is.
‫And since you are on a serializable isolation level to guarantee that serializable ability.
‫Sorry I cannot let you come in Faile do it again.
‫So this transaction now has been rolled back and that is as a result I get this state in this case,
‫which is almost what we want to do.
‫So now if I executed this again.
‫Right.
‫Let's go ahead and do begin transaction boom and I do boom, they do commit.
‫It's not going to yell at me because no other transaction are in in-transit.
‫Let's say I do that we get all A's and that's exactly how serializable works.
‫If that if you want to do is really serialize ability prepare to get failures.
‫Right.
‫There are other ways to achieve this by doing pessimistically.
‫Alptekin Laucke, I say hey hey this, this, this puppy's mine.
‫Now I'm going to paint for, I don't know, selectwoman update whatever I say.
‫Don't touch this stuff.
‫Right.
‫So all other times actually just waits but you have to be in a committed isolation level for that to
‫work.
‫Right.
‫So it's just a little bit complicated.
‫But I just wanted to explain this to Syria.
‫Isolation levels, guys that let me know in the comments section.
‫What you.
‫All right, guys, I just wanted to explain these to isolation levels.
‫You enjoy this.
‫I'm going to see in the next one.
‫You guys stay out on bail.