Difference between COMMIT and ROLLBACK in SQL

COMMIT and ROLLBACK are the two terms used in the transactional statement to perform or undo the SQL transaction. Before going to the term COMMIT and ROLLBACK, we need to understand the term Transaction. A transaction is a logical term with some sequence of instructions or queries to make a complete transaction execution. Each transaction starts with a specific task and ends when the group of tasks is completed. If any of the tasks fails, the transaction is a failure. To make a complete transaction in SQL, we need to perform various activities such as: starts the transaction, set the transaction, commit, the ROLLBACK, and the SAVEPOINT of the transaction. Here we will discuss only two terms, COMMIT and ROLLBACK, and their differences in SQL.

What is SQL COMMIT?

A COMMIT is the SQL command used in the transaction tables or database to make the current transaction or database statement as permanent. It shows the successful completion of a transaction. If we have successfully executed the transaction statement or a simple database query, we want to make the changes permanent. We need to perform the commit command to save the changes, and these changes become permanent for all users. Furthermore, once the commit command is executed in the database, we cannot regain its previous states in which it was earlier before the execution of the first statement.

Syntax

Consider the Employees tables that containing the following records:

In the above example, we will delete all those records from the EMPLOYEES table whose age is 27 and then COMMIT query to make the changes as permanent that visible for all users in the database records.

Follow the given below SQL query:

After that, use the select command to fetch all the records from the Employees table.

Output

In the above table, when the COMMIT query is executed, it permanently saves the EMPLOYEES table changes, and these changes visible to all database users.

What is SQL ROLLBACK?

The SQL ROLLBACK command is used to roll back the current transaction state if any error occurred during the execution of a transaction. In a transaction, the error can be a system failure, power outage, incorrect execution of the transaction, system crash, etc. Generally, a rollback command performs the current transaction's rollback action to return the transaction on its previous state or the first statement. A rollback command can only be executed if the user has not performed the COMMIT command on the current transaction or statement.

Syntax

Consider the Employees tables that containing the following records:

In the above example, we will delete all those records from the EMPLOYEES table whose age is 27 and then perform the ROLLBACK query to retrieve the deleted records from the EMPLOYEES table.

Follow the given below SQL query:

TABLE - EMPLOYEES

Here, in the above table, there are two rows whose age is 27 deleted from the Employees table that satisfy the age condition. And then ROLLBACK query to undo the operations.

After that, use the select command to retrieve all the records from the Employees table.

Output

TABLE - EMPLOYEES

Difference between the COMMIT and ROLLBACK

Conclusion

The commit command ensures the transaction changes are permanently saved in the database or tables to complete a transaction. In contrast, the rollback command is used to undo all changes during the transaction for occurring any types of issues such as power failure, wrong data, or return the current transaction to its initial phase.