Transaction

Explanation

Transactions solve the problem of partial failures. Consider transferring $100 from Alice to Bob: you must debit Alice and credit Bob. If the system crashes between the two operations, Alice is debited but Bob is never credited — money disappears. A transaction wraps both operations: either both succeed (COMMIT) or neither takes effect (ROLLBACK on error). ACID properties govern transactions: Atomicity (the transaction is all-or-nothing — no partial states), Consistency (the transaction brings the database from one valid state to another — constraints are never violated mid-transaction), Isolation (concurrent transactions don't see each other's intermediate states), and Durability (committed transactions survive system crashes — they're written to durable storage, not just memory). Isolation levels control how much transactions see of each other's in-progress work: READ UNCOMMITTED (sees uncommitted changes — dirty reads possible), READ COMMITTED (sees only committed data at each statement — default in PostgreSQL), REPEATABLE READ (sees only data committed before the transaction started — no phantom reads), and SERIALIZABLE (transactions execute as if they were sequential — strongest isolation, most expensive). Transaction scope in ORMs: Sequelize and TypeORM support explicit transactions via transaction objects passed to queries. Prisma uses $transaction. The risk of not using transactions: a multi-step operation that partially fails leaves the database in an inconsistent state — a silent data corruption bug that's difficult to detect and fix.

Code Example

-- SQL transactions

-- Manual transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- debit Alice
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- credit Bob

-- Verify business rule: no negative balances
DO $$
BEGIN
  IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
    RAISE EXCEPTION 'Insufficient funds';
  END IF;
END $$;

COMMIT;  -- both updates committed atomically
-- On any error, PostgreSQL auto-rollbacks the transaction

-- Node.js with pg (PostgreSQL client)
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [100, aliceId]
  );
  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [100, bobId]
  );
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

Why It Matters for Engineers

Transactions are the correctness guarantee that makes financial systems, e-commerce, and any application where two things must happen atomically safe. Without transactions, partial failures create inconsistent data — a corruption that's often silent and hard to detect until an audit or customer complaint. Understanding transactions also helps you reason about concurrency: multiple simultaneous transactions can conflict (two users booking the last seat), and understanding isolation levels and locks lets you choose the right trade-off between performance and correctness.