Difference Between COMMIT and ROLLBACK in SQL

COMMIT and ROLLBACK, are the two transactional statements that are used to, do or undo the transactions. A transaction can have a sequence of queries, or it may have the update statements that modifies the database. The fundamental difference between COMMIT and ROLLBACK lies in their working. If the transaction is successfully executed then, the COMMIT statement permits the modification made by the transaction in the database to become permanent.

On the other hands, if the transaction due to some reason does execute successfully then the ROLLBACK  statement undoes all the updates, right from the first statement of the current transaction.

Let us discuss the difference between Commit and ROLLBACK  statements in SQL with the help of comparison chart shown below.

Content: COMMIT Vs ROLLBACK

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

Comparison Chart

Basis for Comparison	COMMIT	ROLLBACK
Basic	COMMIT validates the modifications made by the current transaction.	ROLLBACK erases the modifications made by the current transaction.
Effect	After execution of COMMIT statement, the transaction can not be ROLLBACK.	Once ROLLBACK is executed database reaches its previous state, i.e. before the execution of the first statement of the transaction.
Occurrence	COMMIT occurs when the transaction gets executed successfully.	ROLLBACK occurs when the transaction is aborted in middle of the execution.
Syntax	COMMIT;	ROLLBACK;

Definition of  COMMIT

COMMIT is an SQL statement, that signal the successful completion of a transaction. Whenever a transaction completes its execution without any interrupt, the modifications made to the database, by the transaction becomes permanent. Which means that the database can not regain its previous states in which it was, before the execution of the first statement, of the transaction.

The syntax of COMMIT statement is as  follow:

COMMIT;

As the last statement of the transaction ends the transaction becomes partially committed. Next, the recovery protocols ensure, that even a system failure, would not unable the database, to make the modifications permanent. As soon as this is checked, the commit point of the transaction has reached and finally the transaction enters into a committed state.

Once the transaction enters into a committed state, it can not be rollbacked, and a new transaction begins.

Definition of ROLLBACK

Like COMMIT, ROLLBACK is also an SQL statement, and it signals that the transaction has not been completed successfully. Hence, the transaction is aborted to undo the changes done by the transaction. After the execution of ROLLBACK,  no modifications, done by the current transaction retains.

The syntax of ROLLBACK is as follow:

ROLLBACK ;

Transaction ROLLBACK becomes necessary if an error occurs during the execution of a transaction. The error can be the system failure, power outage, error in transaction statements, system crash. In the case of power failure or system crash, the ROLLBACK  occurs when the system restarts again. ROLLBACK  can occur only if COMMIT is not yet executed.

Conclusion

To ensure, that the changes made by the transaction are permanently saved in the database, use COMMIT after the transaction’s successful completion. In case the transaction faces any error while execution then to undo the changes done by the transaction, ROLLBACK  is used.