‫Welcome to another Q&A lecture.
‫Today we have a question from Peter.
‫Peter asked.
‫Isolation, repeatable read versus snapshot.
‫So the question is right.
‫So Peter is basically trying to understand and give us an example between repeatable rate versus snapshot
‫isolation.
‫Again, isolation levels.
‫Or think of it as just a single instance.
‫We're not talking about distribute or anything because I got that question as well.
‫It has nothing to do with distributed.
‫It's just a single instant.
‫That's where you're isolating the transactions.
‫So we have here's an example he gave he pulled it from the Internet execution order.
‫So we have select pet ID, pet name score from pet shots, a table called picture where score equal
‫select max score from picture.
‫So he's effectively he's getting it the pet ID who got the most score, whatever that means.
‫Right.
‫That's what it was trying to do.
‫So there is an insight in equity that is inside another query effectively, and Max will return almost
‫one value.
‫So we want to find out which one Is that.
‫The way I would I would have written the query differently, Right?
‫I would I would just do an order by it's like cheaper in that particular case because these are two
‫queries versus I'm going to do select pet name score or thereby score descending and then limit one.
‫I'm going to fetch the first one.
‫That's that's basically how how I would do it.
‫I don't know which one is faster to be honest.
‫Right.
‫This will this is fast because, Max, we know exactly where the score is, assuming the score is indexed.
‫And I know we're talking about data modeling here and performance, but I thought it was going to be
‫useful.
‫The score in this particular case, that if it's an index, this will be immediate because I know the
‫last page in the index.
‫Go and fetch that and that will give me the last score.
‫So that's fast.
‫And then this is ab3 index scan to find that value, which we just found in order to find out the beat
‫ID, the bid name.
‫Right.
‫And the score of that score.
‫Right.
‫So in this particular thing, we're going to find a score and then go jump back to the heap, get the
‫pet ID and other columns.
‫Right.
‫So two queries versus I'm just going to do a select one query or thereby score.
‫And in this particular sense, since the order bias index, I'm going to go to the I'm just going to
‫walk the index.
‫In a reverse order.
‫No, it's actually faster to do an order by.
‫Yeah, because I'm going to go to the last one and find the last entry and immediately jump to that.
‫Right.
‫So it's, it's, it's one query versus two.
‫That makes sense.
‫Right.
‫Yeah, definitely.
‫It's faster to do a select, but it doesn't matter, right?
‫It's not.
‫It's not that question.
‫So the second so I have one transaction that does this select a query like max score of someone and
‫there is a query one where we have updates the score and, and when you have the score, obviously this
‫might change, right?
‫And that's what he's asking is like if I am in my transaction and I query this twice and meanwhile I,
‫if I were a first query, gotta get a better ID, then someone updates the score and commit and then
‫I query it again.
‫And what is the behavior in repeatable rate versus isolate snapshot?
‫Right.
‫So if you do a repeatable read, if your tw x one is a repeatable read, what will happen is you can
‫execute the query.
‫Someone will commit something, change the score, so the ID will change and you're going to execute
‫it again, right?
‫If your query is repeatable, if the transaction one is actually isolation is is repeatable.
‫Read.
‫Then.
‫What will happen, unfortunately, is you're going to get different result.
‫You might say what I thought repeatable will give you a repeatable rate.
‫Well, repeatable rate will give you a repeatable rate for things that you actually physically read
‫a row that you read.
‫Right.
‫If you read a row and that row has changed, reading that same row will give you the same value.
‫But that's not the case here.
‫What changed here is completely something completely different.
‫No rows were changed, rows were added.
‫Right.
‫And when rows are added, the nature of the query is now because of the rate repeatable.
‫Read is the read committed.
‫It's going to see the new stuff.
‫This is a this is a side effect of something called Phantom Reader, which we talked about, right?
‫Because we're inserting thing.
‫My query is now detecting those new things.
‫All of a sudden Phantom reads cannot be solved by repeatable read isolation level.
‫Even that statement is not true anymore because databases break the rules all the time.
‫And it's not that the rules are great, but they break it for a reason, for performance reasons.
‫Right.
‫And correctness.
‫So in this particular thing, a repeatable read will give you different results, right?
‫Because you are technically.
‫Uh, not reading the same, bro.
‫You're dating a different RO because now you select score, Max score.
‫We'll give you a different result because there is a new score.
‫Right now we just added a new score so we can get that score.
‫And as a result, you're going to get the P tied for the new score.
‫So repeatable rate will not solve it.
‫A snapshot will definitely solve it.
‫Snapchat will give you a version say, Hey, I don't care.
‫I started this transaction.
‫My current version or timestamp is PT seven.
‫Please read anything PT seven or older.
‫Never read anything t seven or never read T eight or ten or t nine.
‫I don't care.
‫So if those things are committed in PT ten, I'll never see them because I filtered my snapshot.
‫Isolation is filtering based on a version based on a timestamp.
‫That's very powerful, right?
‫And that's how basically politics works.
‫Postgres doesn't have a concept of snapshot isolation because a repeatable read is a snapshot isolation.
‫Yeah, I just, I just contradicted myself.
‫The way postcodes implemented Repeatable reader is a snap as is as a snapshot isolation level because
‫postcards only have versioning.
‫Because I would talk about the idea of version, right?
‫Every every time you touch a row and new version is created for that row.
‫So it's by default souls, the Phantom reads.
‫As a result, Peter's problem is not a thing here and applescript and I'm not sure in my sequel I might
‫be it might be the same can be quickly verified.
‫Just spin up on my SQL instance on can on a docker container.
‫Try it up.
‫It should do a max in another transaction, update the score and in the same transaction, obviously
‫begin the transaction and then do a max and then update the score and then do our max again.
‫You can if you got the same thing and the repeatable read.
‫That means repeatable.
‫It is implemented as a snapshot isolation level.
‫I hope you enjoy this lecture on the next one.
‫You guys have some.
‫Good bye.