System DesignACID Properties

ACID Properties: The Pillars of Transactional Integrity

LevelIntermediate

Duration75 mins

TopicACID Properties

5 / 5

ACID in Practice

From Theory to Production

We've explored each ACID property in depth—Atomicity's all-or-nothing guarantee, Consistency's invariant preservation, Isolation's concurrency protection, and Durability's permanence promise. Now it's time to bring them together.

In production systems, ACID properties don't operate in isolation. They form an integrated system of guarantees that work together to ensure database reliability. Understanding how they interplay—and when you might intentionally relax them—is the mark of a mature systems engineer.

This page synthesizes everything into practical guidance: how ACID manifests in real systems, when strong ACID matters most, when relaxations are acceptable, and how to make informed trade-offs in system design.

What You Will Learn

This page covers: the complete ACID transaction lifecycle, how the four properties reinforce each other, real-world scenarios requiring strong ACID, scenarios where ACID relaxation is acceptable, the performance costs of full ACID compliance, practical patterns for working with transactions, and a decision framework for choosing appropriate guarantees.

The Complete ACID Transaction Lifecycle

Let's trace a complete transaction through a database, showing how each ACID property contributes at different phases.

Scenario: Bank Transfer

Alice transfers $500 to Bob. This involves debiting Alice's account and crediting Bob's account—a classic two-operation transaction.

ACID Transaction Lifecycle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
┌────────────────────────────────────────────────────────────────────────┐
│                    PHASE 1: BEGIN TRANSACTION                          │
├────────────────────────────────────────────────────────────────────────┤
│ • Transaction assigned a unique ID (e.g., Tx-12345)                    │
│ • ISOLATION: Snapshot taken (for SI/SSI); locks may be acquired        │
│ • ATOMICITY: Transaction registered; WAL will track its operations     │
└────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌────────────────────────────────────────────────────────────────────────┐
│                    PHASE 2: EXECUTE OPERATIONS                         │
├────────────────────────────────────────────────────────────────────────┤
│ Operation 1: UPDATE accounts SET balance = balance - 500               │
│              WHERE user_id = 'alice'                                   │
│                                                                        │
│ • ATOMICITY: WAL record written (before-image: 1000, after: 500)       │
│ • ISOLATION: Row locked or version created depending on implementation │
│ • CONSISTENCY: CHECK constraint verified (balance >= 0)                │
│                                                                        │
│ Operation 2: UPDATE accounts SET balance = balance + 500               │
│              WHERE user_id = 'bob'                                     │
│                                                                        │
│ • ATOMICITY: WAL record written (before-image: 300, after: 800)        │
│ • ISOLATION: Row locked or version created                             │
│ • CONSISTENCY: Constraints verified                                    │
└────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌────────────────────────────────────────────────────────────────────────┐
│                    PHASE 3: PREPARE TO COMMIT                          │
├────────────────────────────────────────────────────────────────────────┤
│ • CONSISTENCY: All deferred constraints checked                        │
│ • CONSISTENCY: Triggers executed                                       │
│ • ISOLATION (SSI): Check for serialization conflicts                   │
│                                                                        │
│ If any check fails → ABORT (proceed to Phase 4a)                       │
│ If all checks pass → COMMIT (proceed to Phase 4b)                      │
└────────────────────────────────────────────────────────────────────────┘
                          │                   │
                          ▼                   ▼
┌──────────────────────────────┐  ┌──────────────────────────────────────┐
│      PHASE 4a: ABORT         │  │      PHASE 4b: COMMIT                │
├──────────────────────────────┤  ├──────────────────────────────────────┤
│ • ATOMICITY: Roll back using │  │ • ATOMICITY: Write COMMIT to WAL     │
│   WAL before-images          │  │ • DURABILITY: fsync WAL to disk      │
│ • ISOLATION: Release locks   │  │ • DURABILITY: (optional) wait for    │
│   Drop uncommitted versions  │  │   replica acknowledgment             │
│ • Result: As if tx never     │  │ • ISOLATION: Release locks / make    │
│   happened                   │  │   versions visible                   │
│                              │  │ • Result: Changes permanent          │
└──────────────────────────────┘  └──────────────────────────────────────┘
                                                  │
                                                  ▼
                              ┌──────────────────────────────────────────┐
                              │        CLIENT RECEIVES "COMMIT OK"       │
                              │                                          │
                              │ The transaction is now:                  │
                              │ • ATOMIC: Both updates applied or neither│
                              │ • CONSISTENT: All invariants preserved   │
                              │ • ISOLATED: No interference from others  │
                              │ • DURABLE: Survives any subsequent crash │
                              └──────────────────────────────────────────┘

How ACID Properties Reinforce Each Other

ACID properties are deeply interconnected. Each property contributes to the overall reliability, and weakening one often impacts others.

The Dependency Web:

ACID Interdependencies

                         ┌─────────────────┐
                         │   CONSISTENCY   │
                         │ (Valid States)  │
                         └────────▲────────┘
                                 │
              supported by       │        supported by
          ┌──────────────────────┤────────────────────────┐
          │                      │                        │
          │                      │                        │
    ┌─────┴──────┐         ┌─────┴──────┐          ┌──────┴──────┐
    │  ATOMICITY │         │ ISOLATION  │          │ DURABILITY  │
    │ (All/None) │◄───────►│ (Separate) │◄────────►│ (Permanent) │
    └────────────┘         └────────────┘          └─────────────┘
          │                      │                        │
          │        shares        │         both           │
          └───────►│◄────────────┘      require WAL       │
                   │                         │            │
             ┌─────┴─────┐                   └────────────┘
             │    WAL    │
             │(Log Files)│
             └───────────┘
 
