Commit and Rollback in SQL

• Commit and rollback are the transaction control commands in SQL.
• All the commands that are executed consecutively, treated as a single unit of work and termed as a transaction.
• If you want to save all the commands which are executed in a transaction, then just after completing the transaction, you have to execute the commit command. This command will save all the commands which are executed on a table. All these changes made to the table will be saved to the disk permanently.
• Whenever the commit command is executed in SQL, all the updations which we have carried on the table will be uploaded to the server, and hence our work will be saved.
• The rollback command is used to get back to the previous permanent status of the table, which is saved by the commit command.
• Suppose, we have started editing a table and later thought that the changes that we have recently carried out on a table are not required. Then, in that case, we can roll back our transaction, which simply means to get back to the previous permanent status of the table, which is saved by the commit command.

Note: One thing to note about the rollback command is that if you have already committed your recent changes, you cannot rollback your transaction. In that case, you can only roll to the last permanent change.

Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.

Example 1:

Let us select the existing database dbs.

Then we will write the following query to create a table in the 'dbs' database:

Then, we will start our transaction by using the BEGIN / START TRANSACTION command.

Now, we will insert records in the student table.

We will execute the SELECT query to verify that all the records are inserted successfully in the student table.

You will get the following table as output:

We will commit our transaction to save all the changes permanently to the disk.

Now, turn off the auto-commit by setting the value of auto-commit as 0.

Then we will delete the student record whose ID is 5.

To verify the results of the delete query, we will again use the SELECT query.

Later, we thought that we needed the record which we have deleted in the earlier step, i.e., the record whose ID is 5. As we know, before deleting the record with ID 5, we have stored the entire student table, which contains five records, into the disk with the commit command.

We will execute the ROLLBACK command to get the original table that we have saved before executing the delete command.

After the rollback command, we need to execute the SELECT command to view the records of the student table.

The above results show that the student table containing five records is successfully retrieved from the disk after using the rollback command.

Now, write a query to update the record and set the percentage as 80 for the student whose ID is 1.

To verify the results of the update query, we will again use the SELECT query. Here, this update query will be applied to the table which was retrieved after the rollback command.

Now, we will again rollback our transaction and execute the select query:

We can see that all the records are retrieved as they were earlier before applying the update query.

Example 2:

Let us select the existing database dbs.

Now we will write the following query to create a table in the 'dbs' database:

Then, we will start our transaction by using the BEGIN / START TRANSACTION command.

Now, we will insert records in the employee table.

We will execute the SELECT query to verify that all the records are inserted successfully in the employee table.

We will get the following table as output:

We will commit our transaction to save all the changes permanently to the disk.

Now, turn off the auto-commit by setting the value of auto-commit as 0.

Then we will add a new record to the employee table.

To verify the results of the insert query, we will again use the SELECT query.

Later, we thought that we don't need the record which we have inserted in the earlier step, i.e., the record whose ID is 6. As we know, before inserting the record with ID 6, we have stored the entire employee table, which contains five records, into the disk with the commit command.

We will execute the ROLLBACK command to get the original table that we have saved before executing the insert command.

After the rollback command, we need to execute the SELECT command to view the records of the employee table.

We can see that all the records are retrieved as they were earlier before applying the insert query.