‫All right, let's talk about the third property in the acid system isolation.
‫Very, very critical and really underrated property.
‫Not all of people understand, truly understand the ramifications of transaction isolation.
‫So in this lecture, we're going to talk about isolation.
‫And this is going to be a long lecture.
‫So sit back, relax, grab a cup of coffee, tea or your favorite drink, and let's go through it.
‫So to talk about isolation, we have to answer this question.
‫We have transactions, correct?
‫And if it's a single user, that's fine.
‫But we have many TCP connections to my database if you support remote and remote connections.
‫And then each connection execute transactions.
‫So there is a chance where concurrency can happen, multiple transaction fighting over to write and
‫to read the same pieces of data.
‫And that's what isolation come into place.
‫The question is, can my inflight transaction see changes made by other transactions that are also in
‫flight?
‫That's the question that needs answering, not only in flight, other in-flight induction, even transaction
‫that has finished.
‫What if I am running a transaction and I'm quitting reading, reading, reading, reading and some other
‫transaction committed to change?
‫The question is, should I see this change?
‫Well, it depends.
‫Do you want to see that change?
‫Right?
‫There is no answer.
‫There is no right or wrong.
‫So as a result of these, you get a bunch of read phenomena as they call it.
‫And I had very fancy name.
‫And the mean understanding of these read phenomena is these are usually undesirable.
‫You don't want these.
‫These are bad side effects, but we're going to go through them.
‫We're going to understand what are they?
‫And if you run into one of them, you're going to see and you understand, Oh, now I see what happening
‫here.
‫And these are one of the nastiest things to debunk, in my opinion.
‫We're also going to talk about isolation levels that were introduced to solve these undesirable read
‫phenomenons.
‫Right?
‫So let's talk about isolation read phenomenon.
‫So we they decided to run and to the first 3D phenomenon called Dirty Read, and we're going to talk
‫about them in details and with an example.
‫But it's essentially a dirty read is I read that you, as an in-flight transaction can only running.
‫You read something that some other transaction has wrote but didn't really commit yet.
‫So there's a chance that this change that you just read could be rolled back or it could be committed,
‫or it could be that Airbus could be crashed.
‫Right?
‫So this is called a dirty read.
‫Anything that is dirty.
‫It means that you wrote something and this right is not really fully flushed, not fully committed.
‫It might change.
‫And that's what it's called dirty.
‫It's not repeatable.
‫Reads are reads that you are in a transaction.
‫You read a value, right?
‫And then in the same transaction, you read it again.
‫And that sounds ridiculous.
‫Like what?
‫Why would you ever read things twice?
‫That's just dumb and an optimal, but no, believe me, you do this all the time without you realizing
‫is not about doing select select idea from account and then doing a select idea from the count.
‫Not the same exact query.
‫No, we're not talking about that.
‫That's yeah, it's a little bit odd to do that.
‫But there are cases where you actually do the same exact way.
‫But I'm talking about the different queries that yield the same value, and there are thousands of these
‫cases.
‫One example is you get a reader value, but then you do a select sum, which also reads the value behind
‫the scenes because it does a sum.
‫These are different values, but they are dependent on the same entry that is in the database.
‫So an unrepeatable read as when you read something and then you the database tries to read the same
‫value again, that changes.
‫Right.
‫And this is called a non repeatable it.
‫It was not repeatable in my own transaction.
‫So that's what a read phenomena.
‫Some people find this undesirable.
‫Right?
‫And we're going to show you show you an example of that phantom red phantom.
‫And these are things that you cannot really read because they don't exist yet.
‫I'm going to give you an example.
‫So example with this is our range queries like, let's say, a selecta, all the activities between
‫this date and this date.
‫Right.
‫When you do this range between this and this right, you get a number right, you get a certain values.
‫But then let's say someone inserted a new Rosiere.
‫It's a completely new rule, so it's not really and not repeatable.
‫Read you didn't even read it.
‫It's just something that is inserted that satisfies your range query now.
‫Now, if you do that and you do another select read, you're going to give that phantom new role and
‫you're going to give that read and that things might go really wrong with that.
‫And that's called The Phantom Read.
‫So you got you got certain result in the first situation, but the second one you you got you got a
‫completely different result.
‫So it's not really completely not repeatable.
‫Read it's it doesn't qualify as an unrepeatable because you didn't really read it in the first place
‫and you didn't.
‫You can't quite catch it.
‫I'm doing this with my head, but he can't see it.
‫I'm doing this with my head.
‫The fourth one is really lost updates.
‫That means I wrote something.
‫But before I commit this right, I try to read what I wrote.
‫Well, this is very common because if if you aren't trans. and you write something and you turn on only,
‫that's something that's completely void because you are in your own bubble.
‫You can read what you wrote, but other times, actually, you really can't.
‫Depends on whether dirty readers are enabled or not.
‫But.
‫Some other times action can actually change what you wrote.
‫So if you turn around and read what you just wrote is just lost.
‫And that's called lost updates.
‫We're going to talk about when that actually can happen and how to circumvent this.
‫All right.
‫Dirty reads less than an example.
‫So we have a sales table here and we have three columns product in quantity and the price.
‫Product one has sold 10 quantities and each is $5.
‫So the total price is, what, five hundred fifty dollars, $50.
‫So we sold fifty dollars worth of product, one product to us twenty point thirty four dollars.
‫So the total is, what, $80.
‫So what is a dirty read in this case?
‫You began a transaction as usual and you start this query.
‫You select the product ID.
‫And then the second column is, is is that actually the expression you multiplying the quantity times
‫the price goes, you want to see the total right from sales.
‫That's generally a bad query, not because we're doing an explosion, but because we don't have a work
‫load.
‫But we're explaining things here.
‫So as long as they tell you that this is bad because if you have billions of sales now this is going
‫through the whole entire table, you have to have always a range.
‫You have to have a bounded query.
‫I always say it's bounded.
‫And the reason is this is an unbounded equation that means there is no bounds, which is just forever
‫goes forever, right?
‫Yeah, we have two rows there.
‫But when things work with €2, it doesn't necessarily work with over 100000 rows.
‫But yeah, we got we got the idea, right?
‫So we have, uh, what we are going to get is a product.
‫One is 50 and the product is 80.
‫Right?
‫That's all we got.
‫And this is the list.
‫I don't know.
‫Maybe I'm generating a report, a sales report and now another transaction started.
‫While this is actually running, so these are in parallel now, right?
‫So this story and this is now so we selected this.
‫We read the actual values that aren't currently in the table transactions to start.
‫And right after this guy read the values, it actually made some more sale.
‫Some people more made some more seasoned product one.
‫We added an extra five.
‫We sold in excess of five products, right?
‫So now what will happen is now it's 15.
‫OK, now look at this.
‫We're doing another query.
‫But now we're just want to sum all that all the sales, regardless of the product.
‫What do you think the value should be versus what do you think the value will be based on a dirty read
‫configuration look or will happen?
‫We're going to get 1:55 why?
‫Because if you multiply this 15 by five plus eight years ago, this didn't change.
‫So the original sum is actually 130, if you think about it, 50 plus 80.
‫But we added a $25 worth of sales.
‫So if you add that you're going to get 155, which is technically no wrong.
‫But if within the same transaction, this is inconsistent because if you actually print this in a report,
‫then you went to the manager said, OK, oh, product 150 product to 80, and then you print it all
‫the way down.
‫In the summary, you put in the sum as 155, there's what.
‫What are you doing?
‫That's just wrong.
‫Right?
‫And why did we do that?
‫Because we read a dirty read.
‫This guy didn't even commit.
‫And yet we read the value.
‫You notice that the line of the commit didn't even happen.
‫Now we come into my transaction, which we lived in, didn't change anything.
‫But we're just reading just to tell you that the transaction is terminated.
‫But now this guy just, hey, you know what?
‫Never mind.
‫I'm going to roll back because, hey, we did.
‫And I actually made five five sales rollback, so I know this is actually worse.
‫You write a dirty read and that Druid has has gone right.
‫And that is really a problem.
‫You get an inconsistent result.
‫So let's talk about none.
‫Repeatable.
‫Read here.
‫--.
‫Configuration here.
‫10:50 10.
‫Five twenty four.
‫And then we're going to begin a transaction.
‫Now we're going to select the same exact thing.
‫We're going to get product 150 product to 80.
‫So what is a non repeatable rate here?
‫Another transaction begins, but look at that.
‫The arrow is shorter.
‫I'm a little shorter.
‫Why?
‫Because here's what I'm going to do this time.
‫I'm going to make the sale and guess what?
‫I'm going to actually commit.
‫The change before the second transaction.
‫Finish it or before the second transaction actually made the second query to do do the.
‫Look at this.
‫So we we actually persisted.
‫This is not a dirty read.
‫This is an actual valid right and valid read.
‫So the value is actually 15, but we read the value B prior to that change.
‫So now what should we do now?
‫I do a some count price from sales and guess what we're going to do?
‫We're going to get the exact same wrong result.
‫We'll get 1:55 when it should have been 130.
‫Right.
‫That's the actual result.
‫That is desirable.
‫So no, this is not really a dirty read.
‫This is an actual, committed read.
‫But the problem is this was a non repeatable read because you read the values here and you read them
‫kind again, right?
‫But you read them through an aggregation function.
‫Right?
‫And as a result, you got an inconsistent rally and you might say I was in this case, I don't want
‫none of it body, not all the time because we're going to come to that.
‫But fixing this is expensive.
‫Fixing this so that you don't, you're all is the same version of the value here is not it is not easy.
‫A lot of data is supported through NBCC, but is not easy because now you have to keep a version of
‫the product one at the original state.
‫That's what post-GST does, by the way.
‫Topolsky is always any updates create a new version of the road and never changes the same value.
‫While MySchool and Oracle, and maybe also a SQL server, changes this.
‫The final value changes 250mm, but it keeps another table called the undo grade.
‫I'm writing and do here.
‫I don't know if you can't see it, but the undo stack keeps an actual writing to desk a place where
‫all the previous values O product one, by the way, it used to be 10 at turns to be 15, right?
‫We, I don't know.
‫It keeps the delta changes.
‫So now if this guy want to read in Postgres, it will read the original version.
‫It just just keeps a version pointer here.
‫So there is another roll product, one version to write version B, for example, so you don't get confused.
‫But in my sequel, what will happen is it doesn't.
‫It cannot read that it has to go and crack open the undo log and then read that values.
‫And that could be expensive for long running transactions, right?
‫Because it cannot trust this latest value anymore.
‫So, yeah, this was just a short explanation of isolated, but we're going to come to that yet.
‫And then we come out and obviously nothing changes bad.
‫Not repeatable.
‫Read can be bad.
‫So it's really up to you guys.
‫You cannot just go by this example and say, Oh, I'm fine.
‫No, really comes back to what are you trying to build and then just understand this problem and say,
‫Hey, I'm fine.
‫I'm even fine with dirty rates because I don't think anybody is fine with their theory.
‫Dirtiness is the wars, but I have actually built an application.
‫What I relied on dirty read, which was a very bad idea.
‫I regretted that later.
‫Let's talk about Phantom Reed.
‫So Phantom reads result from range queries.
‫Anything that can potentially give you one or more results.
‫Pretty much phantom eight always happen.
‫Do you think about it if you have a where clause?
‫I can't think of an example of Phantom.
‫It could not happen, to be honest.
‫So exemplification sales would begin transaction.
‫We do a select quantity price sales, and this time I'm reading this product 50 and 80 transaction to
‫start it now.
‫It didn't really update it inserted a new and new product, completely new product, right?
‫So now if you insert a new product, well, that's fine, because in this case, we don't really read
‫this product.
‫But get this, it actually commits it.
‫So now we have $10 to No.
‫10 quantity of product, three at a price of one and an extra $10.
‫So now what do we do here in the in your original transaction?
‫You're going to do us some quantity from sales, which it doesn't have as an unbounded query.
‫There is no there is no bound to it.
‫So that means we're going to unfortunately some all of them and go to product three.
‫Yikes.
‫So we get 140, which should be 130.
‫We read a committed value.
‫No, that's not really a dirty and that just happened to show up in our range query.
‫Right.
‫It's not really a repeatable read because we never actually read Product three to begin with in the
‫previous one.
‫We read these two values and they changed later.
‫OK.
‫So and why are we differentiating between the two is really the implementation when it comes to it?
‫Like if you if you really want to know your guys are interested, it just comes down to locking in non
‫repeatable reads.
‫When we read something, we wanted to guarantee repeatable reads and you think that the transaction
‫reads, it just obtains a shared lock on it.
‫It's not really a shared luck.
‫It's one kind of locks.
‫We're going to talk about locks in this course as well.
‫And then and then if it just make sure that this red read doesn't change for that transaction, that's
‫what it makes sure of.
‫But how does it get rid of new rules that has never been read?
‫How does it get?
‫You cannot even see the rule to lock it, to prevent it from changing you.
‫That's what.
‫On Tom Ridge, you can grab it right, it's Phantom.
‫So last update, let's talk about the last read phenomenons.
‫So then Tim thing here we know we're going to begin to transaction in parallel concurrently.
‫We're going to update one of these guys did the quantity to increase.
‫Let's say we added a $10 sale to 10 units of sales to product one.
‫Now from 10, it turns to 20.
‫Transaction two.
‫Updated the same quantity to add five.
‫But guess what?
‫This quantity now it's reading the original value because they started the same thing, so both of them
‫actually read quantity as 10, though this guy is also 10.
‫So now what it will do is it will take the 10 plus five and then store it into the product.
‫So now we have 15, so now we lost the value here.
‫OK.
‫And that's what happens in this situation.
‫So now we commit the transaction transaction to us faster, so it committed.
‫And now we have the value 15 and now transaction one assumes that it made the sale.
‫It will do the same.
‫And we're going to give you a completely different result.
‫That is absolutely wrong because it should have been 180, but we got actually less resolved.
‫Why?
‫Because our update was actually overwritten by another transaction.
‫And as a result, it was lost.
‫One of the nastiest thing.
‫And this is going to be solved basically by actually locking the row said, Hey, if someone rolled
‫over, locks are very popular here, we're going to talk about them here like, Hey, did I just change
‫this?
‫I don't want anyone else to change the same rule.
‫There is no point of it.
‫Let me finish and then release it.
‫And then once I'm done, you go your own merry way.
‫All right.
‫We talked about these phenomenons, we need to talk about isolation.
‫I know this, this lectures little long, guys, but there's a lot of information here.
‫Feel free to ask questions and to understand this better.
‫So isolation levels for in-flight transaction, what are they?
‫So I'm going to talk about many, many isolation levels they that were invented to fix those read phenomena
‫that we talked about.
‫So when I try and start my transaction, you set this isolation level using key what I believe set isolation
‫level literally in a transaction.
‫This is the sequel command for you saying, Hey, I want to start a transaction with Reid uncommitted.
‫That's the first one which I believe no database supports except sequels ever read.
‫Uncommitted is just means no isolation.
‫I wouldn't to read anything you read is visible by any body outside that changes this value.
‫I just read uncommitted, as is related to the transaction itself, so it means no isolation and this
‫is from outside is visible to the transaction, whether it's committed or not.
‫That's what's called read uncommitted.
‫You can read uncommitted values as a result.
‫You can get dirty.
‫Reads here bad, obviously.
‫But if you think about it, it's technically fast because I don't really need to maintain anything.
‫It's questionable because the implementation, you know, know what the implementation is.
‫You just assume from theory that it's fast but could be not.
‫Really committed this this isolation is the one of the most popular isolation levels out there, and
‫a lot of databases optimize their engines for Reid committed workloads, which means each query in a
‫transaction only sees committed changes by either transaction.
‫And we talked about that in the examples.
‫So now if if there is a transaction that makes a change ride while I'm reading something, I'm not going
‫to see this change unless the transaction committed was its committed.
‫While I'm running, I'm still running the transaction.
‫I'll be able to see that change.
‫You might not be happy with that.
‫Is it heads could get inconsistent, but just understand that this exist.
‫And I believe this is the default isolation level for many, many databases.
‫Just understand that what if you have a long running transaction and you think that has been committed
‫will be picked up by transaction?
‫This could be bad, repeatable read, which is the isolation level was invented to fix the non repeatable
‫read write none of biddable.
‫Read Is this bad read phenomena or repeatable read is the isolation level to make the reader repeatable.
‫That means as I read and I read it again, is not going to change.
‫I can read a thousand times as long as I'm in the same transaction.
‫The value will not change.
‫Let's read this definition.
‫By the way, I rewrote this definition based on my understanding.
‫You will not find these definitions on the web because, frankly speaking, computer scientists always
‫complicate things because they added all the all of jargon for some reason.
‫Right?
‫So based on my personal experience and professional experience working since 2005, this is what I give
‫you.
‫I give you things that are practical that you can touch and feel right.
‫Fixed a typo there, here.
‫So the transaction will make sure that when our query reads are raw, that role will remain unchanged
‫while it's while the transaction is running effectively, right?
‫So that's what it means.
‫Only when you actually when the toxin actually reads a value, reads an object, needs a role, reads
‫a column.
‫Then we going to make sure that as long as this transaction is running, that add value will never change.
‫But if you didn't learn it, if you don't read it, we're not going to copy a version of that role.
‫If you if that, if you can get my point, right?
‫Because how do you implement something like that?
‫Think about what the database is doing for you.
‫There is cost for everything, and this is a cost.
‫If you you can, you can take a snapshot of the entire database if you want.
‫And that's another observation level, by the way, called snapshot, right?
‫But that's also expensive.
‫There is a thing snapshot, each query in a transaction only sees changes that have been committed up
‫to the start of the transaction.
‫It's like a snapshot of the whole database at that moment.
‫This is guaranteed to get rid of every read phenomena.
‫This repeatability doesn't get rid of phantom reads because we saw that phantom only just sneaks in.
‫It's an insult.
‫We never actually read a value to a locket or to to obtain some sort of a lock so that we can aversion
‫it.
‫We didn't even get to do that right.
‫Snapshot isolation.
‫Just hey, it's just, Hey, this is this is what I read at that stage.
‫And Postgres repeatable read is actually a snapshot.
‫Isolation is just everything a versioned.
‫So if I'm going to start a transaction, you kind of timestamp which version you are in.
‫And then as a result, you're always going to agree this version.
‫So even if you inserted rows show up, you will not pick them up because the database will filter things
‫based on your version effectively.
‫And that's the power of the snapshot isolation.
‫The final one, or the basically the slowest, if you think about it, is the realizable.
‫That means there are no concurrency anymore.
‫Just each transaction is physically, almost physically database is implemented differently.
‫But as if they are serialized after the other.
‫So if there are three transactions stored in the same place, they will be serialized based on.
‫Now this depends on the linearity, ability and stuff.
‫Complicated stuff like that.
‫Which one goes first is the question that the database tries to answer so that you get almost the same
‫results.
‫And obviously, I talked about that each day of and permit isolation level differently.
‫Don't don't overstress, will this isolation level just understand that they exist?
‫Most of the time, you're never going to run into this complex situation.
‫But if you do know that these things exist, especially with a lot of users changing and reading at
‫the same time, this is when you start.
‫You need to know about this, at least.
‫I thought this from Wikipedia.
‫I like this table a lot.
‫It doesn't have the snapshot isolation, but if you if you look at this, you can see that read uncommitted.
‫Everything may occur with the all the reed phenomenon.
‫A. This reed committed.
‫Everything can happen except dirty reed.
‫You cannot get dirty because I'm reading committed to stuff repeatable, right?
‫You don't get dirty leads.
‫You don't get lost updates and you don't get none.
‫Repeatable reads as well.
‫Because we fix that old repeatable.
‫But you still get fandoms.
‫But in serialized well and also snapshot, you don't get any of this.
‫It's all fixed effectively.
‫Right.
‫And there is a slight difference between, say, realizable and snapshot.
‫Realizable is effectively just slows things down.
‫If you think about it, right, because it just serialized this thing while Snapshot takes a version
‫of the database at the start of the transaction.
‫Now, when I say a version of the database, I'm not talking about actual backup, right?
‫It's the database have to implement this in a optimized manner.
‫Makes sense.
‫Guys, let's talk about a little bit of database implementation of isolation.
‫Is that devious implements isolation level differently?
‫Of course, there is the pessimistic, pessimistic the dust bowl.
‫Is that a pessimistic yet pessimistic approach, which is this me, the pessimistic concurrency controls?
‫To be specific, it means I'm going to use locks and I'm going to dedicate an entire section.
‫Lecturers talking about locks talk.
‫When you get to that, you're going to learn more about it.
‫But effectively, I'm going to opt in.
‫Are a roll over locks, table locks, page locks, a table lock that means any change I make, I'm going
‫to put in a lock and the whole table.
‫Very, very expensive.
‫By the way, I run into a lot of situations where my transactions lock the entire table called lock
‫escalation.
‫It's escalated the road to a table lock and then all transactions were just pending trying to change
‫other values.
‫And it couldn't because the table was locked.
‫Page lock the actual logical page that contained the changes on lock.
‫This is especially true in case of clustering.
‫If you want to cluster your table again, we're going to talk about that as well.
‫And then these pages that you write to also have certain locks.
‫So effectively there is a pessimistic approach lock.
‫Hey, I'm changing.
‫Lock it.
‫Don't let anybody touch this.
‫So if you lock a role, only that role won't be able to touch it.
‫You're going to see some examples.
‫When I showed in both Chris and Michael that when we tried to update something, it will succeed.
‫But another transaction will try to update.
‫It will just sit there and wait because the row is locked.
‫Optimistic usually don't use the locks.
‫That's the other approach.
‫That means there will be any pending transaction.
‫So also not only that.
‫So nobody will wait on the other.
‫And also the benefit of this is the lock management, which is extremely expensive, by the way.
‫Now, because how do you know about a role as lock?
‫You have to maintain that memory, right?
‫You have to keep track.
‫The database has to keep.
‫It's like, Oh, this is luck.
‫This is not.
‫This is locked.
‫This is expensive, especially rollover locks.
‫If you have like 700000 tape rolls that you need to lock.
‫This is expensive, so optimistic a lot of people prefer this approach where, hey, I'm not going to
‫obtain an actual lock, but I am going to just let things play by ear.
‫And when something actually when people start people, when transactions start stepping on each other,
‫boom, I'm going to fail the transaction.
‫And the user should just retry, and the error is called usually a serialized, well, error you get,
‫it's like, hey, some something changed at the beginning of the transaction, so that just retry the
‫transaction.
‫No sequel that it was his preferred.
‫This approach to locking because locking is expensive, repeatable, Reid locks.
‫I talk about that a little bit repeatable.
‫Reid locks the rules, it reads, but it could be expensive if you read a lot of rose Bosca's implements
‫repeatable read as a snapshot.
‫We talked about that right snapshot and repeatable read, and Bosca's are identical to the same thing.
‫This is why you don't get phantom reads with poor scores and repeatable rates, where in other databases
‫you get phantom reads or they're beatable reads and.
‫Spoiler alert, that's part of a quiz.
‫See, realizable are usually implemented with optimistic concurrency control, because if you actually
‫truly do see realizable, then is going to your data is going to be so slow, right?
‫Because what is the realizable just one after the other, right?
‫So that's why database is implemented with the optimistic concurrency control.
‫You can also implement that pessimistically if you want to select for update.
‫Hey, I'm going to select this, but I'm going to lock it.
‫That's what you do.
‫So let's summarize those election isolation is the result of having the transaction run in complete
‫isolation from other concurrent transactions.
‫As a result, we get rid phenomenons and we have a lot of isolation levels that have been invented to
‫fix great phenomenons.
‫Let's jump to the next lecture.
‫Good job.
‫By the way, I'm finishing this lecture.
‫Good job.