Key Relationships:
─────────────────
• ATOMICITY + DURABILITY: Both rely on WAL for recovery
• ATOMICITY + ISOLATION: Both use transaction boundaries
• ISOLATION + CONSISTENCY: Isolation prevents concurrent violations
• DURABILITY + CONSISTENCY: Preserved consistent state survives

ACID Property Dependencies
If This Property Fails...	These Properties Are Compromised...
Atomicity	Consistency (partial updates violate invariants)
Consistency	Data validity (incorrect data gets persisted)
Isolation	Atomicity & Consistency (concurrent interference)
Durability	All properties become meaningless (data lost)

ACID Is a System, Not a Checklist

Don't think of ACID as four independent boxes to check. They form an integrated system where each component supports the others. You can't fully evaluate one property without considering its interactions with the others.

When Strong ACID Matters Most

Certain domains absolutely require full ACID guarantees. In these contexts, any compromise risks serious consequences—financial loss, legal liability, or safety hazards.

High-Stakes ACID Domains

•Financial Transactions — Bank transfers, payments, trading systems. Money cannot appear or disappear due to crashes. Regulatory requirements mandate audit trails and data integrity.
•Healthcare Records — Patient data, medication orders, lab results. Incorrect or lost data can harm patients. Legal requirements (HIPAA, etc.) demand reliability.
•Legal and Compliance Systems — Court records, contracts, regulatory filings. Legal validity depends on data integrity. Immutable audit trails required.
•E-commerce Checkout — Order processing, inventory management, payment capture. Lost orders mean lost revenue; double-charging destroys trust.
•Identity and Authentication — User credentials, access permissions, security tokens. Inconsistent auth state creates security vulnerabilities.
•Booking and Reservation Systems — Airlines, hotels, events. Double-booking is a critical business failure. Concurrency must be handled correctly.

High-Stakes Transaction Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
// E-commerce checkout: Must be fully ACID
async function processCheckout(cartId: string, paymentDetails: PaymentDetails) {
  return await prisma.$transaction(async (tx) => {
    // 1. Lock and validate cart
    const cart = await tx.cart.findUnique({
      where: { id: cartId },
      include: { items: { include: { product: true } } }
    });
    
    if (!cart || cart.status !== 'active') {
      throw new Error('Invalid cart');
    }
    
    // 2. Check inventory atomically (prevent overselling)
    for (const item of cart.items) {
      const updated = await tx.product.updateMany({
        where: {
          id: item.productId,
          inventory: { gte: item.quantity }  // Only if enough stock
        },
        data: {
          inventory: { decrement: item.quantity }
        }
      });
      
      if (updated.count === 0) {
        // ATOMICITY: Entire transaction will roll back
        throw new Error(`Insufficient inventory for ${item.product.name}`);
      }
    }
    
    // 3. Process payment
    const paymentResult = await processPayment(paymentDetails, cart.total);
    if (!paymentResult.success) {
      // ATOMICITY: Inventory decrements will be rolled back
      throw new Error('Payment failed');
    }
    
    // 4. Create order record
    const order = await tx.order.create({
      data: {
        userId: cart.userId,
        total: cart.total,
        paymentId: paymentResult.id,
        status: 'confirmed',
        items: {
          create: cart.items.map(item => ({
            productId: item.productId,
            quantity: item.quantity,
            price: item.price
          }))
        }
      }
    });
    
    // 5. Mark cart as completed
    await tx.cart.update({
      where: { id: cartId },
      data: { status: 'completed', orderId: order.id }
    });
    
    return order;
    
    // If ANY step fails, ALL changes roll back
    // CONSISTENCY: Inventory matches orders
    // ISOLATION: No race conditions with concurrent checkouts
    // DURABILITY: Order survives any crash after commit
  }, {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
    timeout: 30000,
    maxWait: 10000
  });
}

When ACID Relaxation Is Acceptable

Not every operation needs full ACID. For some use cases, relaxing guarantees enables better performance, availability, or scalability. The key is understanding the trade-offs.

Scenarios Where Relaxation May Be Acceptable:

ACID Relaxation Decision Matrix
Use Case	Acceptable Relaxation	Rationale
View counts, like counts	Eventual consistency, async writes	Approximate counts are fine; exact count not critical
User activity logging	Async writes, no immediate consistency	Logs can be eventually consistent; some loss acceptable
Cache warming	No durability needed	Cache is rebuilt anyway; not source of truth
Session data	May accept some loss	Users can re-authenticate; convenience, not critical
Analytics/metrics	Approximate is acceptable	Statistical accuracy more important than precision
Draft saves (auto-save)	Eventual durability, async	Explicit save is the commit point; drafts can be lost
Real-time feeds	Slight staleness OK	Freshness less important than availability

Signs Relaxation Is OK

•Data can be regenerated or re-fetched
•Slight inconsistency has no business impact
•User experience with faster response outweighs risk
•The data is supplementary, not authoritative
•Statistical accuracy matters more than precision

