ACID

Backend Fundamentals · Databases · 8 min read

1. What problem does this solve?

When a backend service performs multiple database operations that belong to a single logical task—like transferring money between accounts or creating an order with inventory deduction—there is a risk that some operations succeed while others fail. This can leave the system in an inconsistent state (e.g., money deducted from one account but not credited to the other).

Why it matters

• Data integrity – Users expect that a completed transaction reflects all its effects or none at all.
• Business rules – Financial systems, inventory, reservations, and any domain that relies on atomic changes must never expose partial updates.
• Regulatory compliance – Auditable trails require that operations are either fully applied or fully rolled back.

What happens if ACID is not enforced

• Inconsistent data can propagate errors downstream (e.g., shipping orders with missing inventory).
• Audits fail because logs show partial changes.
• Users may lose trust if they see data anomalies.

Where it appears in real backend systems

• Banking and payment gateways (money transfers, refunds).
• E‑commerce order processing (order creation + inventory update).
• Reservation systems (seat booking + payment).
• Microservice orchestration where multiple services must coordinate a single business event.

2. Mental model

Analogy – The “All‑or‑Nothing” recipe

Imagine you’re baking a cake that requires flour, sugar, eggs, and butter. If you accidentally skip adding butter, the cake will taste off. The recipe guarantees that either all ingredients are added or the cake is discarded.

Engineering model

A database transaction is the same: a unit of work that groups multiple operations. The database guarantees that the unit is Atomic (all or nothing), Consistent (leaves the database in a valid state), Isolated (does not interfere with concurrent transactions), and Durable (once committed, it survives crashes). These four properties are the ACID guarantees.

3. Deep explanation

Internals

Important terminology

• Transaction – A logical unit of work.
• Commit – Finalizes the transaction; changes become visible.
• Rollback – Undoes all changes in the transaction.
• Isolation level – Degree of visibility between concurrent transactions (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE).
• Two‑Phase Commit (2PC) – Protocol for coordinating commits across multiple resources.
• Optimistic Concurrency Control – Assume no conflict; validate before commit.
• Pessimistic Concurrency Control – Acquire locks before accessing data.

Trade‑offs

Failure modes

• Deadlocks – Two transactions wait on each other’s locks. Detected by the DB and one transaction is aborted.
• Lost updates – Concurrent updates overwrite each other if isolation is too low.
• Partial commits – If a crash occurs before commit, WAL ensures rollback.
• Network partitions – In distributed DBs, a node may be isolated; consensus protocols (Raft, Paxos) help.

Limitations

• Scalability – Strict ACID can limit horizontal scaling; sharding often requires eventual consistency.
• Performance – Heavy locking or WAL writes can become bottlenecks.
• Complexity – Coordinating transactions across microservices (2PC) is hard; often replaced by compensating actions (Sagas).

Where it fits in a backend architecture

mermaid
sequenceDiagram
    participant Client
    participant API
    participant DB
    Client->>API: POST /transfer
    API->>DB: BEGIN
    API->>DB: UPDATE accounts SET balance = balance - 100 WHERE id = 1
    API->>DB: UPDATE accounts SET balance = balance + 100 WHERE id = 2
    API->>DB: COMMIT
    DB->>API: OK
    API->>Client: 200 OK

• The API layer starts a transaction, performs multiple updates, and commits.
• If any step fails, the API rolls back.

Interaction with related concepts

• Caching – Cache invalidation must happen after commit.
• Event sourcing – Events are appended after commit; the event store must be durable.
• Distributed tracing – Transactions span multiple services; tracing helps detect deadlocks or long waits.
• Idempotency – Retry logic must respect transaction boundaries to avoid double commits.

4. How it works

1. Client sends request (e.g., transfer money).
2. API starts a transaction (BEGIN).
3. API performs operations (updates, inserts).
4. Database checks constraints and writes to WAL.
5. If all operations succeed → COMMIT writes a commit record to WAL and releases locks.
6. If any operation fails → ROLLBACK undoes changes using WAL.
7. Client receives response based on commit/rollback outcome.

sequenceDiagram
    participant Client
    participant API
    participant DB
    Client->>API: POST /transfer
    API->>DB: BEGIN
    API->>DB: UPDATE accounts SET balance = balance - 100 WHERE id = 1
    API->>DB: UPDATE accounts SET balance = balance + 100 WHERE id = 2
    alt success
        API->>DB: COMMIT
        DB->>API: Commit OK
        API->>Client: 200 OK
    else failure
        API->>DB: ROLLBACK
        DB->>API: Rollback OK
        API->>Client: 500 Internal Server Error
    end

