×

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:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM

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:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM
2001SuriliJain55000MumbaiTestingF
Commit and Rollback in SQL

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;
Commit and Rollback in SQL

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:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM
Commit and Rollback in SQL

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;
Commit and Rollback in SQL

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:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM
2001SuriliJain55000MumbaiTestingF
Commit and Rollback in SQL

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;
EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1002VaibhavSharma48500NoidaFMWM
Commit and Rollback in SQL

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;

Commit and Rollback in SQL

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:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1002VaibhavSharma20000NoidaFMWM
Commit and Rollback in SQL

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.


Related Topics

SQL Between Operator

SQL Between operator is a logical operator in the Structured Query Language. The Between operator is used to retrieve data within the range specified in the condition in the query. The...

7 minutes read.

Where vs Having

Difference Between Where and Having The WHERE and HAVING clauses in SQL are used to filter records stored in tables in the databases. The dissimilarities between the WHERE and HAVING clause...

3 minutes read.

SQL Arithmetic Operators

This page contains all the information, learn about SQL Arithmetic Operators concept in the SQL table with the help of examples. The Arithmetic Operators is used to perform mathematical calculations on...

5 minutes read.

SQL Aggregate Operators

In SQL, the aggregate operators perform a calculation on multiple rows or multiple values of a single column and give the output a single value. Here, multiple rows of data are...

4 minutes read.

Trigger in SQL

In this article, we will learn about the concept of trigger in SQL and its implementation with the help of an example. A Trigger in Structured Query Language is a set...

4 minutes read.

SQL CROSS Join

In this tutorial, we will help you understand the concept of the SQL CROSS Join clause with the few examples. The SQL CROSS Join query is used to join one or...

5 minutes read.

How to delete a row in SQL

Introduction To delete or remove the unused/unwanted rows from a table, DELETE command is used in SQL.DELETE command removes the entire record from a table.The DELETE statement can delete one or...

6 minutes read.

SQL CREATE TABLE

In SQL tutorial, we learned and created different databases. To stores data in databases, we need to create a table. To create the table, we need to use CREATE TABLE...

5 minutes read.

SQL SELECT IN

SQL SELECT IN is a logical operator in Structured Query Language. It is used in SQL queries to reduce the use of multiple 'OR' operators. s The IN operator in SQL...

6 minutes read.

SQL Topological Sorting

It is for Directed Acyclic Graph, which linearly describes the ordering of graphs. It is not possible for all graphs, it is possible for only Directed acyclic graphs. Applications of Topological...

3 minutes read.

SQL Data Manipulation Language

Data Manipulation Language manipulates/make changes in data present in a table. It only affects data/records of table, not on the schema/structure of table. INSERT, UPDATE, DELETE are the commands of DML. INSERT: Stores...

2 minutes read.

SQL SET Keyword

This article will provide you a good understanding of the Set keyword in Structured Query Language. What is the SET keyword? The SET keyword is used to specify values for the variables....

3 minutes read.

SQL SELECT AVG

In this tutorial, we will learn about the aggregate function name avg() function concept in SQL with the help of examples. The AVG() function is one of the aggregate functions in...

3 minutes read.

How to use the BETWEEN operator in SQL

In this entire SQL article, we will understand and learn about the BETWEEN operator concept and how to use it in SQL. What is the BETWEEN operator in SQL? The Between operator...

4 minutes read.

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

4 minutes read.

SQL JOIN

As the name says, joins mean to merge something or to combine something. But in the case of SQL, joins mean to merge or combine two different tables. The Join clause...

12 minutes read.

How to use GROUP BY clause in SQL

In this SQL article, we will learn about the GROUP BY clause and how to use it in SQL. We will also discuss using the GROUP BY clause with the...

6 minutes read.

SQL Comparision Operator

The Comparison Operator compares different data of the Structured Query Language table and checks whether the data are the same, less than, greater than, less than, or greater than equal....

14 minutes read.

SQL Create Database

This tutorial will help us understand how to CREATE a Database in SQL with a few examples. The first step for storing structured records in the database is creating the databases. We...

4 minutes read.

SQL GROUP BY CLAUSE

The GROUP BY clause is used to arrange similar records into the groups in the Structured Query Language queries. The records are arranged with the help of functions which is...

4 minutes read.