‫What is going on is my name is Hussein, and today I'm going to talk about a very interesting read phenomenon
‫database's called Phantom Rietz and this is something a lot of people overlook and it can, if misused,
‫lead to a disaster.
‫So let's just explain, what is that?
‫Phantom Reed is one of the three phenomenons, which is dirty reads and unrepeatable reeds.
‫And the Phantom Reed happens in concurrence kind of transaction.
‫So when another transaction does something and you are in another completely concurrent transaction
‫running at the same time and you're seeing unpleasant results, let's put it this way.
‫So Phantom Reed explicitly is when another transaction inserts a new roll and all of a sudden when you
‫do a query in your own isolated transaction, you start seeing that role despite you should really see
‫that roll.
‫Like, let's give an example.
‫So this is all clear.
‫So I'm here and this is the transaction number one.
‫This transaction number two, there is no there are no any transactions.
‫That is just terminals.
‫But if I do a select store from sales, I have a sales table here.
‫You can see that I have five records here.
‫Right.
‫This is the dates and all that stuff.
‫Right.
‫So now if I do a begin transaction here, I started a transaction and I do a select store from sales.
‫You can see that I see these five sales report and I'm about let's say I started the transaction due
‫to produce a report about the all the sales that I made between these dates and let's say I want to
‫do the some.
‫Rila says do OK, select videos, some of the price from sales, and let's do a group.
‫I have to do a group by P e and all of a sudden you have product number one sold forty dollars.
‫Right.
‫So now if I go I am still in a transaction, go to a completely different transaction and I'm going
‫to insert a new sale.
‫Someone just made a sale and it happened to be between these dates.
‫Right.
‫Say bid price dates, values enter let's just say product number one.
‫It made up fifteen dollars and it was made on February seven, twenty twenty one.
‫And now I was in a transaction.
‫So this automatically started transaction committed.
‫Did that statement and then committed immediately.
‫Now if I go back to my isolated transactions or so they say.
‫Right.
‫And I do a query, all of a sudden the sum has changed.
‫Remember, you're producing a report and a report has to be consistent based on the start of the transaction.
‫So if people starting making sales while you're producing a report, you're going to get funky results
‫like let's say you produce, you're listing all the products.
‫And then you were doing a query to do the some.
‫You're going to get an inconsistent result.
‫Of course you're going to get.
‫Now, if I do select start from sales and even if I do a range query, let's say between this and this,
‫you're going to get this extra roll that had been inserted.
‫This most of the time is undesirable.
‫So how do we fix this?
‫Those guys provide us with ways to fix this and most databases actually do it.
‫And that is called isolation, which I talked about in my introduction to database engineering course.
‫So now the serialisation isolation level allows us allows the databases to serialize transactions so
‫that nobody if a transaction has dependency like this one, this has a dependency on this insert, it
‫will affect it.
‫If if Bosco's detect or MySQL or any databases that supports serialisation, detects that there is a
‫dependency, it will try to isolate those changes.
‫So let's go ahead and be in a let's do the same thing, but in a not isolated and let's do a roll back
‫first and then do the serialization.
‫So if I do begin transaction isolation level serializable and now I am in a transaction that is serializable,
‫that means anything that I read must not depend on other transactions that is currently running.
‫Otherwise things can go bad.
‫So if I do select Star from sales, you can do it.
‫You can see that I have six records.
‫Let's go ahead and insert something in a completely different row.
‫Here are the same thing.
‫Sure.
‫Now if I go back and a query, you see that I've been isolated from that post, detected that, hey,
‫something happen on the other end.
‫But don't worry, we have isolated you from that change.
‫Okay, so now you can safely commit your change and you're good, right?
‫You can produce a report.
‫You can you can do the same and you will be isolated.
‫You will not see that change that happened on the on the other transactions.
‫So finally.
‫This is all true in all databases except postscripts, actually, postcrisis is special when it comes
‫to this.
‫Postscripts prevents phantom reads even in other isolation levels, such as repeatable read isolation
‫level.
‫So there are beatable readers allow you to query, execute the same query, yet get the same result
‫a little bit different than phantom rate that the reader all of a sudden you get a new raw read as just
‫reading a value.
‫And that value does not change no matter how much how many times you cooperate in the same query, in
‫the same transaction.
‫So now I begin my beautiful transaction here, which is not repeatable.
‫There are seven rows now.
‫Why?
‫Because now I started a new transaction.
‫I picked up the latest changes.
‫It's almost like it's it is implemented as versioning because NVCA multi version concurrency control.
‫So when you start a transaction, you create a version that's a version of these rows.
‫And then as you start editing other people, committing the database increases its version.
‫So now even a repeatable read, if you do this thing, that phantom thing, you don't see that phantom
‫read that even in a range queries whether this is an unbounded query like what I'm doing right now or
‫if it's like you're doing between, hey, give me the sales report between this date and this date that's
‫also can be affected as a phantom read.
‫But if you are in a repeatable read isolation level, you do not see that even in because that is very
‫special only to Bosco's MySQL.
‫If you do a repeatable read, you don't get rid of Phantom Rate.
‫That's very, very critical.
‫The only way to get rid of Phantom Reads and Oracle, MySQL, SQL Server, as far as I know, you have
‫to do serializable isolation level or you do some sort of a pessimistic locking was like lock until
‫someone if someone is changing the stiv, don't allow me to read it.
‫That's another way.
‫All right, guys, very quickly to talk about Phantom Reads.
‫I'm going to see you in the next one.
‫You guys stay the by.