System Designer
Back to Fundamentals

ACID Properties

15 min read•Intermediate
Not Started
Loading...

Master the four fundamental guarantees that databases provide for reliable transaction processing. Learn how ACID properties prevent data corruption and ensure business rule integrity.

Understanding ACID

ACID properties are the foundation of reliable database systems. They ensure that database transactions are processed reliably and maintain data integrity even in the face of errors, power failures, and concurrent access.

Why ACID Matters

Without ACID guarantees, your application could lose money, create duplicate records, violate business rules, or leave the system in an inconsistent state. These properties are essential for any application handling critical data.

A

Atomicity

All operations in a transaction succeed or all fail

Example
Bank transfer: debit $100 from Account A AND credit $100 to Account B
Without This Property
If credit fails, debit is automatically rolled back
Implementation
Transaction logs, rollback mechanisms
Business Impact
Prevents partial operations that corrupt data integrity
C

Consistency

Database transitions from one valid state to another

Example
Account balance cannot go negative (business rule)
Without This Property
Transaction rejected if it would violate constraints
Implementation
Constraints, triggers, validation rules
Business Impact
Ensures business rules are never violated
I

Isolation

Concurrent transactions do not interfere with each other

Example
Two users buying the last item see consistent inventory
Without This Property
Without isolation: both users could purchase the same item
Implementation
Locking mechanisms, MVCC
Business Impact
Prevents race conditions and data corruption
D

Durability

Committed transactions persist even after system failure

Example
Payment confirmed remains confirmed after power outage
Without This Property
Customer charged but transaction lost
Implementation
Write-ahead logging, disk persistence
Business Impact
Guarantees no data loss after confirmation

Isolation Levels Explained

Isolation isn't binary - databases offer different levels of isolation with trade-offs between consistency and performance. Choose the right level based on your application's requirements.

1

Read Uncommitted

Fastest
Dirty Reads
Possible
Non-Repeatable
Possible
Phantom Reads
Possible
Best For
Analytics on approximate data
Potential Issues: Dirty reads, Lost updates, Inconsistent data
2

Read Committed

Fast
Dirty Reads
Prevented
Non-Repeatable
Possible
Phantom Reads
Possible
Best For
Most web applications
Potential Issues: Phantom reads, Non-repeatable reads
3

Repeatable Read

Moderate
Dirty Reads
Prevented
Non-Repeatable
Prevented
Phantom Reads
Possible
Best For
Financial calculations
Potential Issues: Phantom reads only
4

Serializable

Slowest
Dirty Reads
Prevented
Non-Repeatable
Prevented
Phantom Reads
Prevented
Best For
Critical financial transactions
Potential Issues: High lock contention, Potential deadlocks

Performance vs Consistency Trade-off

Read Uncommitted
95%performance
20%consistency
Read Committed
80%performance
60%consistency
Repeatable Read
60%performance
80%consistency
Serializable
30%performance
100%consistency

Real-World ACID Scenarios

Understanding how ACID properties apply to common business scenarios helps you design robust applications.

1

E-commerce Checkout

Customer buying the last item in stock

Without ACID
Two customers could purchase the same item
ACID Solution
Use serializable isolation for inventory updates
Implementation
SELECT ... FOR UPDATE on inventory table
2

Banking Transfer

Transfer $500 from savings to checking

Without ACID
Money could be debited but not credited
ACID Solution
Wrap both operations in a single transaction
Implementation
BEGIN; UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE accounts SET balance = balance + 500 WHERE id = 2; COMMIT;
3

User Registration

Create user account with initial preferences

Without ACID
User created but preferences lost
ACID Solution
Create user and preferences in same transaction
Implementation
Use foreign key constraints and transaction boundaries
4

Order Processing

Create order, update inventory, send notification

Without ACID
Order created but inventory not updated
ACID Solution
Transaction for critical operations, async for notifications
Implementation
Transactional outbox pattern for eventual consistency

ACID Support Across Database Types

Full ACID Support

PostgreSQL
Full ACID compliance, multiple isolation levels
MySQL (InnoDB)
ACID compliant with proper engine choice
Oracle, SQL Server
Enterprise-grade ACID compliance

Partial or Configurable ACID

MongoDB
ACID for single documents, multi-document transactions available
Redis
Atomic operations, limited transaction support
Cassandra, DynamoDB
Eventually consistent, limited ACID guarantees

ACID Performance Considerations

Performance Costs

Logging Overhead
Write-ahead logs for durability
10-15%
Lock Contention
Higher isolation levels
20-50%
Validation Checks
Constraint enforcement
5-10%
Coordination Cost
Distributed transactions
30-70%

Optimization Strategies

Batch Operations
Group multiple operations into single transactions
Read Replicas
Offload read traffic to eventually consistent replicas
Appropriate Isolation
Use the lowest isolation level that meets your requirements

ACID Quick Reference

When to Use Strong ACID

  • ☐ Financial transactions (payments, transfers)
  • ☐ Inventory management (prevent overselling)
  • ☐ User account operations (registration, authentication)
  • ☐ Critical business logic (order processing)
  • ☐ Regulatory compliance (audit trails)

When You Can Relax ACID

  • ☐ Analytics and reporting (eventual consistency OK)
  • ☐ Social media posts (can be eventually consistent)
  • ☐ Search indexes (rebuild-able from source)
  • ☐ Caching layers (temporary data)
  • ☐ Logs and metrics (some loss acceptable)

🎯 ACID Failures and Fixes in Production

Learn from real incidents where ACID violations caused business problems and how they were resolved

Scenarios

Knight Capital Trading Disaster
How lack of transaction atomicity led to $440 million loss in 30 minutes
Amazon DynamoDB Consistency Issue
E-commerce inventory overselling due to eventual consistency
PayPal Double Charging Bug
Race condition in payment processing led to duplicate charges
GitHub Repository Corruption
Database failure during Git operations corrupted repository metadata
Slack Message Ordering Problem
Messages appearing out of order due to distributed transaction issues
Uber Driver Location Inconsistency
Location updates lost during high traffic, causing phantom drivers

Context

How lack of transaction atomicity led to $440 million loss in 30 minutes

Metrics

Time to Disaster
30 minutes
Total Loss
$440 million
Root Cause
Partial deployment
ACID Violation
Atomicity failure

Outcome

Partial deployment caused old code and new code to run simultaneously, creating conflicting orders. No atomic deployment process led to catastrophic system inconsistency.

Key Lessons

  • •Deployment must be atomic - all systems updated together or none at all
  • •Database and application code changes must be coordinated
  • •Rollback procedures essential for failed deployments
  • •Testing should include partial deployment scenarios

📝 ACID Properties Quiz

1 of 5Current: 0/5

A bank transfer operation debits $100 from Account A but fails to credit Account B due to a system crash. Which ACID property was violated?