Transactions Internal implementation write ahead log and locks with banking examples

hello everyone my name is Larry and in this session let's learn about transactions and lots to support the transaction the moment your brain hears the word transaction obviously you will be start thinking about database transaction it's quite fine but transaction is not just limited to database but it's a very general concept and in this session I'm gonna explain the different kind of laws which we use to support the feature transaction both in case database and in case of non database kind of implementations as well I'm pretty sure that you guys know about transactions and how it exactly works these examples in this particular video is to make the concept stronger and understand whether the different type of locks available to support the transactions let's take an example in which in this scenario there are two friends who is trying to purchase something on Amazon from the existing wallet considered the Amazon wallet has about hundred dollars and both have the login information and person a and person B are both trying to purchase something of worth $10 now consider a hypothetical scenario in face both a and B are requesting or you know doing the payment at the same microsecond or nano second what happens in that case is both request goes badly into Amazon server at the same time things like that and then the Amazon will obviously have a in on Kentucky owed or makes the connection and what if those two conditions also reaches to the database at the same time and the request to divert the money from the Amazon's wallet also happens at the same exact time what is expected to happen so do you think that both to first when the both request goes to the database and the both subtract ten dollars the result will be just ninety dollars or is it eighty dollars are some error are is it like locked and what should what will happen I really what should happen is definitely if two person spends $10 and $10 from the same account definitely mean that they erase the balance should be $80 it's not supposed to be natural if it balance is $90 that means that there is some serious bond in the system if it is H zero then everything is what you find and if is an error then it's not reliable and if it is locked then also it's not writable and hypothetically these two will never happen and this will also not happen and always this is what happens in Amazon if you do purchase something in Amazon okay because that is what is expected to happen right even though both person are doing the transaction at the same time the amount or the money should be directed in the proper way okay so now how this feature is supported maybe you guys know the answer but let me explain it to you guys this is all happening because of the transactions and even though both requests to modify the same record goes to the DB this is all happening because of transaction and how transactions work is saying when the a request to you know didn't ten dollars from the existing balance of hundred dollar that that particular row will be locked that particular row will be long and 100 out of 100 - $10 will happen and the resulting will be $90 will be updated in the same row until he finishes the you know Direction adaptation of the you know varies entry or the balance in the database B is not they will not be allowed to modify or touch or read the data from the so even though both requests land at the DB at the same time either one will be picked first and then whoever gains who were gains the long basically they are the one whose no balance will be deducted first and then the second guy or the B will get hold of this log and he will subtract it basically once this a finishes his job he will also log the same row or the record and then he updates it took 80 dollars so the answer is $80 so that means without transaction a lot of things cannot be implemented and it's not easy so what is the transaction transaction is a unit of work that you want to treat it as whole and it should either happen in full or nothing should happen at all to give a better perspective about it and take one more example in which the scenario is transferring money days of person a and person B's account in which person here has about $10 and person B has $0 in its account now how to use transaction to do this work properly so to do that there are three essential steps to do that first one is you need to withdraw so whoever is going to perform this operation should first withdraw you know specified amount from the source account that is a and then he need to deposit to account B and meanwhile if he wants to take some Commission he need to take that commission as well so now this this order is pretty much standard like you need to first withdraw off and then you need to deposit and then direct the Commission or the deducting Commission can go in between we don't which one deposit let's keep this order and there are three different operations happening and how we make sure that out of these three performs as a single as a single unit the problem here is considered we are not using transaction we're just executed these three different you know operations in a simple function in a thread okay consider you know user a has requested ok please transfer $5 from account my account to B's account in this case if we are not using transaction what happens next consider we want to transfer $5 from A to B now what happens what are the you know steps we perform is first we have to withdraw $5 from account a and then transfer that $5 to account B okay and then you direct the Commission may be now consider we have $1 Commission and then you definitely need to update you know this was account 8 so totally we have you know in our source account a that is about 4 dollars because Fido will be transferred and $1 the Commission so in a we have $4 in B we have $5 okay this is all good hypothetically this will work if there are no parallel transactions are happening okay and if all of these operations worked one by one perfectly so what we saw was a ideal case now let's discuss some different use cases say for example what happens when there is an error among one of the instructions in these three instructions or there is a server failure say for example now what happens is now a a has told the bank to transfer for example $5 as earlier $5 it will be so now what happens consider that we are going to get error in some of the instruction we don't know where say for example we withdraw so select execute let's execute the instructions so we need to detect the $5 from a so when we withdraw we are going to withdraw $5 from account a so the balance is another PI donor so now now we are trying to transfer this $5 to to bank account B so now consider all this function through some error in that case what happens if in if the core is not only handle and the spy order is not deposited back to a this fight on it is kind of gone this in the account a we only have fight ordered and the fight owner was not successfully transferred to be and that fighter is kind of gone so this is what happens but if you use the transaction so consider now we are telling the reader is that all of these three instructions are covered into a transaction so what transaction says is transaction is a unit of work and it treats all of these instructions as a whole and if everything goes well then it is all otherwise it's nothing so everything should happen everything should execute perfectly then only the whole thing will be you know succeeded otherwise everything will be scrapped okay now what happens is the same scenario first $5 will be deducted from a copy so the balance is $5 and in the deposit say we got some error in this case what transaction will do is it is going to revert everything okay and then the $5 will be added back to account a so we still have the $10 in a country and we never transferred $5 to P so that way we are safe so transaction helped here if for example everything worked fine then our $5 will be deducted and fighter will be deposited to account B and then commission will be deducted again from the account a and then the database knows that all of this operations were performed successfully so it commits into the database so all of the numbers will be reflected properly if some errors telling to you is going to revert if everything succeeds it is basically committing into the database so everything is good and now let's talk about one more scenario that is concurrency so what happens when there are concurrent operation on the same account happening at the same point of time remember from the earlier example I showed you that when a person a and person B are trying to do Amazon shopping at same time trying to buy two things from the same wallet still in that case also everything in all of the amount should be perfectly directed like $20 was supposed to deduct and it it happened now how do we make sure that is going to happen and what happens if we don't use transaction consider now these operations operations are not protected using transaction now what could go wrong say for example two times a has told to transfer some amount B so it has told to transfer $5 okay to be and also meanwhile at the same time here has also told to transfer $5 to C so now what happens so consider both the request came to the database server at the same time exact microsecond or nano second now what happens is the withdraw function will be executed in that case obviously inside the this particular function will be a having a select statement basically it checks what is the balance in account a obviously the both efficiencies that a has $10 a has $10 right so in this case what happens is both the thread think that a has $10 and $10 they didn't fight honor and deposit it to B and this thread also does the same thing they did at $5 and the deposit e to be indeed at $1 Commission $1 Commission and the both will update the remaining balances $4 and $4 so finally what a account will have is kind of $4 but I did what was supposed to happen ideally total of about $6 here and $6 here $12 was supposed to be directed from account a and total of around $5 to account B and fight orator comc was supposed to be deposited but now what happened was both you know transaction succeeded by transferring $5 $5 and one of the Commission under Commission was generated and it has $4 but it is so wrong right so this is like a totally buggy application now what was supposed to happen now one of the instruction supposed to be was supposed to be executed and then only the other instruction was supposed to be a secret 20 didn't happen why because we are not kind of protecting all of this operation or we are not training all of this operation as a whole and they are not predicted by transactions or any kind of locks and the problem here was the these transactions were not properly isolated and then not I know atomic and that was the problem so what happens now if we protect this these three operation with a transaction now what happens is as soon as both requests came at the same time still only one either of this truth face only one threat will actually get to access this particular no bank account a or the a's account so for example if a if this thread thread 1 and thread two first thread 1 will acquire the lock and then it does all these operations and then it releases the lock so now try to will acquire the lock and then it does the operation now how different the operation works now now even though both the threads the request came at the same time to database first the thread 1 will acquire the lock now the calculation looks like something like this so first it will check it will basically be drawn $5 from $10 the remaining is $5 and then it deposits to B that is it is sent to B and then it is going to dig at Commission that is one more dollar so now the remaining balance in the account a is $4 now all done right so the transaction went successfully now the thread will release the lock and then the other thread will basically other thread or the connection it could be tried out of connection so two or three one trick to thinking like that so now thread two will acquire the lock now the transaction will start now it will try to withdraw now it will try to be drawn $5 and it was supposed to send it to or deposit to to see now since that one balance is only $4 now this transaction will fail in the first instruction itself because we can't deduct $5 from the existing account that because we only have $4.00 in the balance this is how it was supposed to the transaction saved us from going bankrupt so because of the transaction feature we got two main advantages the first one is we got a reliable unit of work wearing we had the correct recovery procedure from any of the system failures or any errors and also it gave a method of having the data of consistency right so that's - that's a really important part because we always expect our data in the DB or anywhere to be always consistent so transaction gives us the capability to keep the data consistent and also it gives us a ability to recover from any potential errors which we see in our application and also it gave us one more feature which is the isolation from the concurrent axis that basically as I have already explained these days we have a lot of distributed systems so that means that we have a lot of concurrent axis of data it will be happening on from the DB or from any of the storage right so transaction basically provides us the you know isolation from these concurrent accessors so you just learn what are the different advantages of using transaction in your applicant applications it is very useful right but you also need to understand how exactly these transactions transaction feature is implemented inside the databases as well different databases implement the transaction feature in different ways but a very generic common way to interview no implement the transaction feature is right ahead love so this is how it works say you have the single unit of work which internally contains a lot of Quays when you actually stop it all of these queries are to execute to the database those transaction related queries will basically end up into the wall so before modifying all of that data the lock will be acquired to the rows which will be affected by the transaction say in this case is from just modifying one particular row a lock will be placed basically ensures that nobody other than this transaction will update this particular row but there are different ways of unlocking as well save your transaction isolation level is kind of said to allow it to read but not to write this is what happens this log will make sure that no one is going to update this record why this transaction is happening but it might annoy you to read the information which is I'll be there before this funds actually is updated so once the transaction queries hit here all the queries which also includes the redo and do and in all the commit related information will be recorded into the wall file this is basically a in memory storage and once all of this information is committed basically eventually all of the content which is there in the world will be flushed back to the actual databases so when the transaction is happening what you should remember is when the transaction is happening this particular row is locked but the data is not modified until all of this transaction related you know single unit of work has been executed properly once it is executed all of this modifiable data will be later pushed on to the database to persist for longer duration so what you should look for here is there are two things one is long and the other one is one that is the right headlock so this particular right I had a long data structure or whatever you call it as this feature basically provides you to do you know commit and rollback that kind of features but the log here is the one which basically provides you the you know consistent data modification because once we lock this particular row nobody is allowed to update this particular row so hence this particular block is very important in transaction this is just example in database but I'm going to show you a lot more examples where the logs are the one which plays a very crucial role in providing the consistent data are providing the transaction capabilities so this is just in the database but let's learn what are the different kind of logs available which actually helps us to do transaction like features in distributed systems

Channel: Tech Dummies Narendra L

Views: 57,533

Keywords: Amazon interview question, interview questions, interview preparations, algo and ds interview question, software interview preparation, developer interview questions, Facebook interview question, google interview question, Technical interview question, software architecture, system design, learn System design, transactions implementations, transactions in online payments, how db transactinos works, banking queries and transactions, what is transactions

Id: DR7j8b9LIhE

Length: 20min 38sec (1238 seconds)

Published: Thu Jun 13 2019