Using Transactions to Maintain Atomicity in MySQL

Use Transactions to Ensure Atomicity

Write a MySQL query to demonstrate how transactions ensure atomicity when transferring funds between two accounts.

Solution:

-- Start a new transaction
START TRANSACTION;

-- Deduct 1000 from Account 1
-- This step decreases the balance of Account 1 by 1000.
-- If the account balance is insufficient and no checks are in place, it could result in an overdraft.
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;

-- Add 1000 to Account 2
-- This step increases the balance of Account 2 by 1000.
-- If the previous update fails (e.g., due to insufficient funds in Account 1), this should not be executed unless properly handled.
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;

-- Commit the transaction to make changes permanent
-- If all updates succeed, the transaction is committed, and both updates become permanent.
-- If any error occurs before the commit, changes should be rolled back to maintain consistency.
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to ensure that both updates (debit and credit) are executed atomically.
• Key Components:
• START TRANSACTION and COMMIT: Ensure atomicity.
• Why use Transactions?:
• Transactions ensure that either all operations succeed or none do, maintaining data integrity.
• Real-World Application:
• For example, in a banking system, you might use transactions to ensure accurate fund transfers.

For more Practice: Solve these Related Problems:

• Write a MySQL query that ensures atomicity when updating a customer’s loyalty points and order status after a purchase.
• Write a MySQL query that demonstrates atomicity by performing multiple operations on a healthcare patient record within a single transaction.
• Write a MySQL query where a transaction updates multiple dependent records in a library database while ensuring atomicity.
• Write a MySQL query that ensures atomicity in a payroll processing system where salaries are deducted and tax payments are processed simultaneously.

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

Previous MySQL Exercise: Prevent Lost Updates with FOR UPDATE.
Next MySQL Exercise: Rollback on Constraint Violation.