Implementing Savepoints in MySQL Transactions

Use Savepoints in a Transaction

Write a MySQL query to create a savepoint within a transaction and rollback to that savepoint if needed.

Solution:

-- Begin a new transaction to ensure atomicity
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.
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;

-- Create a SAVEPOINT before applying the bonus update:
-- This allows rolling back only the bonus update while keeping the salary increase intact.
SAVEPOINT before_bonus;

-- Update the bonus for all employees in DepartmentID = 2 by adding 1000
UPDATE Employees 
SET Bonus = Bonus + 1000 
WHERE DepartmentID = 2;

-- If an issue occurs with the bonus update, rollback to the SAVEPOINT
-- This undoes only the bonus update, keeping the salary increase unchanged.
ROLLBACK TO before_bonus;

-- Commit the transaction to make the salary update permanent
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how to use savepoints to partially rollback a transaction.
• Key Components:
• SAVEPOINT before_bonus: Creates a savepoint.
• ROLLBACK TO before_bonus: Rolls back to the savepoint.
• Why use Savepoints?:
• Savepoints allow you to undo part of a transaction without rolling back the entire transaction.
• Real-World Application:
• For example, in a payroll system, you might use savepoints to handle partial updates.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create multiple savepoints and rollback to different stages in a transaction.
• Write a MySQL query to use savepoints for handling partial updates in a batch operation.
• Write a MySQL query to demonstrate a scenario where savepoints improve error handling in complex transactions.
• Write a MySQL query to implement a nested transaction using savepoints.

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

Previous MySQL Exercise: Handle Deadlocks.
Next MySQL Exercise: Demonstrate Dirty Read.