5. Practical implementation

TypeScript / Node.js with PostgreSQL (pg)

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

async function transferFunds(
  fromAccountId: number,
  toAccountId: number,
  amount: number
) {
  const client = await pool.connect();
  try {
    // 1. Start transaction
    await client.query('BEGIN');

    // 2. Debit source account
    const debitRes = await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 RETURNING balance',
      [amount, fromAccountId]
    );
    if (debitRes.rowCount === 0) {
      throw new Error('Insufficient funds or account not found');
    }

    // 3. Credit destination account
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toAccountId]
    );

    // 4. Commit
    await client.query('COMMIT');
    console.log('Transfer successful');
  } catch (err) {
    // 5. Rollback on any error
    await client.query('ROLLBACK');
    console.error('Transfer failed:', err);
    throw err;
  } finally {
    client.release();
  }
}

Key points

• BEGIN starts a transaction; all subsequent queries run in the same context.
• The debit query uses a WHERE balance >= $1 to prevent overdraft; if it returns 0 rows, we throw and rollback.
• COMMIT writes a commit record to the WAL; if the process crashes before commit, the WAL will roll back on restart.
• ROLLBACK undoes all changes made in the transaction.

Using Redis transactions (MULTI/EXEC)

import { createClient } from 'redis';

const redis = createClient();

async function incrementCounter(key: string, delta: number) {
  await redis.connect();
  try {
    await redis.multi()
      .incrby(key, delta)
      .exec();
  } catch (err) {
    console.error('Redis transaction failed', err);
  } finally {
    await redis.disconnect();
  }
}

Redis guarantees that all commands in a MULTI/EXEC block are executed atomically.

6. Production considerations

7. Common mistakes

8. Senior engineer perspective

What changes at scale?

• Architecture decisions – Decide between single‑node ACID DB vs. distributed consensus (e.g., CockroachDB, Spanner).
• Trade‑offs – 2PC across microservices is reliable but introduces latency; Sagas trade strict ACID for eventual consistency.
• Bottlenecks – Lock contention on hot keys; WAL throughput on high‑write workloads.
• Failure scenarios – Network partitions in distributed DBs; node failures during commit.
• Monitoring – Track transaction latency, deadlock counts, WAL lag, and replication lag.
• Cost/performance – Synchronous replication increases cost; sharding reduces per‑node load but complicates joins.
• When NOT to use ACID – For analytics pipelines where eventual consistency is acceptable; for high‑throughput logging where batching is preferred.

9. Interview questions

10. Hands‑on task

Task: Implement a “transfer” endpoint in the provided Node.js repository that:

1. Accepts fromAccountId, toAccountId, and amount via POST /api/transfer.
2. Uses a PostgreSQL transaction to debit and credit accounts atomically.
3. Returns HTTP 200 on success, 400 if insufficient funds, and 500 on any other error.
4. Logs transaction start, commit, rollback, and any deadlock errors.

Hints: Use pg’s client.query('BEGIN'), client.query('COMMIT'), and client.query('ROLLBACK'). Add a WHERE balance >= $1 clause to prevent overdraft. Wrap logic in try/catch/finally.

11. Quick revision

• ACID: Atomicity, Consistency, Isolation, Durability.
• Atomicity = all or nothing; enforced via WAL and rollback.
• Consistency = schema & business rules; enforced by constraints.
• Isolation levels control visibility; higher levels → more locking.
• Durability = WAL + replication; survives crashes.
• 2PC coordinates commits across multiple resources.
• Deadlocks: detect and abort one transaction.
• Optimistic vs. pessimistic concurrency control.
• In microservices, consider Sagas for eventual consistency.
• Monitor transaction latency, deadlock counts, WAL lag.
• Avoid READ UNCOMMITTED; use READ COMMITTED or higher.
• Always rollback on error; never leave transactions open.
• Use SELECT … FOR UPDATE for row‑level locking when needed.
• Sharding can break strict ACID; use distributed DBs if needed.
• Test rollback paths; handle network partitions.

12. References

• PostgreSQL documentation – Transactions: https://www.postgresql.org/docs/current/transaction-iso.html
• MySQL documentation – Isolation Levels: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
• Redis – MULTI/EXEC: https://redis.io/docs/manual/transactions/
• Two‑Phase Commit: https://en.wikipedia.org/wiki/Two-phase_commit
• CockroachDB – Distributed ACID: https://www.cockroachlabs.com/docs/stable/architecture.html
• Kafka – Exactly‑once semantics: https://kafka.apache.org/documentation/#exactly_once
• Node.js pg library: https://node-postgres.com/