6. What is Transaction Control Language? Using COMMIT, ROLLBACK and SAVEPOINT commands in MySQL.

Welcome to everything computerised, I'm CA and in this video I will be going to explain the transaction control language and the three commands present in it so watch this video till end and let's get started save this playlist basic SQL tutorial for class X and XII so that you do not miss out any video that might be important for you in the previous video I had explained the data control language let's now talk about the transaction control language and the three commands present in it so what is transaction control language when we perform operations on a table with the help of the data manipulation language that is if we insert a data into a table if we delete a data from a table or if we update a data from the table these all operations are not permanent and the changes which happened to the table are temporary and the table which is present on the actual server has not been affected at all so in order to apply these changes to the table permanently we have to do something and so we make use of the TCL commands so basically TCL helps us to control the transactions in a table here transaction means any process or operation which we perform on a table with the help of DML commands use of insert command is also a transaction use of delete command is also a transaction and use of update command is also a transaction so TCL helps us to control how we operate on a table that is the transactions in a table I think you are quite clear with the concept of the transaction control language let's now take a look at its definition transaction control language or TCL is a computer programming language basically a component of SQL which is used to manage transactions in a database this is used to manage changes made to the data in a table by DML statements it also allows statements to be grouped together into logical transactions there are three commands present in TCL these are commit, rollback and savepoint let us understand what these commands are and after that I'll show you how these work in MySQL so the first command we are gonna to talk about is the commit command it is used to save the changes made to the table and hence making the changes permanent when we use this command the changes that we had made to the table is uploaded to the server and hence our work is saved the next command is the rollback command it is used to get back to the previous permanent status of the table that is if I started editing a table and I thought that the changes that I have made is not required I can roll back or get back to the previous permanent status of the table one thing that you have to note about the rollback command is that if you committed your changes you cannot rollback you can roll back only if the changes made are temporary and the table can we roll back to its last permanent change let's now talk about the last command of TCL that is the save point command it is used along with the rollback command it is a command in TCL which is used to mark a transaction in a table suppose you are making a very long table and you want to roll back only to a certain position in a table then this can be achieved using the save point command if you made a transaction in a table you can mark that transaction as a certain name and later on if you want to roll back to that point you can do it easily by using the transactions name save point is helpful when we want to rollback only a small part of the table and not the whole table in simple words you can say that save point is a bookmark in SQL now that you have understood the basics of the TCL commands let's see how these work in MySQL in MySQL there is a setting named auto commit it is enabled by default and every command we write is automatically committed and the changes are permanent so the TCL command does not makes any sense to it you can try it yourself and check if the TCL commands work on it or not for the TCL commands to run on MySQL either you have to turn off the auto commit or you have to write a statement start transaction so we will use the start transaction statement run the MySQL command line client and write start transaction it will disable the auto commit till you close this window so whatever you do we'll be temporary and will not affect the SQL server until you manually commit it so I have inserted some rows into a table let's close the MySQL window and check if the changes are applied to the table or not in a new SQL window so you can see that the transaction that I had made before has not been applied to the table and the rows have not been inserted let's repeat the same thing but this time I'll use the commit command so let's close this window and open a new one and check if the changes were made or not so you can see that this time the rows have been inserted let's use the rollback command so I have written the commands to update insert and delete a row let's see the table now here you have to note that I have not committed so the changes that I am doing now are temporary and the table which is on the server has not been affected and is of this format and this one is the temporary table let's now run the rollback command so you can see that the table has been rolled back to the previous committed state and the transactions that is the changes that I had made using these three DML statements are discarded let's run the exact three DML statements but this time I'll use the commit command before rollback so I have committed the transactions let's run the rollback command so you can see that the rollback command did not work this is because we have made the transactions permanent with the commit command let's now see how we can roll back to a specific position in a table with save point let's name the current position as initial now I'll run the insert command and name this transaction as ins after this the update command and name as upd and at last the delete command and name as del you can take any name of your choice I have taken these names because it will be easier to refer let's see the table so this is the temporary table I don't want to execute the delete command so I will roll back to upd it will discard all the transactions after upd and the transactions before upd are not affected so this is how the TCL commands work you can experiment on it by running different statements and explore the different outputs but one thing that you have to remember that in MySQL first you have to execute the start transaction command before running TCL commands or else it will not work so this was all about the transaction control language in the next video I will explain the detailed concept of the Select statement so meanwhile like this video and subscribe the channel if you haven't yet thanks for watching and see you in the next video *subtitles by CA*

Channel: Everything Computerized

Views: 33,074

Rating: 4.8212004 out of 5

Keywords: query, queries, sql, data, manipulation, what, is, dml, dcl, tcl, ddl, control, transaction, transaction in dbms, transaction management in dbms, transaction management in dbms gate, transaction management in rdbms, transaction and concurrency control in dbms for gate, transaction processing system, transaction processing concept, what is transaction control language in sql, what is transaction control language, tcl in sql, tcl sql, commit, rollback, in, savepoint, tcl commands, what is tcl

Id: OSfcmmoQWgU

Length: 7min 34sec (454 seconds)

Published: Fri Jan 10 2020