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
|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.|
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:
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:
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.
Key Differences Between COMMIT and ROLLBACK in SQL
- The main difference between the COMMIT and ROLLBACK statements of SQL is that the execution of COMMIT statement makes all the modification made by the current transaction become permanent. On the other hands, the execution of ROLLBACK erases all the modification made by the current transaction.
- Once COMMIT statement has executed the modification made by the transaction can not be ROLLBACK. However, once the ROLLBACK statement is executed the database reaches its previous state.
- COMMIT gets executed on the successful execution of the transaction statements. However, the ROLLBACK is executed when the transaction does not get executed successfully.
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.
Can you kindly provide the practical example of Commit & Rollback