Day 7: Transactions and ACID Properties
Date: January 25, 2025
Today, we focus on Transactions and ACID Properties, essential concepts for ensuring data consistency and reliability in database operations. Transactions are the backbone of critical workflows, from e-commerce order placements to banking applications. By understanding how to implement and manage transactions, we can maintain data integrity, even in the face of system failures or errors.
Concepts
- Importance of Transactions
A transaction is a sequence of one or more SQL operations performed as a single logical unit of work. Transactions ensure that either all operations succeed or none of them take effect, maintaining data integrity.
Example Scenarios:
Placing an order (deducting stock, updating order status, and processing payment).
Transferring money between bank accounts.
- ACID Properties
The ACID properties ensure reliability and consistency in transactions:
- Atomicity:
Ensures that all operations in a transaction are completed successfully.
If any operation fails, the transaction is rolled back, leaving the database unchanged.
- Consistency:
Ensures the database remains in a valid state before and after the transaction.
All integrity constraints must be preserved.
- Isolation:
Ensures that concurrent transactions do not interfere with each other.
One transaction's operations remain invisible to others until complete.
- Durability:
Ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure.
Practice
Example: Order Placement Workflow
Scenario:
A user places an order. The following actions need to occur as part of the transaction:
Deduct stock from the inventory table.
Update the order status in the orders table.
Insert a record in the transactions table for tracking.
Steps:
Begin the Transaction:
Use START TRANSACTION to initiate the process.Execute SQL Operations:
Perform all related SQL statements within the transaction.Handle Errors with ROLLBACK and COMMIT:
Use ROLLBACK to undo changes if an error occurs.
Use COMMIT to save changes when all operations succeed.
SQL Code Example
START TRANSACTION;
-- Step 1: Deduct stock from inventory
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 1;
-- Step 2: Update order status
UPDATE orders
SET status = 'Processed'
WHERE order_id = 101;
-- Step 3: Insert record in transactions table
INSERT INTO transactions (order_id, transaction_date, amount)
VALUES (101, NOW(), 250);
-- If all steps succeed, commit the transaction
COMMIT;
Error Handling Example:
If an error occurs at any step, execute ROLLBACK:
START TRANSACTION;
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 1;
-- Simulate an error (e.g., foreign key constraint violation)
UPDATE orders
SET status = 'Processed'
WHERE order_id = 999; -- Non-existent order_id
-- If an error occurs, rollback changes
ROLLBACK;
Real-World Example: Banking Application
Scenario: Money Transfer Between Two Accounts
Deduct the transfer amount from the sender's account.
Add the transfer amount to the recipient's account.
SQL Code Example:
START TRANSACTION;
-- Step 1: Deduct from sender's account
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
-- Step 2: Add to recipient's account
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
-- Step 3: Log the transaction
INSERT INTO transactions (sender_id, recipient_id, amount, transaction_date)
VALUES (1, 2, 500, NOW());
-- Commit the transaction
COMMIT;
Rollback on Failure Example:
START TRANSACTION;
-- Deduct from sender
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
-- Simulate an error in recipient update
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 999; -- Non-existent account_id
-- Rollback changes on error
ROLLBACK;
Benefits of Using Transactions
Data Integrity:
Ensures consistent and valid data states.Error Recovery:
Easily recover from errors using ROLLBACK.Concurrency Management:
Transactions prevent conflicts in multi-user environments.Automation:
Simplifies managing complex workflows.
Interview Preparation
What is a transaction, and why is it important?
Explain the ACID properties of a transaction.
How would you implement a money transfer between two accounts using SQL transactions?
What happens if a transaction is not committed or rolled back?
What is the difference between COMMIT and ROLLBACK?
Outcome for the Day
By the end of Day 7, you should:
Understand the purpose and implementation of transactions in SQL.
Master the ACID properties and their significance.
Write transaction-based workflows for real-world use cases like order placement or money transfers.
In Day 8, we’ll explore Database Relationships and Constraints to build robust and scalable database designs. Ready to dive deeper?
Top comments (0)