Using Transactions to Guarantee Durability in MySQL

Use Transactions to Ensure Durability

Write a MySQL query to demonstrate how transactions ensure durability by committing changes.

Solution:

-- Start a new transaction
START TRANSACTION;

-- Update the salary of all employees in DepartmentID = 2
-- The salary is increased by 10% (multiplied by 1.1)
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;

-- Commit the transaction to permanently save the changes
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how committed changes are durable and survive system failures.
• Key Components:
• COMMIT: Ensures durability.
• Why use Transactions?:
• Transactions ensure that committed changes are permanently saved, even in the event of a crash.
• Real-World Application:
• For example, in a payroll system, you might use transactions to ensure salary updates are permanent.

For more Practice: Solve these Related Problems:

• Write a MySQL query that ensures salary changes persist even after a database crash using transaction durability.
• Write a MySQL query to update customer addresses in a retail database while ensuring the changes are permanent.
• Write a MySQL query where an online payment transaction is committed to the database, guaranteeing durability.
• Write a MySQL query that ensures transaction logs are properly maintained so that committed changes can be recovered after a failure.

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

Previous MySQL Exercise: Demonstrate Serializable Isolation Level.