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.

mysql> USE dbs;

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

mysql> CREATE TABLE student(ID INT PRIMARY KEY, Name VARCHAR(20), Percentage INT, Location VARCHAR(20), DateOfBirth DATE);

Commit and Rollback in SQL

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

mysql> START TRANSACTION;

Commit and Rollback in SQL

Now, we will insert records in the student table.

mysql> INSERT INTO student(ID, Name, Percentage, Location, DateOfBirth) VALUES (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 Jaiswal", 90, "Chennai", "2005-10-02"), (5, "Rahul Khanna", 92, "Ambala", "1996-03-04");

Commit and Rollback in SQL

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

mysql> SELECT *FROM student;

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 Jaiswal	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.

mysql> COMMIT;

Commit and Rollback in SQL

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

mysql> SET autocommit = 0;

Commit and Rollback in SQL

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

mysql> DELETE FROM student WHERE ID = 5;

Commit and Rollback in SQL

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

mysql> SELECT *FROM employee;

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 Jaiswal	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.

mysql> ROLLBACK;

Commit and Rollback in SQL

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

mysql> SELECT * FROM student;

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 Jaiswal	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.

mysql> UPDATE student SET Percentage = 80 WHERE ID = 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.

mysql> SELECT * FROM student;

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 Jaiswal	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:

mysql> ROLLBACK;

Commit and Rollback in SQL

mysql> SELECT * FROM student;

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 Jaiswal	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.

mysql> USE dbs;

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

mysql> CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);

Commit and Rollback in SQL

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

mysql> START TRANSACTION;

Commit and Rollback in SQL

Now, we will insert records in the employee table.

mysql> INSERT INTO employee( ID, Name, City, Salary, Age) VALUES( 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);

Commit and Rollback in SQL

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

mysql> SELECT *FROM employee;

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.

mysql> COMMIT;

Commit and Rollback in SQL

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

mysql> SET autocommit = 0;

Commit and Rollback in SQL

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

mysql> INSERT INTO employee VALUES (6, "Sneha Tiwari", "Kanpur", 38000, 38);

Commit and Rollback in SQL

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

mysql> SELECT *FROM employee;

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.

mysql> ROLLBACK;

Commit and Rollback in SQL

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

mysql> SELECT *FROM employee;

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.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

Commit and Rollback in SQL

Comment / Reply From

You May Also Like

SQL COUNT DISTINCT

What is Race Condition

Trigger in SQL

How to Use ORDER BY in SQL

How to Use GROUP BY in SQL

SQL Arithmetic Operators

SQL Languages

How to use Auto-Increment in SQL

Popular Post

PHP Tutorial

NET Framework

Python Tutorial

Language

PHP Tutorial

NET Framework

Python Tutorial

Newsletter