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.
Go to:
PREV : Demonstrate Serializable Isolation Level.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.