Open In App

Difference between COMMIT and ROLLBACK in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

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 

  COMMIT ROLLBACK
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;


Last Updated : 24 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads