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.