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.

COMMIT vs ROLLBACK 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:

EMPID EMP NAME AGE ADDRESS SALARY
101 JOHN 32 CALIFORNIA 5000$
102 DANIEL 38 LOS ANGELES 2900$
103 MARIA 27 TEXAS 4900$
104 JOY 39 FLORIDA 3200$
105 ROOT 28 SAN FRANCISCO 2800$
106 MARCUS 27 CALIFORNIA 1000$

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

EMPID EMP NAME AGE ADDRESS SALARY
101 JOHN 32 CALIFORNIA 5000$
102 DANIEL 38 LOS ANGELES 2900$
104 JOY 39 FLORIDA 3200$
105 ROOT 28 SAN FRANCISCO 2800$

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:

EMPID EMP NAME AGE ADDRESS SALARY
101 JOHN 32 CALIFORNIA 5000$
102 DANIEL 38 LOS ANGELES 2900$
103 MARIA 27 TEXAS 4900$
104 JOY 39 FLORIDA 3200$
105 ROOT 28 SAN FRANCISCO 2800$
106 MARCUS 27 CALIFORNIA 1000$

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

EMPID EMP NAME AGE ADDRESS SALARY
101 JOHN 32 CALIFORNIA 5000$
102 DANIEL 38 LOS ANGELES 2900$
104 JOY 39 FLORIDA 3200$
105 ROOT 28 SAN FRANCISCO 2800$

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

EMPID EMP NAME AGE ADDRESS SALARY
101 JOHN 32 CALIFORNIA 5000$
102 DANIEL 38 LOS ANGELES 2900$
103 MARIA 27 TEXAS 4900$
104 JOY 39 FLORIDA 3200$
105 ROOT 28 SAN FRANCISCO 2800$
106 MARCUS 27 CALIFORNIA 1000$

Difference between the COMMIT and ROLLBACK

Comparison Based on Parameters COMMIT Statement ROLLBACK Statement
Definition/ Basic A COMMIT statement is used to save the changes on the current transaction is permanent. A Rollback statement is used to undo all the changes made on the current transaction.
Transaction condition Once the current transaction is completely executed using the COMMIT command, it can't undo its previous state. Whereas in the Rollback statement, once the current transaction is successfully executed, it can reach its previous state using the ROLLBACK command.
Syntax of Statement Commit; Rollback;
Occurrence The COMMIT statement is applied when the transaction is completed. The Rollback statement occurs when the transaction is either aborted, power failure, or incorrect execution of system failure.
Successfully executed the statement. If all the statements are executed successfully without any error, the COMMIT statement will permanently save the state. If any operations fail during the completion of a transaction, it shows all the changes have not been successfully executed, and we can undo them using the ROLLBACK statement.
Visible change When we perform the commit command, the current transaction statement becomes permanent and visible to all users. Whereas the rollback command is also visible to all users, even the current transaction may contain the wrong or right information.

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.


Next TopicDDL vs DML




Contact US

Email:[email protected]

COMMIT vs ROLLBACK in SQL
10/30