Home » Commit and Rollback in SQL

Commit and Rollback in SQL

by Online Tutorials Library

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:

Commit and Rollback in SQL

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

Commit and Rollback in SQL

Now, we will insert records in the student table.

Commit and Rollback in SQL

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:

ID Name Percentage Location DateOfBirth
1 Manthan Koli 79 Delhi 2003-08-20
2 Dev Dixit 75 Pune 1999-06-17
3 Aakash Deshmukh 87 Mumbai 1997-09-12
4 Aaryan aspire 90 Chennai 2005-10-02
5 Rahul Khanna 92 Ambala 1996-03-04

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

Commit and Rollback in SQL

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

Commit and Rollback in SQL

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

Commit and Rollback in SQL

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

ID Name Percentage Location DateOfBirth
1 Manthan Koli 79 Delhi 2003-08-20
2 Dev Dixit 75 Pune 1999-06-17
3 Aakash Deshmukh 87 Mumbai 1997-09-12
4 Aaryan aspire 90 Chennai 2005-10-02

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.

Commit and Rollback in SQL

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

ID Name Percentage Location DateOfBirth
1 Manthan Koli 79 Delhi 2003-08-20
2 Dev Dixit 75 Pune 1999-06-17
3 Aakash Deshmukh 87 Mumbai 1997-09-12
4 Aaryan aspire 90 Chennai 2005-10-02
5 Rahul Khanna 92 Ambala 1996-03-04

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.

Commit and Rollback in SQL

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.

ID Name Percentage Location DateOfBirth
1 Manthan Koli 80 Delhi 2003-08-20
2 Dev Dixit 75 Pune 1999-06-17
3 Aakash Deshmukh 87 Mumbai 1997-09-12
4 Aaryan aspire 90 Chennai 2005-10-02
5 Rahul Khanna 92 Ambala 1996-03-04

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

Commit and Rollback in SQL

ID Name Percentage Location DateOfBirth
1 Manthan Koli 79 Delhi 2003-08-20
2 Dev Dixit 75 Pune 1999-06-17
3 Aakash Deshmukh 87 Mumbai 1997-09-12
4 Aaryan aspire 90 Chennai 2005-10-02
5 Rahul Khanna 92 Ambala 1996-03-04

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:

Commit and Rollback in SQL

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

Commit and Rollback in SQL

Now, we will insert records in the employee table.

Commit and Rollback in SQL

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:

ID Name City Salary Age
1 Priyanka Bagul Nasik 26000 20
2 Riya Sharma Mumbai 72000 28
3 Neha Verma Varanasi 37000 19
4 Neeta Desai Nasik 39500 21
5 Priya Wagh Udaipur 60000 32

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

Commit and Rollback in SQL

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

Commit and Rollback in SQL

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

Commit and Rollback in SQL

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

ID Name City Salary Age
1 Priyanka Bagul Nasik 26000 20
2 Riya Sharma Mumbai 72000 28
3 Neha Verma Varanasi 37000 19
4 Neeta Desai Nasik 39500 21
5 Priya Wagh Udaipur 60000 32
6 Sneha Tiwari Kanpur 38000 38

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.

Commit and Rollback in SQL

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

ID Name City Salary Age
1 Priyanka Bagul Nasik 26000 20
2 Riya Sharma Mumbai 72000 28
3 Neha Verma Varanasi 37000 19
4 Neeta Desai Nasik 39500 21
5 Priya Wagh Udaipur 60000 32

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


Next TopicSQL Concatenate

You may also like