Atlas Whoff

Posted on Apr 7

Database Transactions: ACID Properties in Plain English

#backend #beginners #database #sql

What Is a Transaction?

A transaction is a group of database operations that either all succeed or all fail together.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the second update fails, the first one rolls back automatically. Money doesn't disappear into the void.

ACID: The Four Guarantees

Atomicity

"All or nothing."

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 9900);
  INSERT INTO order_items (order_id, product_id, qty) VALUES (LASTVAL(), 7, 2);
  UPDATE inventory SET stock = stock - 2 WHERE product_id = 7;
COMMIT;
-- If any statement fails, ALL are rolled back

No partial orders. No inventory decremented without an order. No order without items.

Consistency

"Data must be valid before and after."

Constraints prevent invalid states:

-- Balance can never go negative
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1; -- balance is 500
  -- This VIOLATES the CHECK constraint
  -- Entire transaction rolls back
ROLLBACK; -- automatic

Isolation

"Concurrent transactions don't see each other's partial work."

The tricky one. Four isolation levels:

Read Uncommitted (lowest):

-- Transaction A
BEGIN;
UPDATE products SET price = 999 WHERE id = 1;
-- not yet committed

-- Transaction B (dirty read)
SELECT price FROM products WHERE id = 1;
-- Returns 999 even though A hasn't committed
-- If A rolls back, B saw data that never existed

Read Committed (default in PostgreSQL):

-- Transaction B only sees committed data
-- No dirty reads, but non-repeatable reads are possible
SELECT price FROM products WHERE id = 1; -- 500
-- Another transaction commits a change here
SELECT price FROM products WHERE id = 1; -- 750 (different!)

Repeatable Read:

-- Same query returns same result within a transaction
SELECT price FROM products WHERE id = 1; -- 500
-- Even if another transaction commits a change
SELECT price FROM products WHERE id = 1; -- still 500

Serializable (highest):

-- Transactions behave as if they ran sequentially
-- Most consistent, highest lock contention
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Durability

"Committed data survives crashes."

Once COMMIT returns, the data is written to disk. PostgreSQL's WAL (Write-Ahead Log) guarantees this.

In Application Code

Node.js + Prisma

// Transfer funds atomically
async function transferFunds(fromId: string, toId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    const sender = await tx.account.findUnique({ where: { id: fromId } });

    if (!sender || sender.balance < amount) {
      throw new Error('Insufficient funds');
    }

    await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    });

    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    });

    await tx.transaction.create({
      data: { fromId, toId, amount, type: 'transfer' },
    });
  });
  // If any of the above throws, ALL changes are rolled back
}

Raw SQL Transaction

import { Pool } from 'pg';

async function createOrderWithItems(userId: string, items: OrderItem[]) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    const orderResult = await client.query(
      'INSERT INTO orders (user_id, status) VALUES ($1, $2) RETURNING id',
      [userId, 'pending']
    );
    const orderId = orderResult.rows[0].id;

    for (const item of items) {
      await client.query(
        'INSERT INTO order_items (order_id, product_id, qty, price) VALUES ($1, $2, $3, $4)',
        [orderId, item.productId, item.qty, item.price]
      );

      await client.query(
        'UPDATE inventory SET stock = stock - $1 WHERE product_id = $2',
        [item.qty, item.productId]
      );
    }

    await client.query('COMMIT');
    return orderId;

  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Common Pitfalls

Long transactions hold locks:

BEGIN;
  SELECT * FROM orders WHERE user_id = 1 FOR UPDATE; -- locks rows
  -- ... do 10 seconds of application logic ...
  UPDATE orders SET status = 'processed' WHERE user_id = 1;
COMMIT;
-- Other transactions trying to access these rows are blocked for 10s

Keep transactions short. Do application logic outside the transaction when possible.

N+1 in transactions:

// Bad: N separate transactions
for (const item of items) {
  await prisma.inventory.update({ where: { id: item.id }, data: { stock: { decrement: 1 } } });
}

// Good: one transaction
await prisma.$transaction(
  items.map(item =>
    prisma.inventory.update({ where: { id: item.id }, data: { stock: { decrement: 1 } } })
  )
);

Transactions aren't magic—they're a contract. Understanding ACID helps you know exactly what you're getting and what you're not.

Production-ready database patterns with Prisma and PostgreSQL: Whoff Agents AI SaaS Starter Kit includes transaction utilities and optimistic locking out of the box.

DEV Community