Signs Relaxation Is NOT OK

•Money, inventory, or legal records involved
•Regulatory requirements mandate integrity
•Inconsistency could harm users or business
•Data cannot be reconstructed if lost
•The operation has compensating transaction requirements

Relaxed ACID for View Counts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// View count: eventual consistency is fine
async function incrementViewCount(articleId: string) {
  // Fire-and-forget: we don't wait for confirmation
  // Some increments may be lost on crash—that's acceptable
  prisma.article.update({
    where: { id: articleId },
    data: { viewCount: { increment: 1 } }
  }).catch(err => {
    // Log but don't throw—view count loss is acceptable
    console.warn(`Failed to increment view count: ${err.message}`);
  });
  
  // Return immediately—don't block user experience
  // for a non-critical operation
}
 
// Or even better: batch updates
const viewBuffer = new Map<string, number>();
 
function bufferViewIncrement(articleId: string) {
  viewBuffer.set(articleId, (viewBuffer.get(articleId) || 0) + 1);
}
 
// Flush periodically
setInterval(async () => {
  const updates = [...viewBuffer.entries()];
  viewBuffer.clear();
  
  // Bulk update—much more efficient
  for (const [articleId, count] of updates) {
    await prisma.article.update({
      where: { id: articleId },
      data: { viewCount: { increment: count } }
    }).catch(console.warn);
  }
}, 30000); // Every 30 seconds

Performance Costs of ACID

ACID guarantees come with performance overhead. Understanding these costs helps you make informed decisions about when full ACID is necessary and when relaxation might be beneficial.

Atomicity Costs:

WAL logging overhead (extra writes)
Transaction bookkeeping
Potential for rollback (wasted work)

Consistency Costs:

Constraint checking on every modification
Trigger execution
Index maintenance

Isolation Costs:

Lock contention (transactions wait for each other)
MVCC overhead (version management, garbage collection)
Potential for aborts in SSI

Durability Costs:

fsync latency (1-10ms per commit)
Network round-trip for synchronous replication
Storage I/O for WAL

Typical ACID Performance Impact
Configuration	Writes/Second	Latency (p95)	Data Safety
fsync=off, no replication	50,000+	< 1ms	Low (data loss on crash)
fsync=on, no replication	5,000-10,000	5-10ms	Medium (survives crash)
fsync=on, async replication	4,000-8,000	8-15ms	Medium (may lose recent data)
fsync=on, sync replication	1,000-3,000	20-50ms	High (survives server failure)
Serializable + sync repl	500-2,000	30-100ms	Maximum

Optimization Strategies

If ACID performance is a bottleneck: (1) Use group commit for better batching, (2) Use async replication for reads, sync for writes, (3) Partition workloads—critical data gets full ACID, analytics get relaxed, (4) Use connection pooling to reduce connection overhead, (5) Consider read replicas to offload read traffic.

Practical Transaction Patterns

These patterns help you use ACID effectively while avoiding common pitfalls.

Pattern 1: Keep Transactions Short

Long transactions hold locks, block other transactions, and increase the chance of conflicts. Keep transaction scope minimal.

Short Transaction Pattern
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
// ❌ BAD: Long transaction with external call
async function processOrderBad(orderId: string) {
  await prisma.$transaction(async (tx) => {
    const order = await tx.order.findUnique({ where: { id: orderId } });
    
    // This HTTP call might take seconds—holding locks the whole time!
    const shippingLabel = await externalShippingService.createLabel(order);
    
    await tx.order.update({
      where: { id: orderId },
      data: { shippingLabel, status: 'shipped' }
    });
  });
}
 
// ✅ GOOD: Minimize transaction scope
async function processOrderGood(orderId: string) {
  // Read data outside transaction
  const order = await prisma.order.findUnique({ where: { id: orderId } });
  
  // External call outside transaction
  const shippingLabel = await externalShippingService.createLabel(order);
  
  // Short transaction for the actual update
  await prisma.$transaction(async (tx) => {
    // Verify order hasn't changed
    const current = await tx.order.findUnique({ where: { id: orderId } });
    if (current.status !== 'pending') {
      throw new Error('Order status changed');
    }
    
    await tx.order.update({
      where: { id: orderId },
      data: { shippingLabel, status: 'shipped' }
    });
  });
}

Pattern 2: Optimistic Concurrency Control

Instead of locking pessimistically, detect conflicts at update time using version numbers.

Optimistic Locking Pattern
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
// Table has a 'version' column that increments on each update
 
async function updateWithOptimisticLocking(
  userId: string, 
  newEmail: string
) {
  // Read current version
  const user = await prisma.user.findUnique({ where: { id: userId } });
  
  // Apply business logic (can take time, no locks held)
  const validatedEmail = await validateEmail(newEmail);
  
  // Update only if version hasn't changed
  const result = await prisma.user.updateMany({
    where: { 
      id: userId, 
      version: user.version  // Only update if version matches
    },
    data: { 
      email: validatedEmail,
      version: { increment: 1 }  // Bump version
    }
  });
  
  if (result.count === 0) {
    // Someone else modified the row—retry or fail
    throw new Error('Concurrent modification detected');
  }
  
  return result;
}
 
