‫Hey, guys, what's going on?
‫So we have talked about acid, the four properties in a theoretical manner, but it's time to put them
‫in practice and explain those four properties in an actual database setting.
‫So I'm going to pick this, but you can do the same exercise with any database you want for some.
‫I would luck to use Docker because this way I can try out many, many databases with just one command
‫and I have spit up another database just like that.
‫So let's go ahead.
‫And it's been a database right here.
‫I've got to keep this up because I'm going to need that to a terminals, but I'm going to open up a
‫new one right here.
‫And let's do a little bit about Puskás Container going to Docker Run.
‫Let's call it doctorand.
‫Let's give it a name, dash, dash name is going to call it acid.
‫It's got acid and we get to do we don't really need to expose the port or anything because we're going
‫to really go into the container itself, bash into the container.
‫The next command is let's go ahead and detach that and then let's pull up Puskás 13.
‫I think that's the latest thing before we run that actually we forgot something very important, which
‫is that she was Griss password equal postscripts.
‫You have to give it pulseless user password as go go ahead and run that just like that is going to take
‫a little bit while in your end to load the image.
‫But once you have it, you're ready to rock and roll.
‫Let's go ahead.
‫And so so let's make sure that the container is running just like that.
‫Yes.
‫Here is my PJI acid.
‫It's running.
‫They always make sure that it's running because you might have spilled something in the command or did
‫something bizarre or something is wrong.
‫Right.
‫So go ahead and do it and get it into the acid.
‫And what can I do?
‫We will execute a physical because of the PostgreSQL command.
‫And I want to log in as Deshu that Posterous user that just like that, we are in the process commensal
‫now.
‫We can execute beautiful stuff.
‫We can create a database, we can play that create tables.
‫So since I am in the Postgres database, I have a database called Polska, so I'm going to play up with
‫that database right there.
‫Right.
‫I was going to do create a table called Products to Test at Tomasetti and I'll go ahead and create table
‫products.
‫Let's do a PID serial.
‫So that's a little increment in my school, I think it's called.
‫And sure.
‫Let's make this the primary key.
‫And I think we can just continue doing that stuff right and then the online name as a text boom.
‫What else price is double our shorts, double our Flodden because I keep forgetting in different databases.
‫When you work on many databases, you get confused.
‫And the finally is inventory.
‫How much inventory do we have in this product?
‫And let's just use an integer here.
‫And that should be it.
‫Yeah, that's what I thought.
‫It's not called double.
‫It's got to be flawed, I think.
‫I think it's double in my school or I keep forgetting.
‫All right, now we have a product sale, let's go ahead and create a sales table so we can produce sales.
‫Great table sales.
‫Let's go ahead and do a sale I.D. That's a cereal.
‫Also primary key and enter the primary key.
‫That's the product ID I can create as a foreign key, but doesn't have to.
‫So that's an integer, right?
‫That's a product I.D. to draw.
‫Boom, price float.
‫We learned our lesson, so that's an entry in the sales, could be a product, could be sold in a different
‫price.
‫Right.
‫But the current price of the product is in the product table.
‫This is what was sold.
‫That and then the quantity as also an integer, a boob.
‫All right.
‫Now we have two beautiful tables let's test out at Tomasetti.
‫So the first thing, listeners populate some some rows on this table.
‫So I was going to do insert into products.
‫And let's give it a name, I don't know what are we insert name and a prize and inventory or simply
‫say, I'm just going to do it one one.
‫Royo let's call this a table at tables.
‫Very confusing phone selling phones.
‫And the price of the phone is nine nine nine nine nine nine nine.
‫That's an expensive phone inventory.
‫Let's say you have hundred units, then boom.
‫And then.
‫That's it.
‫That's the only product we have.
‫OK, so now let's test out our Atomos City.
‫So I mean, if we did select store for our products, you can see that we have this row.
‫But we're going to do is we're going to make a sale.
‫We're going to sell, let's say, 10 phones.
‫Like we sold 10 phones.
‫What does that mean?
‫The fear of walking with Demand-Driven designed that is one has scribed, but it's in the database,
‫it's broken into multiple queries in a single transaction, and so a sale is often units.
‫That means you have to deduct 10 units from the inventory and then you have to insert a row in the sales
‫table saying that you have inserted that you cannot do that.
‫And two transaction, you have to do it in an atomic transaction.
‫That's why we explained in the video the lecture.
‫So we've got to begin a transaction.
‫And then we're going to the first transaction we're going to do is update products, right?
‫What was the phone idea?
‫Let's just do from products you might want to query or process.
‫Idea number one, processed product line number one.
‫So we're going to do is update products.
‫Set name.
‫A bread product, set, inventory, inventory, equal inventory, minus 10.
‫Well, that's in this transaction.
‫So now if I query that product again, you can see it's 90, right?
‫Very, very critical here.
‫So now if I go ahead and insert.
‫Let's assume.
‫I crashed right here.
‫If I crashed right here.
‫What will happen, you just lost 10 units from your phones that you did a really sold OK, and that's
‫the idea of atomistic because you have to follow it up.
‫Right.
‫So, like, let's say I crashed right here.
‫I don't know, just this killed the whole container just like that.
‫If you do that without automaticity, it will be a disaster if you come back to the database.
‫And then you said select star from product and you saw that it's a 90, but no.
‫It says hundred y.
‫Because of animosity, we started a transaction, we did an update, but we crashed, we exited fossickers
‫in this case simulating a crash, right.
‫That is the idea of at the most of all, the transaction have to be done in as an attempt to split those
‫and those do the transaction again in the right way.
‫Again, transaction doing an update.
‫To them, victory, and then now I'm going to insert a sale and certain the sales process ideia.
‫Price and quantity, and let's just do values.
‫Number one, I sold it and I thought I sold it at nine nine nine nine nine Sam Price, they sold 10
‫units room.
‫And you can break it out into five minutes at this price and five minutes at this price will not.
‫And as we did of that, now, clearly the sale of Sable's you can see that we have 10 beautiful units
‫right here and my products are 90.
‫So now if you some of those puppies up.
‫You get a hundred, and that's called consistency's.
‫We just explained atomos and consistency and the same concept.
‫And this is only viewable in my transaction.
‫So now if I went to a completely different terminal and tried to query again, I'm not I'm not committing
‫yet.
‫Right.
‫And I do like to do the same thing.
‫Hopefully we got a school there, a big acid, then if I do select star from products, you can say
‫they're still 100 and if you call it the sales, stable.
‫There is nothing and that's part of isolation, because now we're we're in an atomic manner, so these
‫two things were inserted in that transaction, but we didn't roll back or commit the moment I commit.
‫Now, we have a nice view and out of the data and the data is now flushed and available, visible to
‫all transaction.
‫Now, if I created it again, you can see that we have a sales now zero.
‫So that is one.
‫And if you query the product table, you can see that's 90.
‫So what I was to talk about here, talking about Midcity City, we talked about consistency because
‫if you don't have to Midcity, you don't have consistency.
‫And you can argue with that, obviously, guys, because you can say, hey, I can I can be smart and
‫make a single query.
‫Right.
‫And I didn't for the longest time, Mongo DB, for example, didn't have Atomic City across documents.
‫It has only atomos of the across a single document.
‫And people are fine with that because most of the time they will do their data model so that it is a
‫single document and instead of not, say, normalized like we do here.
‫All right, let's jump into isolation.
‫All right, guys, so I went ahead and inserted a few rows in the table that you don't have to see the
‫process.
‫I'm going to cut it out.
‫It's a straightforward thing.
‫We sold a couple of units it here, a couple of units right here.
‫So now let's test out isolation and to decide isolation.
‫Guys, I want to do the following.
‫Right.
‫So to this isolation, guys, I want to do the following just with normal transactions.
‫I'm going to start a transaction in my first session right here.
‫And I'm going to do a normal query saying select product and the count of the product.
‫All right.
‫I just want to see how how much sales did I made on getting our for each product from sales and group
‫by group.
‫If I do that all of a sudden now and this query, I have three sales for like two and three sales for
‫product one, which is pretty good right now.
‫I want to actually print the report that that shows that what are this list?
‫Just the whole thing.
‫So in order to do that, you do select product.
‫Price, maybe you do a join, but we don't.
‫We're not going to do that quantity from sales.
‫Right.
‫But before you going to execute that, you're going to execute at another session started right here.
‫OK, and inserted a new sail, OK, you just inserted a new sail.
‫Right, and you are just doing your own thing right here and generating this large report, but if you
‫reach this stage and meanwhile think so, think of these two US as concurrent application transaction
‫going at the same time.
‫Someone is making a sale, someone is generating a report.
‫So in the same time, I'm going to generate a new cell.
‫Hey, we just sold a new phone, ram pedi.
‫Analysts say price and quantity, home values.
‫We sold the new iPhone.
‫And at a price nine nine nine nine nine nine is just said, we sold 10 more phones and we also go ahead
‫and update the products where sit.
‫Quantity, quantity, no set inventory, equal inventory milestone where the equation because we just
‫we just made etc. So we need to decrement and then what we did is we actually committed.
‫We're done.
‫We made us all.
‫And we weren't back here.
‫We're continuing to generate the report again, this is happening in parallel.
‫I'm posing it just to show you.
‫But this is now we made a sale and then you printed the record.
‫So you printed that.
‫You made three or three sales, three sales here.
‫And then you made and then you actually printed that stuff.
‫And guess what, guys?
‫This don't match.
‫You said that you and this happens a lot.
‫Right.
‫And actual companies, where were the actual table doesn't match the counts.
‫And this happens if you do this kind of things.
‫This I'm going to talk about.
‫This is called isolation.
‫Isolation.
‫We're not fully isolated here.
‫So when it will happen here, we printed this poppy.
‫Right.
‫And we said, OK, we made three phones, actually four phones and two and three ear buds.
‫So the ear buds are correct, but the phones are not correct.
‫You just produced an inconsistent.
‫View, so inconsistency or consistency in general can happen because of animosity, which we explained
‫and can also happen because of isolation, which is what I'm about to explain here.
‫So how do I make sure that nobody.
‫Touches my view when I am beginning my transaction, if I start in this transaction, I want you to
‫give me a snapshot, right, of all the coins, and I'm going to do as if at the moment of the where
‫the transaction started.
‫And that's called a repeatable rate, so it's going to roll back this thing.
‫And then we would begin a transaction, but we're going to do isolation level, repeatable rate.
‫I think that's how you say the default was.
‫Reid committed what that means.
‫Anything that has been committed on other transaction, I, I can see it and this isolation level,
‫Merks most of the time, sometimes in this case you don't want it, you don't want a repeat operator
‫or serializable.
‫So let's go ahead and start in a repeatable read.
‫And now if I'm going to make my beautiful query, the count, hey, there are three and four, which
‫is which is good.
‫So we made four sales bad like that, but exclude the second query.
‫Took away the sale, but in the meantime, someone started another transaction, we don't care what
‫isolation level at that point where they.
‫They inserted another.
‫Sayle.
‫Email, exactly.
‫And they committed.
‫So they just made it into the sale.
‫What?
‫If you query this.
‫Notice that it didn't show up on your case here.
‫Why?
‫Because you're your isolation level prevents you from seeing stuff that other people's changing.
‫That's that's a beautiful repeatably.
‫It's expensive.
‫You can understand how it works because it works at them like sea level.
‫And Bosco's does it differently.
‫Bicyclers and differently.
‫SQL Server does it differently.
‫Oracle does it exact very similar to bicycle bicycle.
‫This is very similar to Oracle, if you want to be very precise.
‫But that said, now, if you you have a consistent view, you made five sales technically on the iPhone.
‫But if you query again that group by you only see four, you're in this other other query for other
‫session.
‫If you just select the ID count copied from what is that?
‫So we have five units here.
‫But here, no matter how many times you execute that, you're going to get a repeatable read your readers
‫a repeatable.
‫It's not going to change.
‫And that's a very attractive feature for a lot of applications.
‫Right.
‫So now once you commit or roll back, you're done if you execute equity again.
‫You see five because you're out of your transaction.
‫All right, guys, that was isolation, finally for the fun of it.
‫Let's do this, guys.
‫Exit right here and then Dr..
‫I'm going to do this, I'm going to begin a transaction, and then I'm going to insert a new product
‫inserted to explain durability and that new product, and I'm going to prepare here to kill the content
‫of the whole the whole container.
‫You stop, right?
‫But in the exact same second, I am going to insert a new sales and see if this is actually going to
‫happen.
‫If I say if it says commit, what will happen here.
‫Name.
‫Price inventory, yeah, that's just inserts a new product that's called.
‫TV.
‫Yeah, three thousand dollars and there is only 10 units of this, and I can do this and I'm going to
‫commit, but at the same time we're going to kill the container.
‫So immediately we were killed.
‫So now.
‫I want to know, did my TV get recorded that Postgres told us that it's committed, right?
‫If I do this and a lot of their base doesn't support this, by the way, like this, but it is in the
‫movie database.
‫But it does support persistance, right?
‫Essentially adorability is that means if you tell me that you committed something, that means if I
‫log in again, I better be there to see if it is out there.
‫Yup, it's right there because it told me it committed if if it didn't return the comment, that means
‫it hasn't been written and durability is a very you might say you might think of it as like, OK, of
‫course, Hosain, it's given that it has to be.
‫If I write, something has to be there by noon, some is right to memory first for a performance reason
‫and the container or the host dies, they forget to flash to disk.
‫You just lost your stuff.
‫But, guys, what do you think about this, guys?
‫Thank you so much for watching.
‫How fun.
‫Enjoy the rest of the course that I'm going to see on the next one.
‫Goodbye.