Database Transactions: ACID Properties in Plain English

Source: Dev.to

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.

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.

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

Concurrent transactions don’t see each other’s partial work

Four isolation levels illustrate the behavior.

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;

Committed data survives crashes

Once COMMIT returns, the data is written to disk. PostgreSQL’s WAL (Write‑Ahead Log) guarantees durability.

// Transfer funds atomically (Prisma + TypeScript)
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 
    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.