// Retry wrapper for optimistic locking
async function withRetry<T>(
  operation: () => Promise<T>,
  maxRetries = 3
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      if (error.message === 'Concurrent modification detected' && attempt < maxRetries) {
        await new Promise(r => setTimeout(r, 100 * attempt)); // Backoff
        continue;
      }
      throw error;
    }
  }
  throw new Error('Max retries exceeded');
}

Pattern 3: Idempotent Operations

Design operations so they can be safely retried without side effects.

Idempotent Operation Pattern
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
// ❌ NOT IDEMPOTENT: Multiple calls create multiple records
async function createPaymentBad(orderId: string, amount: number) {
  return prisma.payment.create({
    data: { orderId, amount, status: 'pending' }
  });
}
 
// ✅ IDEMPOTENT: Use idempotency key
async function createPaymentGood(
  orderId: string, 
  amount: number, 
  idempotencyKey: string
) {
  // First, check if we've already processed this request
  const existing = await prisma.payment.findUnique({
    where: { idempotencyKey }
  });
  
  if (existing) {
    return existing; // Return the same result as before
  }
  
  // Create with the idempotency key (unique constraint)
  try {
    return await prisma.payment.create({
      data: { 
        orderId, 
        amount, 
        status: 'pending',
        idempotencyKey  // Unique constraint prevents duplicates
      }
    });
  } catch (error) {
    if (error.code === 'P2002') { // Unique constraint violation
      // Race condition—another request created it first
      return prisma.payment.findUnique({ where: { idempotencyKey } });
    }
    throw error;
  }
}

ACID Beyond Traditional Databases

ACID isn't exclusive to traditional relational databases. Many modern systems provide ACID-like guarantees, though sometimes with different trade-offs.

NewSQL Databases:

Databases like CockroachDB, Google Spanner, and TiDB provide full ACID across distributed clusters. They use sophisticated protocols (Percolator, TrueTime) to achieve serializable isolation at global scale.

ACID Support Across Database Types
Database Type	Examples	ACID Support	Notes
Traditional RDBMS	PostgreSQL, MySQL, Oracle	Full ✓	Gold standard; well-understood semantics
NewSQL	CockroachDB, Spanner, TiDB	Full ✓	Distributed ACID; higher latency
Document Stores	MongoDB 4.0+	Single-document + Multi-doc ✓	Added multi-doc transactions in 4.0
Key-Value	FoundationDB	Full ✓	ACID-compliant distributed KV
Key-Value	Redis	Limited	Single-command atomic; MULTI/EXEC not durable
Wide-Column	Cassandra, HBase	Limited	Row-level atomicity only
Time-Series	TimescaleDB	Full ✓ (PostgreSQL)	Inherits ACID from PostgreSQL

Read the Fine Print

When a NoSQL database claims 'ACID support,' read the documentation carefully. Many provide atomicity only for single-document operations, not multi-document transactions. Some offer 'eventual durability.' Understand exactly what guarantees you're getting.

Decision Framework: Choosing Your Guarantees

Use this framework when designing systems to determine the appropriate level of ACID guarantees.

ACID Decision Framework
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
┌────────────────────────────────────────────────────────────────────────┐
│                    ACID REQUIREMENT ASSESSMENT                         │
└────────────────────────────────────────────────────────────────────────┘
 
QUESTION 1: Is money, legal standing, or safety involved?
─────────────────────────────────────────────────────────
  YES → FULL ACID REQUIRED. No shortcuts.
  NO  → Continue assessment.
 
QUESTION 2: Can this data be regenerated or refetched?
─────────────────────────────────────────────────────────
  YES → Durability relaxation may be acceptable.
        Consider: async writes, write-behind caching.
  NO  → Durability is required. Ensure fsync and replication.
 
QUESTION 3: Is temporary inconsistency visible to users?
─────────────────────────────────────────────────────────
  YES, and it matters → Full consistency required.
  YES, but acceptable → Eventual consistency may work.
  NO  → Eventual consistency is likely fine.
 
QUESTION 4: Do multiple operations need to succeed together?
─────────────────────────────────────────────────────────
  YES → Atomicity required. Use transactions.
  NO  → Single operations can be auto-committed.
 
QUESTION 5: Do concurrent users modify the same data?
─────────────────────────────────────────────────────────
  YES, frequently → Higher isolation level needed.
                    Consider: Serializable, explicit locking.
  YES, rarely    → Optimistic locking may suffice.
  NO             → Read Committed is usually fine.
 
QUESTION 6: What is the performance requirement?
─────────────────────────────────────────────────────────
  Latency-critical (< 10ms) → 
      May need async replication, connection pooling.
      Consider: eventual durability for non-critical data.
  
  Throughput-critical (> 10k TPS) →
      Need group commit, connection pooling, possibly sharding.
      Consider: batching writes, async processing.
  
  Moderate requirements →
      Standard ACID is fine. Don't over-optimize.

When in Doubt, Choose Safety

If you're unsure whether you need full ACID, start with full ACID. It's much easier to relax guarantees later (when you understand your workload better) than to add them (which may require architectural changes). Premature optimization toward weaker guarantees has caused many production incidents.

Module Summary: ACID Properties Mastered

You've now completed a comprehensive study of ACID properties—the foundational guarantees that make databases reliable repositories for critical data.

Module Key Takeaways

