SQL COMMIT Vs ROLLBACK Statement

The two phrases used in the transactional statement to complete or undo the SQL transaction are COMMIT and ROLLBACK.

We must first grasp the term Transaction before moving on to the terms COMMIT and ROLLBACK.

A transaction is a logical phrase that refers to a set of queries that must be executed in order to finish the transaction.

Each transaction begins with a single task and concludes with the completion of the entire group of tasks.

The transactionis a failure if any of the tasks fails.

To make a complete transaction in SQL, we must accomplish several tasks, including starting the transaction, setting the transaction, committing the transaction, rolling back the transaction, and saving the transaction's savepoint.

Only two terms, COMMIT and ROLLBACK , will be discussed in this article, as well as their differences in SQL.

SQL COMMIT Statement

The commit statement permanently saves the modifications made by the current transaction.

Update, Remove, and Insert statements may be included in a transaction.

Since the last commit or the START TRANSACTION statement, the COMMIT statement stores all changes made in the current transaction.

You can also use the END TRANSACTION command to save the modifications.

The transactional command COMMIT is used to save changes made by a transaction to the database.

Since the last COMMIT or ROLLBACK command, the COMMIT command saves all transactions to the database.

SQL ROLLBACK Statement

The ROLLBACK statement cancels a transaction's changes by rolling back the current transaction.

The ROLLBACK statement undoes all changes made by the current transaction, i.e., all modifications are undone until the last commit or the START TRANSACTION statement is issued.

Example 1:Consider the following records in the Employee table:

ID	Name	Dept	Salary
1	Ranjani Mai	Database	16500
2	Siva Kumar	Security	27500
3	Devi Mai	Database	39000
4	Nirmala	Administration	14000

Now, let's start a transaction and delete records from the table having Dept=Datase and finally we use ROLLBACK command to undo all the changes.

--sqlserver,Mysql

DELETE FROM Employee WHERE Dept=Database;
ROLLBACK;

If you look at the Employee table, you'll notice that the record dept in databae are still present:

Output: The rollbace output will be

ID	Name	Dept	Salary
1	Ranjani Mai	Database	16500
2	Siva Kumar	Security	27500
3	Devi Mai	Database	39000
4	Nirmala	Administration	14000

SQL SQL Difference Between COMMIT & ROLLBACK Transaction Statement

There are 5 main distinction linking in commit & rollback Transactions:-

COMMIT	ROLLBACK
The COMMIT statement is used to permanently store the modifications made to the current transaction.	A Rollback statement is used to reverse all changes made to the current transaction.
The current transaction cannot be undone once it has been fully executed with the COMMIT command.	The ROLLBACK command can be used to return the current transaction to its former state once it has been successfully executed.
Syntax of Statement - Commit;	Syntax of Statement- Rollback;
When the transaction is finished, the COMMIT statement is used.	The Rollback statement is used when a transaction is aborted, there is a power outage, or there is an improper execution of a system failure.
Because all of the statements are successfully run with no errors, the COMMIT statement will store the state permanently.	If any operations fail during the transaction's completion, it means that all of the modifications were not completed correctly, and we can undo them with the ROLLBACK statement.
The current transaction statement becomes permanent and available to all users when we use the commit command.	All users can see the rollback command, even if the current transaction contains incorrect or correct information.

All Tutorials