COMMIT and ROLLBACK in SQL

Key Features of SQL ROLLBACK

1. Reverting: Cancels all changes that have been made, but not yet committed.
2. Error Handling: Helps when you or someone makes a mistake within a transaction.
3. Data Dependability: Ensures that the database remains stable and reliable.
4. Limited: Only can undo the action thar are made after the last COMMIT command.
5. Database Support: It is used in SQL Server and other relational database systems.

Let us create a table called the Students table, which we will be using for understanding the use cases of COMMIT and ROLLBACK in SQL.

— Creating the table

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);
-- Inserting the data into the table
INSERT INTO Students (ID, Name, Age) VALUES (1, 'Rahul', 20);
INSERT INTO Students (ID, Name, Age) VALUES (2, 'Aditi', 22);
INSERT INTO Students (ID, Name, Age) VALUES (3, 'Karan', 21);
-- Commit command 
COMMIT;
-- Displaying the result
SELECT * FROM Students;

Output:

This is how the Students table looks after creating and inserting the data into it.

Use Cases of COMMIT in SQL

Let’s explore the use case of COMMIT in SQL:

1. Using COMMIT with INSERT Statement in SQL

When you want to save the newly inserted data into your database, you can use COMMIT with INSERT in SQL.

INSERT INTO Students (ID, Name, Age) VALUES (4, 'Neha', 23);
COMMIT;

Output:

Explanation: Here, this query is employed to add a new record, and thus the SQL COMMIT command permanently saves the changes that are made.

2. Using COMMIT with UPDATE Statement in SQL

This command is used to save the updated values permanently in the database.

UPDATE Students SET Age = 25 WHERE ID = 2;
COMMIT;

Output:

Explanation: Here, the record is updated with the following UPDATE command, followed by permanent saving with the help of the COMMIT command in SQL.

3. Using COMMIT with DELETE Statement in SQL

This command is used to remove the record permanently from the database.

DELETE FROM Students WHERE ID = 1;
COMMIT;

Output:

Explanation: Here, the DELETE command in SQL is used to delete the record from the table, and the COMMIT command is used to save it permanently.

4. Using COMMIT with Transactions Involving Multiple Tables in SQL

The COMMIT command is used with multiple tables to save changes across multiple tables together.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    StudentID INT,
    Amount INT
);
INSERT INTO Orders (OrderID, StudentID, Amount) VALUES (101, 3, 500);
UPDATE Students SET Age = Age + 1 WHERE ID = 3;
COMMIT;

Output:

Explanation: Here, SQL COMMIT is used with multiple tables to save the changes across multiple tables together.

Use Cases of ROLLBACK in SQL

Let’s explore the use cases of ROLLBACK in SQL:

1. Using ROLLBACK with INSERT Statement in SQL

This command is used to cancel the inserted rows that are not committed.

START TRANSACTION;
INSERT INTO Students (ID, Name, Age) VALUES (4, 'Neha', 23);
-- deleting the record from the table
ROLLBACK;   
SELECT * FROM Students;

Output:

Explanation: Here, this ROLLBACK command is used to undo the changes.

2. Using ROLLBACK with UPDATE Statement in SQL

This command is used to undo all the changes made by the UPDATE statement in SQL.

START TRANSACTION;
UPDATE Students SET Age = 30 WHERE ID = 3;
ROLLBACK;   -- Karan’s age remains the same

Output:

Explanation: Here, the ROLLBACK command is used to undo updates that are made during the current transaction.

3. Using ROLLBACK with DELETE Statement in SQL

This command is used to restore the records the changes that are not been permanently deleted.

START TRANSACTION;
DELETE FROM Students WHERE ID = 2;
ROLLBACK;   -- Aditi’s record is restored

Output:

Explanation: Here, the ROLLBACK command is used to restore the records deleted in the current transaction before COMMIT

Difference Between COMMIT and ROLLBACK in SQL

Feature	COMMIT SQL	ROLLBACK SQL
Purpose	COMMIT in SQL is used to save all changes permanently.	ROLLBACK is used to cancel the uncommitted changes.
Reversibility	It is irreversible, as once committed, it cannot be undone.	Reversible as it restores the database to the last COMMIT.
Use Case	When you are sure about the changes.	When there is an error or a wrong transaction.
Data Safety	It ensures that data is stored permanently.	It ensures data consistency by undoing mistakes.

Best Practices for Using COMMIT and ROLLBACK in SQL

1. Use COMMIT After Testing: Only use COMMIT if you’re certain the changes are accurate.
2. Keep Transactions Short: Short transactions decrease the probability of conflicts and errors.
3. ROLLBACK when errors occur: In order to keep data safe, always try to ROLLBACK when an error occurs.
4. Group Logical Operations:  Perform all the changes that are related in one transaction.
5. Always test with sample data: Before you run on actual databases, test the COMMIT and ROLLBACK commands on the sample tables to not get any errors.

Conclusion

The SQL  commands used to keep the database safe and reliable are the COMMIT and ROLLBACK commands. All the changes made in the database are done with the help of COMMIT SQL and undone with the help of ROLLBACK SQL. COMMIT and ROLLBACK are the basis of control of transactions in SQL and are to be utilized to ensure that the database is accurate, consistent, and reliable. When used correctly, COMMIT saves the successful SQL operations permanently, while ROLLBACK allows you to undo the changes and restore the database to its previous state. Understanding the timing of COMMIT and ROLLBACK applications is a key skill that any development and database specialist should possess.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with our SQL joins interview questions, prepared by industry experts. 

COMMIT and ROLLBACK in SQL – FAQs