•Atomicity ensures all-or-nothing — Transactions complete in their entirety or have no effect. WAL logging and recovery mechanisms make this possible.
•Consistency preserves invariants — Database constraints and application logic work together to ensure data validity across all transactions.
•Isolation prevents interference — Concurrent transactions execute as if serialized. Isolation levels let you trade correctness for performance.
•Durability makes commits permanent — Once acknowledged, committed data survives any failure. fsync and replication are the implementation.
•ACID properties reinforce each other — They form an integrated system, not independent features. Weakening one often impacts others.
•Not all data needs full ACID — Understand your requirements and choose appropriate guarantees. Over-engineering is waste; under-engineering is risk.
•Performance and safety are trade-offs — Every ACID guarantee has a cost. Design systems that provide necessary safety without unnecessary overhead.

Module Complete

You now have a deep understanding of ACID properties—what they guarantee, how they're implemented, when they're essential, and when relaxation is acceptable. This knowledge is foundational for database selection, transaction design, and system architecture. You can now reason precisely about data guarantees in any system design discussion.

5 / 5

Loading learning content...

System DesignACID Properties

ACID Properties: The Pillars of Transactional Integrity

LevelIntermediate

Duration75 mins

TopicACID Properties

5 / 5

ACID in Practice

From Theory to Production

What You Will Learn

The Complete ACID Transaction Lifecycle

Let's trace a complete transaction through a database, showing how each ACID property contributes at different phases.

Scenario: Bank Transfer

Alice transfers $500 to Bob. This involves debiting Alice's account and crediting Bob's account—a classic two-operation transaction.

ACID Transaction Lifecycle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
┌────────────────────────────────────────────────────────────────────────┐
│                    PHASE 1: BEGIN TRANSACTION                          │
├────────────────────────────────────────────────────────────────────────┤
│ • Transaction assigned a unique ID (e.g., Tx-12345)                    │
│ • ISOLATION: Snapshot taken (for SI/SSI); locks may be acquired        │
│ • ATOMICITY: Transaction registered; WAL will track its operations     │
└────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌────────────────────────────────────────────────────────────────────────┐
│                    PHASE 2: EXECUTE OPERATIONS                         │
├────────────────────────────────────────────────────────────────────────┤
│ Operation 1: UPDATE accounts SET balance = balance - 500               │
│              WHERE user_id = 'alice'                                   │
│                                                                        │
│ • ATOMICITY: WAL record written (before-image: 1000, after: 500)       │
│ • ISOLATION: Row locked or version created depending on implementation │
│ • CONSISTENCY: CHECK constraint verified (balance >= 0)                │
│                                                                        │
│ Operation 2: UPDATE accounts SET balance = balance + 500               │
│              WHERE user_id = 'bob'                                     │
│                                                                        │
│ • ATOMICITY: WAL record written (before-image: 300, after: 800)        │
│ • ISOLATION: Row locked or version created                             │
│ • CONSISTENCY: Constraints verified                                    │
└────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌────────────────────────────────────────────────────────────────────────┐
│                    PHASE 3: PREPARE TO COMMIT                          │
├────────────────────────────────────────────────────────────────────────┤
│ • CONSISTENCY: All deferred constraints checked                        │
│ • CONSISTENCY: Triggers executed                                       │
│ • ISOLATION (SSI): Check for serialization conflicts                   │
│                                                                        │
│ If any check fails → ABORT (proceed to Phase 4a)                       │
│ If all checks pass → COMMIT (proceed to Phase 4b)                      │
└────────────────────────────────────────────────────────────────────────┘
                          │                   │
                          ▼                   ▼
┌──────────────────────────────┐  ┌──────────────────────────────────────┐
│      PHASE 4a: ABORT         │  │      PHASE 4b: COMMIT                │
├──────────────────────────────┤  ├──────────────────────────────────────┤
│ • ATOMICITY: Roll back using │  │ • ATOMICITY: Write COMMIT to WAL     │
│   WAL before-images          │  │ • DURABILITY: fsync WAL to disk      │
│ • ISOLATION: Release locks   │  │ • DURABILITY: (optional) wait for    │
│   Drop uncommitted versions  │  │   replica acknowledgment             │
│ • Result: As if tx never     │  │ • ISOLATION: Release locks / make    │
│   happened                   │  │   versions visible                   │
│                              │  │ • Result: Changes permanent          │
└──────────────────────────────┘  └──────────────────────────────────────┘
                                                  │
                                                  ▼
                              ┌──────────────────────────────────────────┐
                              │        CLIENT RECEIVES "COMMIT OK"       │
                              │                                          │
                              │ The transaction is now:                  │
                              │ • ATOMIC: Both updates applied or neither│
                              │ • CONSISTENT: All invariants preserved   │
                              │ • ISOLATED: No interference from others  │
                              │ • DURABLE: Survives any subsequent crash │
                              └──────────────────────────────────────────┘

How ACID Properties Reinforce Each Other

ACID properties are deeply interconnected. Each property contributes to the overall reliability, and weakening one often impacts others.

The Dependency Web:

