Using MySQL ROLLBACK to Handle Transaction Errors

Rollback a Transaction on Error

Write a MySQL query to start a transaction, attempt to update a column, and rollback the changes if an error occurs.

Solution:

-- Begin a new transaction to ensure that all operations are treated as a single unit
START TRANSACTION;

-- Update the salary of all employees in DepartmentID = 2 by increasing it by 10%:
-- Salary is multiplied by 1.1 to apply a 10% raise.
-- The WHERE clause ensures that only employees in Department 2 are affected.
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;

-- Simulate an error (this could be a logical issue, constraint failure, or manual testing)
-- Instead of committing the changes, we roll back to undo the modifications.
ROLLBACK;

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how to undo changes if an error occurs during a transaction.
• This shows the use of ROLLBACK.
• Key Components:
• START TRANSACTION: Begins a new transaction.
• UPDATE Employees: Attempts to update salaries.
• ROLLBACK: Undoes the changes if an error occurs.
• Why use Transactions?:
• Rollback ensures that incomplete or erroneous changes are not applied to the database.
• Real-World Application:
• For example, in an e-commerce system, you might rollback a transaction if inventory updates fail during an order placement.

For more Practice: Solve these Related Problems:

• Write a MySQL query to update a column and rollback the changes if the new value exceeds a certain limit.
• Write a MySQL query to delete a record and rollback if a foreign key constraint violation occurs.
• Write a MySQL query to insert a record into two tables within a transaction and rollback if the second insertion fails.
• Write a MySQL query to attempt a division operation and rollback if a division by zero error occurs.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Start a Transaction and Commit Changes.
Next MySQL Exercise: Set Isolation Level to Read Committed.