ACID Transactions in System Design

ACID Transactions in System Design

ACID is an acronym representing Atomicity, Consistency, Isolation, and Durability—the core properties that ensure reliable transaction processing in databases. These properties are critical in maintaining data integrity, especially in multi-user or distributed environments.

1. Atomicity

• Definition: A transaction is an indivisible unit of work that is either fully completed or fully rolled back.
• Purpose: Ensures partial transactions do not leave the database in an inconsistent state.
• Example:
  • A money transfer between two accounts involves two operations:
    1. Debiting account A.
    2. Crediting account B.
  • If one operation fails, the entire transaction is rolled back.

2. Consistency

• Definition: A transaction must bring the database from one valid state to another, maintaining defined rules, constraints, and integrity.
• Purpose: Ensures that the database adheres to all data constraints and relationships before and after the transaction.
• Example:
  • If a bank’s rule is that account balances cannot go negative, a transaction violating this rule is aborted.

3. Isolation

• Definition: Transactions operate independently without interfering with one another, even when executed concurrently.
• Purpose: Prevents unintended interaction between concurrent transactions.
• Example:
  • Two transactions updating the same account balance:
    • Transaction 1 adds $100.
    • Transaction 2 deducts $50.
  • Isolation ensures one transaction’s changes are not visible to the other until completion.

Isolation Levels:

• Read Uncommitted: Least restrictive; allows dirty reads.
• Read Committed: Prevents dirty reads but allows non-repeatable reads.
• Repeatable Read: Ensures consistent reads for the same data in a transaction.
• Serializable: Most restrictive; ensures complete isolation.

4. Durability

• Definition: Once a transaction is committed, its changes are permanently recorded, even in the event of a system failure.
• Purpose: Protects data from loss due to crashes or power outages.
• Example:
  • After completing a transaction to update a database record, the system guarantees the change persists even after a reboot.

ACID in Distributed Systems

In distributed systems, ensuring ACID compliance can be challenging due to:

1. Network Latency.
2. Node Failures.
3. Data Partitioning.

Techniques used:

• Two-Phase Commit (2PC): Coordinates transaction commit across multiple nodes.
• Distributed Consensus Algorithms: E.g., Raft or Paxos, to ensure agreement on transaction states.

Advantages of ACID Transactions

1. Ensures data integrity in multi-user environments.
2. Guarantees reliability for critical applications like banking and e-commerce.
3. Provides predictable behavior under concurrent access.

Limitations of ACID

1. Performance Overhead: Ensuring ACID properties can slow down transaction processing.
2. Scalability Issues: Strict ACID compliance can be challenging in distributed databases.
3. Complexity: Implementing ACID in distributed systems requires sophisticated protocols.

Alternatives to ACID: BASE

In highly distributed or scalable systems (like NoSQL databases), BASE is often used:

• Basically Available
• Soft state
• Eventual consistency

BASE trades strict consistency for high availability and scalability, which is essential in systems like social media or streaming platforms.

Applications of ACID

1. Financial Systems: Banking, stock trading.
2. E-commerce Platforms: Order processing.
3. Healthcare Systems: Patient records.

Example of ACID Transactions

Scenario: Online Shopping Payment Process

Imagine you're purchasing an item from an e-commerce website. The process involves multiple operations, such as deducting money from your account and updating the inventory.

Step-by-Step Explanation

1. Atomicity

• Action:
  • Deduct $500 from your wallet or credit card.
  • Decrease the inventory count of the purchased item.
• Requirement:
  • If either operation fails (e.g., payment is successful, but inventory update fails), the entire transaction is rolled back.
  • Result: Your wallet is not charged, and the inventory remains unchanged.

2. Consistency

• Action:
  • The system enforces rules, such as ensuring that an item cannot be purchased if it's out of stock.
  • The transaction checks if the inventory count is greater than 0 before proceeding.
• Requirement:
  • If the stock is insufficient, the transaction fails, keeping the database consistent with business rules.
  • Result: No money is deducted, and inventory remains consistent.

3. Isolation

• Action:
  • Multiple users might try to purchase the same item simultaneously.
  • If user A is buying the last unit, their transaction locks the inventory row until completion.
• Requirement:
  • User B must wait until user A's transaction is committed or rolled back before proceeding.
  • This prevents both users from "purchasing" the last item simultaneously.
  • Result: User A successfully buys the item, and user B sees the updated inventory.

more in isolation with Example - link

4. Durability

• Action:
  • Once the transaction commits (payment deducted, inventory updated), the system ensures that these changes are saved to permanent storage.
• Requirement:
  • Even if the server crashes immediately after committing, the transaction’s effects persist.
  • Result: Your payment and inventory changes are not lost.

Database-Level Implementation

BEGIN TRANSACTION;

-- Deduct $500 from user's account
UPDATE Wallets
SET Balance = Balance - 500
WHERE UserId = 123;

-- Decrease inventory count for the purchased item
UPDATE Inventory
SET Stock = Stock - 1
WHERE ItemId = 456;

-- Check constraints to ensure no violations
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'Transaction Failed';
END
ELSE
BEGIN
    COMMIT TRANSACTION;
    PRINT 'Transaction Successful';
END;

Failure Handling

• Case 1: If the wallet update succeeds but inventory update fails → The ROLLBACK ensures that the wallet balance returns to its original state.
• Case 2: If both operations succeed → The COMMIT ensures durability.

Implementation of ACID transaction in Database - link