ACID Interdependencies

                         ┌─────────────────┐
                         │   CONSISTENCY   │
                         │ (Valid States)  │
                         └────────▲────────┘
                                 │
              supported by       │        supported by
          ┌──────────────────────┤────────────────────────┐
          │                      │                        │
          │                      │                        │
    ┌─────┴──────┐         ┌─────┴──────┐          ┌──────┴──────┐
    │  ATOMICITY │         │ ISOLATION  │          │ DURABILITY  │
    │ (All/None) │◄───────►│ (Separate) │◄────────►│ (Permanent) │
    └────────────┘         └────────────┘          └─────────────┘
          │                      │                        │
          │        shares        │         both           │
          └───────►│◄────────────┘      require WAL       │
                   │                         │            │
             ┌─────┴─────┐                   └────────────┘
             │    WAL    │
             │(Log Files)│
             └───────────┘
 
Key Relationships:
─────────────────
• ATOMICITY + DURABILITY: Both rely on WAL for recovery
• ATOMICITY + ISOLATION: Both use transaction boundaries
• ISOLATION + CONSISTENCY: Isolation prevents concurrent violations
• DURABILITY + CONSISTENCY: Preserved consistent state survives

ACID Property Dependencies
If This Property Fails...	These Properties Are Compromised...
Atomicity	Consistency (partial updates violate invariants)
Consistency	Data validity (incorrect data gets persisted)
Isolation	Atomicity & Consistency (concurrent interference)
Durability	All properties become meaningless (data lost)

ACID Is a System, Not a Checklist

When Strong ACID Matters Most

Certain domains absolutely require full ACID guarantees. In these contexts, any compromise risks serious consequences—financial loss, legal liability, or safety hazards.

High-Stakes ACID Domains

•Financial Transactions — Bank transfers, payments, trading systems. Money cannot appear or disappear due to crashes. Regulatory requirements mandate audit trails and data integrity.
•Healthcare Records — Patient data, medication orders, lab results. Incorrect or lost data can harm patients. Legal requirements (HIPAA, etc.) demand reliability.
•Legal and Compliance Systems — Court records, contracts, regulatory filings. Legal validity depends on data integrity. Immutable audit trails required.
•E-commerce Checkout — Order processing, inventory management, payment capture. Lost orders mean lost revenue; double-charging destroys trust.
•Identity and Authentication — User credentials, access permissions, security tokens. Inconsistent auth state creates security vulnerabilities.
•Booking and Reservation Systems — Airlines, hotels, events. Double-booking is a critical business failure. Concurrency must be handled correctly.

High-Stakes Transaction Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
// E-commerce checkout: Must be fully ACID
async function processCheckout(cartId: string, paymentDetails: PaymentDetails) {
  return await prisma.$transaction(async (tx) => {
    // 1. Lock and validate cart
    const cart = await tx.cart.findUnique({
      where: { id: cartId },
      include: { items: { include: { product: true } } }
    });
    
    if (!cart || cart.status !== 'active') {
      throw new Error('Invalid cart');
    }
    
    // 2. Check inventory atomically (prevent overselling)
    for (const item of cart.items) {
      const updated = await tx.product.updateMany({
        where: {
          id: item.productId,
          inventory: { gte: item.quantity }  // Only if enough stock
        },
        data: {
          inventory: { decrement: item.quantity }
        }
      });
      
      if (updated.count === 0) {
        // ATOMICITY: Entire transaction will roll back
        throw new Error(`Insufficient inventory for ${item.product.name}`);
      }
    }
    
    // 3. Process payment
    const paymentResult = await processPayment(paymentDetails, cart.total);
    if (!paymentResult.success) {
      // ATOMICITY: Inventory decrements will be rolled back
      throw new Error('Payment failed');
    }
    
    // 4. Create order record
    const order = await tx.order.create({
      data: {
        userId: cart.userId,
        total: cart.total,
        paymentId: paymentResult.id,
        status: 'confirmed',
        items: {
          create: cart.items.map(item => ({
            productId: item.productId,
            quantity: item.quantity,
            price: item.price
          }))
        }
      }
    });
    
    // 5. Mark cart as completed
    await tx.cart.update({
      where: { id: cartId },
      data: { status: 'completed', orderId: order.id }
    });
    
    return order;
    
    // If ANY step fails, ALL changes roll back
    // CONSISTENCY: Inventory matches orders
    // ISOLATION: No race conditions with concurrent checkouts
    // DURABILITY: Order survives any crash after commit
  }, {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
    timeout: 30000,
    maxWait: 10000
  });
}

When ACID Relaxation Is Acceptable

Not every operation needs full ACID. For some use cases, relaxing guarantees enables better performance, availability, or scalability. The key is understanding the trade-offs.

Scenarios Where Relaxation May Be Acceptable:

ACID Relaxation Decision Matrix
Use Case	Acceptable Relaxation	Rationale
View counts, like counts	Eventual consistency, async writes	Approximate counts are fine; exact count not critical
User activity logging	Async writes, no immediate consistency	Logs can be eventually consistent; some loss acceptable
Cache warming	No durability needed	Cache is rebuilt anyway; not source of truth
Session data	May accept some loss	Users can re-authenticate; convenience, not critical
Analytics/metrics	Approximate is acceptable	Statistical accuracy more important than precision
Draft saves (auto-save)	Eventual durability, async	Explicit save is the commit point; drafts can be lost
Real-time feeds	Slight staleness OK	Freshness less important than availability

Signs Relaxation Is OK

