Skip to content
Related Articles

Related Articles

Difference between COMMIT and ROLLBACK in SQL

View Discussion
Improve Article
Save Article
Like Article
  • Difficulty Level : Basic
  • Last Updated : 24 Feb, 2022

1. COMMIT-
COMMIT in SQL is a transaction control language that is used to permanently save the changes done in the transaction in tables/databases. The database cannot regain its previous state after its execution of commit. 

Example: Consider the following STAFF table with records: 

STAFF

sql> 
SELECT *
FROM Staff
WHERE Allowance = 400;

sql> COMMIT; 

Output: 

So, the SELECT statement produced the output consisting of three rows. 

2. ROLLBACK

ROLLBACK in SQL is a transactional control language that is used to undo the transactions that have not been saved in the database. The command is only been used to undo changes since the last COMMIT.

Example: Consider the following STAFF table with records: 

STAFF

sql> 
SELECT *
FROM EMPLOYEES
WHERE ALLOWANCE = 400;

sql> ROLLBACK; 

Output: 

So, the SELECT statement produced the same output with the ROLLBACK command. 

Difference between COMMIT and ROLLBACK 

 COMMITROLLBACK
1.COMMIT permanently saves the changes made by the current transaction.ROLLBACK undo the changes made by the current transaction.
2.The transaction can not undo changes after COMMIT execution.Transaction reaches its previous state after ROLLBACK.
3.When the transaction is successful, COMMIT is applied.When the transaction is aborted, incorrect execution, system failure ROLLBACK occurs.
4.COMMIT statement permanently save the state, when all the statements are executed successfully without any error.  In ROLLBACK statement if any operations fail during the completion of a transaction, it cannot permanently save the change and we can undo them using this statement.
5.

Syntax of COMMIT statement are:

COMMIT;

Syntax of ROLLBACK statement are:

ROLLBACK;

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!