Using Transactions to Maintain Data Consistency in MySQL

Use Transactions to Ensure Consistency

Write a MySQL query to demonstrate how transactions ensure consistency when updating multiple tables.

Solution:

-- Start a new transaction
START TRANSACTION;

-- Deduct 1000 from the balance of the account with AccountID = 1
-- This assumes that the account has sufficient balance.
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;

-- Update the transaction record with TransactionID = 1 to reflect the amount of 1000
-- This ensures that the transaction history is in sync with the account update.
UPDATE Transactions SET Amount = 1000 WHERE TransactionID = 1;

-- Commit the transaction to save both changes permanently
-- If no errors occur, both updates are applied together as an atomic operation.
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to ensure that updates to multiple tables are consistent.
• Key Components:
• START TRANSACTION and COMMIT: Ensure consistency.
• Why use Transactions?:
• Transactions ensure that related changes are applied together, maintaining data integrity.
• Real-World Application:
• For example, in a banking system, you might use transactions to ensure consistent updates to accounts and transactions.

For more Practice: Solve these Related Problems:

• Write a MySQL query that ensures consistent updates across multiple tables in a customer order management system.
• Write a MySQL query that updates both a student’s grade record and attendance record in a university system while ensuring consistency.
• Write a MySQL query to maintain data consistency when updating inventory stock and sales records in an e-commerce system.
• Write a MySQL query where a failure in one update operation results in a rollback of all related updates to maintain consistency.

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

Previous MySQL Exercise: Demonstrate Read Uncommitted Isolation Level.
Next MySQL Exercise: Demonstrate Serializable Isolation Level.