•Data can be regenerated or re-fetched
•Slight inconsistency has no business impact
•User experience with faster response outweighs risk
•The data is supplementary, not authoritative
•Statistical accuracy matters more than precision

Signs Relaxation Is NOT OK

•Money, inventory, or legal records involved
•Regulatory requirements mandate integrity
•Inconsistency could harm users or business
•Data cannot be reconstructed if lost
•The operation has compensating transaction requirements

Relaxed ACID for View Counts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// View count: eventual consistency is fine
async function incrementViewCount(articleId: string) {
  // Fire-and-forget: we don't wait for confirmation
  // Some increments may be lost on crash—that's acceptable
  prisma.article.update({
    where: { id: articleId },
    data: { viewCount: { increment: 1 } }
  }).catch(err => {
    // Log but don't throw—view count loss is acceptable
    console.warn(`Failed to increment view count: ${err.message}`);
  });
  
  // Return immediately—don't block user experience
  // for a non-critical operation
}
 
// Or even better: batch updates
const viewBuffer = new Map<string, number>();
 
function bufferViewIncrement(articleId: string) {
  viewBuffer.set(articleId, (viewBuffer.get(articleId) || 0) + 1);
}
 
// Flush periodically
setInterval(async () => {
  const updates = [...viewBuffer.entries()];
  viewBuffer.clear();
  
  // Bulk update—much more efficient
  for (const [articleId, count] of updates) {
    await prisma.article.update({
      where: { id: articleId },
      data: { viewCount: { increment: count } }
    }).catch(console.warn);
  }
}, 30000); // Every 30 seconds

Performance Costs of ACID

ACID guarantees come with performance overhead. Understanding these costs helps you make informed decisions about when full ACID is necessary and when relaxation might be beneficial.

Atomicity Costs:

WAL logging overhead (extra writes)
Transaction bookkeeping
Potential for rollback (wasted work)

Consistency Costs:

Constraint checking on every modification
Trigger execution
Index maintenance

Isolation Costs:

Lock contention (transactions wait for each other)
MVCC overhead (version management, garbage collection)
Potential for aborts in SSI

Durability Costs:

fsync latency (1-10ms per commit)
Network round-trip for synchronous replication
Storage I/O for WAL

Typical ACID Performance Impact
Configuration	Writes/Second	Latency (p95)	Data Safety
fsync=off, no replication	50,000+	< 1ms	Low (data loss on crash)
fsync=on, no replication	5,000-10,000	5-10ms	Medium (survives crash)
fsync=on, async replication	4,000-8,000	8-15ms	Medium (may lose recent data)
fsync=on, sync replication	1,000-3,000	20-50ms	High (survives server failure)
Serializable + sync repl	500-2,000	30-100ms	Maximum

Optimization Strategies

Practical Transaction Patterns

These patterns help you use ACID effectively while avoiding common pitfalls.

Pattern 1: Keep Transactions Short

Long transactions hold locks, block other transactions, and increase the chance of conflicts. Keep transaction scope minimal.

Short Transaction Pattern
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
// ❌ BAD: Long transaction with external call
async function processOrderBad(orderId: string) {
  await prisma.$transaction(async (tx) => {
    const order = await tx.order.findUnique({ where: { id: orderId } });
    
    // This HTTP call might take seconds—holding locks the whole time!
    const shippingLabel = await externalShippingService.createLabel(order);
    
    await tx.order.update({
      where: { id: orderId },
      data: { shippingLabel, status: 'shipped' }
    });
  });
}
 
// ✅ GOOD: Minimize transaction scope
async function processOrderGood(orderId: string) {
  // Read data outside transaction
  const order = await prisma.order.findUnique({ where: { id: orderId } });
  
  // External call outside transaction
  const shippingLabel = await externalShippingService.createLabel(order);
  
  // Short transaction for the actual update
  await prisma.$transaction(async (tx) => {
    // Verify order hasn't changed
    const current = await tx.order.findUnique({ where: { id: orderId } });
    if (current.status !== 'pending') {
      throw new Error('Order status changed');
    }
    
    await tx.order.update({
      where: { id: orderId },
      data: { shippingLabel, status: 'shipped' }
    });
  });
}

Pattern 2: Optimistic Concurrency Control

Instead of locking pessimistically, detect conflicts at update time using version numbers.

Optimistic Locking Pattern
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
// Table has a 'version' column that increments on each update
 
async function updateWithOptimisticLocking(
  userId: string, 
  newEmail: string
) {
  // Read current version
  const user = await prisma.user.findUnique({ where: { id: userId } });
  
  // Apply business logic (can take time, no locks held)
  const validatedEmail = await validateEmail(newEmail);
  
  // Update only if version hasn't changed
  const result = await prisma.user.updateMany({
    where: { 
      id: userId, 
      version: user.version  // Only update if version matches
    },
    data: { 
      email: validatedEmail,
      version: { increment: 1 }  // Bump version
    }
  });
  
  if (result.count === 0) {
    // Someone else modified the row—retry or fail
    throw new Error('Concurrent modification detected');
  }
  
  return result;
}
 
// Retry wrapper for optimistic locking
async function withRetry<T>(
  operation: () => Promise<T>,
  maxRetries = 3
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      if (error.message === 'Concurrent modification detected' && attempt < maxRetries) {
        await new Promise(r => setTimeout(r, 100 * attempt)); // Backoff
        continue;
      }
      throw error;
    }
  }
  throw new Error('Max retries exceeded');
}

