Skip to content
←Data & Databases

// Data & Databases · 02

ACID Transactions

Several related writesleads toAll or nothing

A group of writes that succeeds together or not at all.

How it fits together

ACID Transactions structureBEGINInsert orderROLLBACKorders: nonelines: nonestock: unchangedInsert linesStock check failsDecrement stockCOMMIT1 insert order2 insert lines3 decrement4 COMMITstock < 05 ROLLBACK
uses / callsfails / error path· numbered steps replay in order
  • BEGIN uses / calls Insert order (1 insert order)
  • Insert order uses / calls Insert lines (2 insert lines)
  • Insert lines uses / calls Decrement stock (3 decrement)
  • Decrement stock uses / calls COMMIT (4 COMMIT)
  • Decrement stock fails / error path Stock check fails (stock < 0)
  • Stock check fails fails / error path ROLLBACK (5 ROLLBACK)

Spot it

  • Order saved but its lines are missing
  • A crash mid-request leaves half-written data
  • Money left one account and never arrived

It costs

Long transactions hold locks and connections, and they cannot span services.

In code

BEGIN;​INSERT INTO orders (id, customer_id, placed_at)VALUES (@order_id, @customer_id, CURRENT_TIMESTAMP);​INSERT INTO order_lines (order_id, product_id, quantity)VALUES (@order_id, @product_id, @quantity);​UPDATE stockSET quantity = quantity - @quantityWHERE product_id = @product_id  AND quantity >= @quantity;​-- 0 rows updated means not enough stock: send ROLLBACK here instead of COMMITCOMMIT;
Go deeperThe full explanation and what to ask for · 1 min readExpand

Atomicity: all or nothing. Consistency: constraints hold before and after. Isolation: concurrent transactions don't see each other's half-done work, to a configurable degree. Durability: once committed, it survives a crash, thanks to the write-ahead log.

The classic mistake is a transaction that spans a network call. An HTTP request or message publish inside it holds locks while you wait, and if the commit then fails, the side effect has already happened. Keep transactions short and database-only.

ACID stops at the database boundary. Across services you need a saga; to publish an event reliably with a write, use a transactional outbox. The I is tuned by isolation levels, and the default is rarely fully serializable.

Ask for it

  • atomicity
  • durability
  • transaction boundary
  • rollback
  • unit of work
  • write-ahead log

Placing an order writes the order, its lines and a stock decrement in separate calls. Wrap them in one transaction (one ORM save, or an explicit BEGIN around the raw SQL calls), with no HTTP calls inside, and test that a failure mid-way leaves nothing behind.

Connects to