What does ROLLBACK do in the below Query transaction commit ROLLBACK

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.

Inhaltsverzeichnis Show

Content: COMMIT Vs ROLLBACK
Comparison Chart
Definition of COMMIT
Definition of ROLLBACK
Key Differences Between COMMIT and ROLLBACK in SQL
Properties of Transactions
Transaction Control
Transactional Control Commands
The COMMIT Command
The ROLLBACK Command
The SAVEPOINT Command
The RELEASE SAVEPOINT Command
The SET TRANSACTION Command

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

Comparison Chart
Definition
Key Differences
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.

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.

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.

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors.

Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID.

Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
Consistency − ensures that the database properly changes states upon a successfully committed transaction.
Isolation − enables transactions to operate independently of and transparent to each other.
Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

The following commands are used to control transactions.

COMMIT − to save the changes.
ROLLBACK − to roll back the changes.
SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
SET TRANSACTION − Places a name on a transaction.

Transactional Control Commands

Transactional control commands are only used with the DML Commands such as - INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

The COMMIT Command

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

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

The syntax for the COMMIT command is as follows.

COMMIT;

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+

Following is an example which would delete those records from the table which have age = 25 and then COMMIT the changes in the database.

SQL> DELETE FROM CUSTOMERS WHERE AGE = 25; SQL> COMMIT;

Thus, two rows from the table would be deleted and the SELECT statement would produce the following result.

+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

The syntax for a ROLLBACK command is as follows −

ROLLBACK;

Example

Consider the CUSTOMERS table having the following records −

Following is an example, which would delete those records from the table which have the age = 25 and then ROLLBACK the changes in the database.

SQL> DELETE FROM CUSTOMERS WHERE AGE = 25; SQL> ROLLBACK;

Thus, the delete operation would not impact the table and the SELECT statement would produce the following result.

The SAVEPOINT Command

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

The syntax for a SAVEPOINT command is as shown below.

SAVEPOINT SAVEPOINT_NAME;

This command serves only in the creation of a SAVEPOINT among all the transactional statements. The ROLLBACK command is used to undo a group of transactions.

The syntax for rolling back to a SAVEPOINT is as shown below.

ROLLBACK TO SAVEPOINT_NAME;

Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

Example

Consider the CUSTOMERS table having the following records.

The following code block contains the series of operations.

SQL> SAVEPOINT SP1; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=1; 1 row deleted. SQL> SAVEPOINT SP2; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=2; 1 row deleted. SQL> SAVEPOINT SP3; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=3; 1 row deleted.

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone −

SQL> ROLLBACK TO SP2; Rollback complete.

Notice that only the first deletion took place since you rolled back to SP2.

SQL> SELECT * FROM CUSTOMERS; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ 6 rows selected.

The RELEASE SAVEPOINT Command

The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.

The syntax for a RELEASE SAVEPOINT command is as follows.

RELEASE SAVEPOINT SAVEPOINT_NAME;

Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.

The SET TRANSACTION Command

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write.

The syntax for a SET TRANSACTION command is as follows.

SET TRANSACTION [ READ WRITE | READ ONLY ];