Pattern 3: Idempotent Operations

Design operations so they can be safely retried without side effects.

Idempotent Operation Pattern
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
// ❌ NOT IDEMPOTENT: Multiple calls create multiple records
async function createPaymentBad(orderId: string, amount: number) {
  return prisma.payment.create({
    data: { orderId, amount, status: 'pending' }
  });
}
 
// ✅ IDEMPOTENT: Use idempotency key
async function createPaymentGood(
  orderId: string, 
  amount: number, 
  idempotencyKey: string
) {
  // First, check if we've already processed this request
  const existing = await prisma.payment.findUnique({
    where: { idempotencyKey }
  });
  
  if (existing) {
    return existing; // Return the same result as before
  }
  
  // Create with the idempotency key (unique constraint)
  try {
    return await prisma.payment.create({
      data: { 
        orderId, 
        amount, 
        status: 'pending',
        idempotencyKey  // Unique constraint prevents duplicates
      }
    });
  } catch (error) {
    if (error.code === 'P2002') { // Unique constraint violation
      // Race condition—another request created it first
      return prisma.payment.findUnique({ where: { idempotencyKey } });
    }
    throw error;
  }
}

ACID Beyond Traditional Databases

ACID isn't exclusive to traditional relational databases. Many modern systems provide ACID-like guarantees, though sometimes with different trade-offs.

NewSQL Databases:

ACID Support Across Database Types
Database Type	Examples	ACID Support	Notes
Traditional RDBMS	PostgreSQL, MySQL, Oracle	Full ✓	Gold standard; well-understood semantics
NewSQL	CockroachDB, Spanner, TiDB	Full ✓	Distributed ACID; higher latency
Document Stores	MongoDB 4.0+	Single-document + Multi-doc ✓	Added multi-doc transactions in 4.0
Key-Value	FoundationDB	Full ✓	ACID-compliant distributed KV
Key-Value	Redis	Limited	Single-command atomic; MULTI/EXEC not durable
Wide-Column	Cassandra, HBase	Limited	Row-level atomicity only
Time-Series	TimescaleDB	Full ✓ (PostgreSQL)	Inherits ACID from PostgreSQL

Read the Fine Print

Decision Framework: Choosing Your Guarantees

Use this framework when designing systems to determine the appropriate level of ACID guarantees.

ACID Decision Framework
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
┌────────────────────────────────────────────────────────────────────────┐
│                    ACID REQUIREMENT ASSESSMENT                         │
└────────────────────────────────────────────────────────────────────────┘
 
QUESTION 1: Is money, legal standing, or safety involved?
─────────────────────────────────────────────────────────
  YES → FULL ACID REQUIRED. No shortcuts.
  NO  → Continue assessment.
 
QUESTION 2: Can this data be regenerated or refetched?
─────────────────────────────────────────────────────────
  YES → Durability relaxation may be acceptable.
        Consider: async writes, write-behind caching.
  NO  → Durability is required. Ensure fsync and replication.
 
QUESTION 3: Is temporary inconsistency visible to users?
─────────────────────────────────────────────────────────
  YES, and it matters → Full consistency required.
  YES, but acceptable → Eventual consistency may work.
  NO  → Eventual consistency is likely fine.
 
QUESTION 4: Do multiple operations need to succeed together?
─────────────────────────────────────────────────────────
  YES → Atomicity required. Use transactions.
  NO  → Single operations can be auto-committed.
 
QUESTION 5: Do concurrent users modify the same data?
─────────────────────────────────────────────────────────
  YES, frequently → Higher isolation level needed.
                    Consider: Serializable, explicit locking.
  YES, rarely    → Optimistic locking may suffice.
  NO             → Read Committed is usually fine.
 
QUESTION 6: What is the performance requirement?
─────────────────────────────────────────────────────────
  Latency-critical (< 10ms) → 
      May need async replication, connection pooling.
      Consider: eventual durability for non-critical data.
  
  Throughput-critical (> 10k TPS) →
      Need group commit, connection pooling, possibly sharding.
      Consider: batching writes, async processing.
  
  Moderate requirements →
      Standard ACID is fine. Don't over-optimize.

When in Doubt, Choose Safety

Module Summary: ACID Properties Mastered

You've now completed a comprehensive study of ACID properties—the foundational guarantees that make databases reliable repositories for critical data.

Module Key Takeaways

•Atomicity ensures all-or-nothing — Transactions complete in their entirety or have no effect. WAL logging and recovery mechanisms make this possible.
•Consistency preserves invariants — Database constraints and application logic work together to ensure data validity across all transactions.
•Isolation prevents interference — Concurrent transactions execute as if serialized. Isolation levels let you trade correctness for performance.
•Durability makes commits permanent — Once acknowledged, committed data survives any failure. fsync and replication are the implementation.
•ACID properties reinforce each other — They form an integrated system, not independent features. Weakening one often impacts others.
•Not all data needs full ACID — Understand your requirements and choose appropriate guarantees. Over-engineering is waste; under-engineering is risk.
•Performance and safety are trade-offs — Every ACID guarantee has a cost. Design systems that provide necessary safety without unnecessary overhead.

Module Complete

5 / 5