Key differences between COMMIT and ROLLBACK

Introduction

COMMIT and ROLLBACK are the two most common terms during transactional statements which are performed by the SQL transaction. For performing one complete SQL transaction, different transaction statements like COMMIT, SAVEPOINT, and ROLLBACK are used in that transaction. This article mainly explains the difference between COMMIT and ROLLBACK present in SQL. These functions are present in TCL ( Transaction Control Language) in SQL.

COMMIT

COMMIT is the statement that is used to save the changes that happened in the transaction permanently in the ROM memory. These changes cannot be reverted back into the database. Once the COMMIT statement is executed the database table cannot be moved into its previous state. COMMIT statement is used only when the transaction is completely successful.

For a better understanding of the COMMIT statement in SQL, let’s look at the below example:

Consider the above database table and fetch the details of the Employee whose Salary is >1000000.

Syntax of COMMIT statement in SQL is as follows:

The query to be written to obtain the details of Employees whose Salary>1000000 is as follows:

The output obtained is as follows:

ROLLBACK

The ROLLBACK statement is also from TCL ( Transaction Control Language ) in SQL. This statement helps the user to undo the changes before they are updated in the ROM memory(database). This statement can be used when there is a failure in the currently executing transaction and we can roll back all the changes that happened until that point and start the transaction freshly again.

For a better understanding of the ROLLBACK statement in SQL, let’s look at the below example:

Consider the above database table and fetch the details of the Employee whose Salary is>1000000.

Syntax of the ROLLBACK statement in SQL is as follows:

The query to be written to obtain the details of Employees whose Salary>1000000 is as follows:

The output obtained is as follows:

Differences Between COMMIT and ROLLBACK

The below table clearly explains the main differences between COMMIT and ROLLBACK statements in SQL

COMMIT	ROLLBACK
COMMIT statement in SQL permanently changes in the database table when executed.	ROLLBACK statement in SQL helps the users to undo the changes before they’re changed in the database table.
COMMIT statement cannot be reverted back and doesn't get its previous state back.	The ROLLBACK statement can be reverted and can undo the changes and get back to its previous state.
COMMIT statement is executed when the transaction is completely performed. It indicates that the transaction is completed.	ROLLBACK is executed when a failure occurred during the transaction. This indicates that the transaction has to be started again by reverting all the previous changes.
Syntax in SQL: COMMIT;	Syntax in SQL: ROLLBACK;

Conclusion

This article mainly explained the differences between COMMIT and ROLLBACK present in SQL and used during transactions. Once the COMMIT statement is used, all the changes in the database are considered to be permanent whereas comparatively, ROLLBACK is executed when a failure has occurred during the transaction and the transaction has to be started again from the beginning.