Commit and Rollback in SQL

In Structured Query Language, we have Data Definition Language (DDL) and Data Manipulation Language (DML) commands the same way we have Transaction Control Language (TCL) commands in the Structured Query Language.

Transaction Control Language commands in the Structured Query Language are Commit and Rollback.

Whatever commands we executed wrapped in one unit of work known as a transaction.

All the operations or commands of DDL or DML are stored or executed in a transaction. Suppose we have executed one update or delete operation on a table executed in a transaction. To save such executed DDL or DML, we have to execute the commit command of a Transaction Control Language. Commit is used to save all the operations we performed on a table, and all the operation is saved. All this is about Commit.

Now, think, what if we want to undo the operations we saved using the commit commands? Then can we undo the operation in the Structured Query Language? Yes, we can undo the committed operations; we will use another command of Transaction Control Language to undo the operations, and that command is Rollback. We will use the Rollback command to undo the commit operation in the Structured Query Language.

Let us see a few examples of Commit and Rollback commands:

Consider the existing employees tables which have the following records:

Employeeid	First_Name	Last_Name	Employee_Salary	Employee_City	Employee_Department	Gender
1001	Pranoti	Shende	48000	Kolhapur	FMW	F
1002	Vaibhav	Sharma	20000	Noida	FMW	M
1003	Nikhil	Vani	55000	Indore	Oracle	M
1004	Prachi	Sharma	55000	Noida	Oracle	F
1005	Harshada	Koli	48500	Nashik	Angular	F
1006	Sonal	Maheshwari	24000	Pune	Oracle	F
1007	Bhavesh	Jain	65000	Pune	FMW	M
1008	Kapil	Verma	50000	Nashik	Angular	M
1009	Rajesh	Goud	55000	Indore	Testing	M
1010	Deepam	Jauhari	60000	Indore	Testing	M

We can use the commit and Rollback command without starting the transactions, but we will start our transaction using Start Transaction Command for good practice.

Let’s begin and see each operation example one by one.

First, we will turn off the auto-commit by assigning a value of auto-commit to 0

SET AUTOCOMMIT = 0;

Example 1:  In this example, we will insert new values into existing table employees and then will commit

INSERT INTO EMPLOYEES VALUES(2001, ‘SURILI’, ‘JAIN’,  55000, ‘MUMBAI’,  ‘TESTING’, ‘F’);

We will use the SELECT query to check the record that is inserted successfully or not in the employee's table:

SELECT * FROM EMPLOYEES;

The output of the above query is as follows:

Employeeid	First_Name	Last_Name	Employee_Salary	Employee_City	Employee_Department	Gender
1001	Pranoti	Shende	48000	Kolhapur	FMW	F
1002	Vaibhav	Sharma	20000	Noida	FMW	M
1003	Nikhil	Vani	55000	Indore	Oracle	M
1004	Prachi	Sharma	55000	Noida	Oracle	F
1005	Harshada	Koli	48500	Nashik	Angular	F
1006	Sonal	Maheshwari	24000	Pune	Oracle	F
1007	Bhavesh	Jain	65000	Pune	FMW	M
1008	Kapil	Verma	50000	Nashik	Angular	M
1009	Rajesh	Goud	55000	Indore	Testing	M
1010	Deepam	Jauhari	60000	Indore	Testing	M
2001	Surili	Jain	55000	Mumbai	Testing	F

As we can see, the record, which we insert into the table is successfully inserted.

Now, to save this transaction, we will use the commit command.

COMMIT;

The employee id 2001 details are saved now, and it won't be Rollback until and unless we delete it using the Delete command.

Now, I want to delete the employee whose id is 2001. We will use the Delete operation to delete this employee's details

DELETE FROM EMPLOYEES WHERE EMPLOYEEID = 2001;

We will execute the Select query to verify whether the records we deleted are successfully deleted or not.

SELECT * FROM EMPLOYEES;

The output of the above query is as follows:

Employeeid	First_Name	Last_Name	Employee_Salary	Employee_City	Employee_Department	Gender
1001	Pranoti	Shende	48000	Kolhapur	FMW	F
1002	Vaibhav	Sharma	20000	Noida	FMW	M
1003	Nikhil	Vani	55000	Indore	Oracle	M
1004	Prachi	Sharma	55000	Noida	Oracle	F
1005	Harshada	Koli	48500	Nashik	Angular	F
1006	Sonal	Maheshwari	24000	Pune	Oracle	F
1007	Bhavesh	Jain	65000	Pune	FMW	M
1008	Kapil	Verma	50000	Nashik	Angular	M
1009	Rajesh	Goud	55000	Indore	Testing	M
1010	Deepam	Jauhari	60000	Indore	Testing	M

We deleted the employee whose id was 2001. Now, what if I want to undo the Delete operation committed, can we undo the delete operation? Yes, we can undo the operation committed using the Rollback command. Rollback is used to undo the transaction.

We will execute the ROLLBACK command to undo the operation.

ROLLBACK;

We will execute the Select query to view the records of the employee's table. We undo the delete operation. But how will you know whether the Delete operation is Rollback or not?

SELECT * FROM EMPLOYEES;

The output of the above query is as follows:

Employeeid	First_Name	Last_Name	Employee_Salary	Employee_City	Employee_Department	Gender
1001	Pranoti	Shende	48000	Kolhapur	FMW	F
1002	Vaibhav	Sharma	20000	Noida	FMW	M
1003	Nikhil	Vani	55000	Indore	Oracle	M
1004	Prachi	Sharma	55000	Noida	Oracle	F
1005	Harshada	Koli	48500	Nashik	Angular	F
1006	Sonal	Maheshwari	24000	Pune	Oracle	F
1007	Bhavesh	Jain	65000	Pune	FMW	M
1008	Kapil	Verma	50000	Nashik	Angular	M
1009	Rajesh	Goud	55000	Indore	Testing	M
1010	Deepam	Jauhari	60000	Indore	Testing	M
2001	Surili	Jain	55000	Mumbai	Testing	F

The above output clearly says us that we successfully undo the Delete operation using the Rollback command.

Now next operation we will see is on the Update operation. We will write a query to update the employee salary whose employee id is 1002 and set the employee salary as 48500.

UPDATE EMPLOYEES SET EMPLOYEE_SALARY = 48500 WHERE EMPLOYEEID = 1002;

Again, we will execute the Select query to check whether the employee salary is modified or not.

SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = 1002;

Employeeid	First_Name	Last_Name	Employee_Salary	Employee_City	Employee_Department	Gender
1002	Vaibhav	Sharma	48500	Noida	FMW	M

The result clearly itself says that employee salary is modified. What if I want to undo the update operation of employee id 1002? Will I need to execute an update operation on employee Id 1002 again? Rather than executing an update operation on employee id 1002 and modifying the salary, we will undo the update operation using Rollback Command.

ROLLBACK;

We undo the update operation now, will execute the Select query, and check whether it is Rollback or not.

 SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = 1002;

The output of the above query is as follows:

Employeeid	First_Name	Last_Name	Employee_Salary	Employee_City	Employee_Department	Gender
1002	Vaibhav	Sharma	20000	Noida	FMW	M

As we can see, the employee id 1002 employee salary as same earlier before executing the Update query.

Note: We can undo the Delete command and truncate command, but we cannot undo the Drop command because Drop removes all the data and Drop the table structure and Drop all the index or views related to that table.