WikiGalaxy
ACID vs BASE in Database Design
ACID Properties:
- Atomicity: Ensures that each transaction is all or nothing, meaning that if one part of the transaction fails, the entire transaction fails.
- Consistency: Guarantees that a transaction can only bring the database from one valid state to another, maintaining database invariants.
- Isolation: Ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
- Durability: Once a transaction has been committed, it will remain so, even in the event of a system failure.
BASE Properties:
- Basically Available: The system guarantees availability, in terms of the CAP theorem, the system will always respond to requests.
- Soft state: The state of the system may change over time, even without input, due to eventual consistency.
- Eventual consistency: The system will eventually become consistent once it stops receiving input.
Bank Transaction
In a bank transaction, ACID properties are crucial to ensure that money is neither lost nor created out of thin air during transfers between accounts.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
Atomicity:
Ensures that both account updates occur, or neither does.
Consistency:
Ensures the total balance remains the same before and after the transaction.
Isolation:
Ensures concurrent transactions do not interfere with each other.
Durability:
Guarantees that once the transaction is committed, the changes persist even in the event of a crash.
Social Media Feed
Social media platforms often use BASE properties to ensure high availability and eventual consistency for user feeds.
// Fetch new posts
List newPosts = fetchNewPosts(userId);
// Display posts
displayPosts(newPosts);
Basically Available:
Ensures that the feed is always accessible, even if some data might be slightly outdated.
Soft State:
The feed can change as new posts are added or removed over time.
Eventual Consistency:
Guarantees that all users will eventually see the same set of posts.
Inventory Management
ACID properties are used in inventory systems to ensure accurate stock levels during order processing.
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;
INSERT INTO orders (product_id, quantity) VALUES (101, 1);
COMMIT;
Atomicity:
Ensures that the inventory update and order creation occur together.
Consistency:
Maintains accurate inventory levels across transactions.
Isolation:
Prevents concurrent transactions from causing inconsistencies.
Durability:
Once committed, the transaction persists, ensuring no loss of data.
E-commerce Product Recommendations
Product recommendation systems in e-commerce sites leverage BASE properties to provide personalized suggestions.
// Fetch recommendations
List recommendations = getRecommendations(userId);
// Display recommendations
displayProducts(recommendations);
Basically Available:
Recommendations are available even if some data is outdated.
Soft State:
Recommendations can change as user preferences evolve.
Eventual Consistency:
Ensures that all users will eventually receive updated recommendations.
Online Booking Systems
ACID properties are crucial in online booking systems to prevent double bookings and ensure accurate reservations.
BEGIN;
UPDATE seats SET status = 'booked' WHERE seat_id = 202;
INSERT INTO bookings (user_id, seat_id) VALUES (5, 202);
COMMIT;
Atomicity:
Ensures that seat status update and booking record creation occur together.
Consistency:
Maintains accurate booking records across transactions.
Isolation:
Prevents concurrent transactions from causing booking conflicts.
Durability:
Once committed, the transaction persists, ensuring no loss of data.
Distributed Cache Systems
BASE properties are often used in distributed cache systems to ensure high availability and performance.
// Cache retrieval
Data data = cache.get(key);
if (data == null) {
data = fetchDataFromDB(key);
cache.put(key, data);
}
Basically Available:
Ensures data is accessible even if some cache nodes are down.
Soft State:
Cache data can change as underlying database updates occur.
Eventual Consistency:
Ensures that cache data will eventually reflect database updates.
Financial Trading Systems
In financial trading systems, ACID properties ensure the integrity of transactions and prevent data anomalies.
BEGIN;
UPDATE trades SET status = 'executed' WHERE trade_id = 303;
INSERT INTO trade_history (trade_id, status) VALUES (303, 'executed');
COMMIT;
Atomicity:
Ensures trade status update and history record creation occur together.
Consistency:
Maintains accurate trade records across transactions.
Isolation:
Prevents concurrent transactions from causing trade conflicts.
Durability:
Once committed, the transaction persists, ensuring no loss of data.
Content Delivery Networks (CDN)
CDNs utilize BASE properties to ensure fast and reliable content delivery across geographically distributed servers.
// Serve content
Content content = cdn.getContent(url);
if (content == null) {
content = fetchContentFromOrigin(url);
cdn.cacheContent(url, content);
}
Basically Available:
Ensures content is available even if some CDN nodes are down.
Soft State:
Content can change as new versions are uploaded to the origin server.
Eventual Consistency:
Ensures that all CDN nodes will eventually serve the latest content.
