Transaction SQL tutorial | SQL Commit and Rollback | ACID Property in SQL | Edureka Rewind

Key Takeaways

transactions so what are transactions a transaction is basically normally we execute single queries but sometimes we can make a group of queries group of operations that can be executed to solve a single purpose so in that that particular thing we use case we use transactions it is basically to run the manipulate manipulation operations all together so if a single action of that particular transaction or single query of that particular transaction fails then entire transaction leads to fail it never returns anything so this is the purpose of transaction for example we have to transfer a money from one account to another so in that case first of all we are going to check that if amount is sufficient in our account or not if it is fine we are going to deduct the amount from that amount and going to transfer it to the next amount so this is again this is a process a cycle that should go so this cycle is done in a transaction so that if anything unexpected occurs we can roll back we come back to our initial stage so here is we call it acid basically acid principle that is used in transactions acid means a means Atomic it simply means all the operations are completed successfully either all the operations are completed successfully or no operation is is completed means if if there is any issue with with any of the transaction action we just roll back everything so we maintain our atomicity then it is consistency it simply means either the transaction is processed completely or it is not processed at all so it Remains the database Remains the consistent if we are going to update the uh data of three tables in transactions and if I have updated data for first table and it up while updating for the second table I got some error so even the data for the first table will be rolled back simply means the data will remain all consistent they will there's no chance that data will be inconsistent next is isolation isolation simply means that each transaction is independent of other transactions no transaction can overlap with any of the other transaction so it is isolated with other other transactions durability simply means once the transactions or all the transactions are executed and we have committed them we have used the commit keyword so all the result will be committed and it will remain permanently in our database server so this is called durability so this is the assd principle that we follow in transactions you might be that uh our transactions basically begin with begin and end with come into a roll either we commit our transactions or if there is an issue we roll back our transaction so commit simply means if transaction is completed successfully commit command will make the changes permanent and roll back means if there is an issue in any of the transaction action everything will be roll back so the behavior of the transaction can be controlled by Auto commit right Auto comment simply means if we make it basically it is by default one if it if autocommit keyword is one then all the transaction statement is committed automatically we don't have to use the commit and the end to commit the transactions when it is zero then we have to provide the commit close at the end of the transaction to commit all the queries so here you can see we can execute SQL commands by using myql query right we can we can use transactions in p SP as well we can use our ml query function to start the transactions or to commit the transactions or to roll back the transactions so basically the transactions can be executed straightforward directly but it is always better to use a particular table type to do that basically we use inod DB tables in our case so I IO DB tables are called transaction save tables means transactions can execute perfectly on these tables and our commit and roll back will work as for the asset principle so we use INB tables simply what we do how we can use uh from the myql version 5.6 I think 5 yes from the 5.6 the default table uh table type is chosen as I before that the default table type was my exam we had to manually alter our table to make it INB if we are going to do any kind of transactions or we are going to uh we have we are having we are going to apply some kind of constraints on our database like uh foreign key constraints and all so we we are going to use inod tables in case in that case we are not going to use mym mym tables mym tables are basically for reads if we are doing only read from a particular table we are going to use myam but if rights are more reads are less then we should prefer in tables I think you might already be aware of these Concepts as I think suit are uh proficient in Oracle so you might be hard of this teams and terms you might be aware of what is table type what is INB what is mym here you can see we are using Create table student we are giving the column name column type and then size not null email Comm typ column type size and type of table so here we are giving type is equal to inod if I don't give this type if we are using 5.6 version of my SQL it will automatically take it by default take it in a TV if it is we we are using the earlier versions it will take mym so this is the difference that is put in the latest version of my my SQL because nudb tables are most preferred tables if we are going to perform any kind of transactions or we are going to do any kind of referential Integrity kind of thing or like uh foreign key constraints so we we use in know deit tables in that case so this was about transactions so first of all let's see this what is this Auto commit Auto commit I told you that if we set we can set like this set auto commit equal to 1 now if I execute it now if you start a transaction and if we run something it will be committed automatically if we make it by default so in order to make a transaction we keep it auto commit equal to zero so I will do so I will do set auto commit equal to zero I will to update like I'm having students table and in students table I'm having uh these records so what I'm going to do in in my first record I'm going to change email address update students set email is equal to moit yahoo.com right now it is gmail.com I'm going to do it yahoo.com then I will do back see now what happens here I've done start Auto commit equal to zero so it is showing me one query executed now I will start my transaction and once I've started my transaction I will update students now if I see select star from students what you can see the email ID becomes moit yahoo.com now I do roll back now see what happens see email has again become M gmail.com because all the queries that I'm running name that are under a transaction transaction is a set of queries that either all will be committed or no one nothing will be committed so if I do the roll back everything will be rolled back so you can see the student email again becomes M gmail.com but if I update it and I check it it is moit the.com but if I do commit then after doing the commit if I try to roll back I will not be able to roll back because commit simply means it makes the changes permanent now if I take run the roll back thing I will not get moit gmail.com I will get m.y how.com because after making the change I have committed it and if it is committed you cannot roll back it right so this was commit and roll back this was a transaction me you start a transaction you execute some queries if any error or un exception occurs in between you can roll back and everything will be rolled back from each and every T table or each and every change that you have made everything will be rolled back I think uh this was the simplest way that I might have told you because normally transactions are very complex and showing example of complex uh transactions May confuse people so this was the simplest example that I I I could give you you might be clear now what is transactions how we start transactions what is roll back what is commit