Relational Database Atomicity Explained By Example

Key Takeaways

what's up y'all this is russain nozzle from I geometry where we discuss software engineering by example and today topic today's topic is acid not the kind that you take nexium for but the other kind which is database acid which stands for atomicity consistency isolation and durability so this is a applied this is relevant for the relational databases so so let that's good let's discuss this so let's break this and what is acid and why these properties why do we have these four properties and how they are useful for us right so we're gonna start the database is Ward the first property is atomicity alright so in order to discuss these properties we're gonna take like we always do in this channel an example an actual real life example and a hello world example right so just take Instagram for it for for an example right let's assume this is all made up obviously the design and everything it's gonna be made up but let's take Instagram for it for for example and let's assume that we're gonna design the database for Instagram so you're gonna be building erm for Instagram so we'll take it very simple so you have pictures right and you have people which our users and you have people liking pictures let's take the comments out of the picture yeah so the requirements is for each picture that I post I need to know how many likes this picture makes right so that's that's one piece of information the other piece is who likes these pictures who are there give me the list of all these users so the way I'm gonna design this is yeah let's go ahead and write like two tables here and I'm using this online tool sketch that I oh it's a really cool app you just found it so yeah it's gonna make a table called pictures and then we're gonna make another table called likes which are the individual likes of these right and what do we have what oh that's big all right what do we have we have an ID a picture have an ID correct and what else picture as URL again it's all fictional right I mean they might have more and more and more stuff there but and we have like Scouts right right and for consistence make it all caps to follow the standard here so that's URL right it sounds good sounds good sounds good all right how about the likes so this will tell me like for this particular picture with this ID right this got this much we're gonna get we're gonna get two examples in a second here so just go ahead and just copy that a like will basically have the picture ID right which picture got like and literally who like did does that make sense I think it does right very simple yeah just feels like a table I guess right thank right that makes sense get on with my face here second but yeah neck sense right so yeah okay so we'll discuss atomicity so we talking all the time just to sketch up this stuff so let's say I have what I have I just posted a new picture I don't really care who posted and I mean we can add that record who is the owner of this picture but it's not relevant for this we can add a lot of stuff so it got an ID so picture number one all right and has some URL and zero likes okay delete stuff in a second but we have no likes for this picture so let's discuss Tama City here so that's we just we just have a nice design now so you like that right so these are linked all right the one picture can have many likes I think mmm zero or more not bad in this case yeah so yeah otama City the first thing first property rights or whole thing was just to discuss that after ma city and then we're gonna discuss in different videos the rest of properties of assets so we have a database here what is a Thomas City what is that so let's say I my name is Jon Jon Snow and he found picture one which is the picture of wolf I guess along he's into that right so he likes that picture double click all right double tap and he likes it so how how are we going to update what is the series of sequel that we need to update right so what do we need to do what what series of sequel statements that we need to execute in order to update that there are two things right first the first statement is hey I'm update I'm liking picture number one so two statements the first one is update table pictures maybe you'll just write that as well all right yeah why not update to update pictures set likes count equal likes count plus one right we're ID is equal one I'm liking picture number one so that's the first statement yeah let's make it like that okay let's make it a little smaller so what is the second statement the second statement is also add me I am Joan insert a record into the likes table answers into likes yeah for simplicity you just assume that John right alright so these are the two statements that needs to be executed yeah first set that count and then insert a record okay so what is a toast otama City this brings us to the topic we need to discuss which is a tomas City atomic we need to make this unit of work that like has to happen as a one unit single unit of work that means if that statement get executed all right the other statement has to be executed so both of them has to succeed otherwise we will end up in an inconsistent state right so what will happen here is okay I'm going to basically delete that puppy okay and then yeah like one right and then I am going to actually add a new record here she's that handwriting though Jonas no while you're doing John alright look at that so yeah so I I updated that record and I also inserted a new record here so if something happened like I atomicity is this property if I managed to successfully update that picture and for some reason let's say oh okay let's say I lost connection right database went down at that moment really unlikely but let's say that that's one case where the automa City can kicks in alright and let's say I just lost connectivity I lost connection to the database so I did not manage to send that our statement which is really unlikely but yeah okay so I end up with this but I did not update I did not insert a new record for John to the likes so if that happened let's assume that I don't have a I don't have any item s at the end Mr gum might actually not have that they don't really care this is it's not really I don't care for the day if it shows like you have hundred likes and if you list though we're gonna talk about that in a minute but it's there let's say we don't we have if you don't have atomicity and I did that we our picture will show one like so I'm gonna okay using my head that's supposed to be iPhone all right sir all right okay on that alright that's John snow egg hey just click done then and he liked it but for some reason he lost connection and then he come back and he said oh there is ok that that owner of the picture says ok my wolf got one like but if I click I see I want to see who actually liked it right I want to see who liked it so you click on that and you'll end up it says okay no users ah that's an example of inconsistent results that you get out out of not implementing atomicity so atomic transaction so these two pieces of sequel statement has to be executed together and and you that's why you have to wrap it in a transaction if you're doing programming you have to hey both those puppies have to go in if one of them fail that's one case of failure where we lost connection right but you're gonna end up with a lot of other failures constraint which we'll see in the next episode you're gonna see a lot of cases so so that's and I'm not sure that that is a thomas city so let's let's do another thing the same sansa right let's so that were beautiful wolf and she decided to like that picture to says what do you in Sansa let's go okay delete delete okay this is a ridiculous all right so now sounds are like that picture so two statements then used to write and then Shawn saw what are you doing sensor look at that picture alright so we have now - right now we're gonna discuss the consistency and in the next episode so that's the second property so that's basically atomicity right if you don't end up so atomicity and consistent you're kinda linked but i prefer to gusseted up in a previous episode so you might have a lot of questions about primary keys and foreign keys we're gonna discuss that in the next episode so stay tuned guys subscribe and you're gonna see you on the next one I geometry software engineering by examples see ya