Database transactions explained: ACID, rollbacks and race conditions

Placing an order usually means more than one change: take the item out of stock, create the order, record the payment. If the server crashes halfway, you don’t want stock gone with no order, or an order with no stock taken. A transaction groups those changes so they all happen, or none of them do.

The code below uses Node.js with its built-in SQLite module (Node 22.13 or newer). The same pattern works in any language and with PostgreSQL or MySQL.

The basic shape#

BEGIN;
UPDATE products SET stock = stock - 2 WHERE id = 1;
INSERT INTO orders (product_id, quantity) VALUES (1, 2);
COMMIT;

• BEGIN starts the transaction.
• The changes inside it are invisible to everyone else until…
• COMMIT makes them all permanent at once.
• ROLLBACK, instead of COMMIT, throws them all away.

Using it from code#

The important part is the rollback when anything fails:

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("shop.db");

function placeOrder(productId, quantity) {
  db.exec("BEGIN");
  try {
    db.prepare("UPDATE products SET stock = stock - ? WHERE id = ?").run(quantity, productId);
    db.prepare("INSERT INTO orders (product_id, quantity) VALUES (?, ?)").run(productId, quantity);
    db.exec("COMMIT");
  } catch (err) {
    db.exec("ROLLBACK");
    throw err;
  }
}

The products table has CHECK (stock >= 0). With 3 mugs in stock, placeOrder(1, 2) succeeds and leaves 1. A second placeOrder(1, 2) would take stock to −1, so the database rejects the update, the code rolls back, and you get CHECK constraint failed: stock >= 0. Stock stays at 1 and there’s still exactly one order.

The same holds if the second statement fails: the stock change from the first statement is undone too. That’s the whole point.

Most libraries have a helper that does the BEGIN/COMMIT/ROLLBACK for you, such as prisma.$transaction(), Knex’s knex.transaction(), or sequelize.transaction(). Use it when you have one; the idea is the same.

ACID in plain words#

You’ll see transactions described as ACID:

• Atomic. All the changes happen, or none do. No half-finished orders.
• Consistent. The data moves from one valid state to another. Every constraint (CHECK, UNIQUE, foreign keys) still holds after the commit.
• Isolated. Transactions running at the same time don’t see each other’s half-finished work.
• Durable. Once COMMIT returns, the change survives a crash or power cut.

The race condition you need to know about#

Here’s a bug that transactions alone don’t always prevent. Two customers try to buy the last mug at the same moment, and the code does this:

// Don't do this: read, decide in code, then write
const { stock } = db.prepare("SELECT stock FROM products WHERE id = ?").get(1);
if (stock >= 1) {
  db.prepare("UPDATE products SET stock = ? WHERE id = ?").run(stock - 1, 1);
}

Both requests read stock = 1, both decide there’s enough, and both write 0. You’ve sold one mug twice. This is a lost update, and it can happen even inside transactions at the default isolation level of PostgreSQL and MySQL.

Three ways to fix it, simplest first:

1. Let the database do the check and the change in one statement.

   SQLCopy

   UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1;

   Then check how many rows changed. If it’s 0, the item sold out. One statement is atomic on its own, so two requests can’t both succeed.

2. Lock the row while you decide. In PostgreSQL and MySQL, SELECT ... FOR UPDATE inside a transaction makes the second request wait until the first commits, then read the new value.

3. Use a stricter isolation level such as SERIALIZABLE. The database detects the conflict and fails one of the transactions, which you then retry. Safer, but you must write the retry code.

A CHECK (stock >= 0) constraint is a good safety net either way: even if the code is wrong, stock can’t go negative.

Keep transactions short#

While a transaction is open, it can hold locks that make other requests wait. So:

• Don’t call slow external services inside one. Charging a card or sending an email can take seconds. Do it before, or after the commit (a background job is ideal for emails).
• Don’t wait for user input while a transaction is open.
• Do the reads and writes, then commit. Milliseconds, not seconds.

When an external call and a database change must both happen exactly once, for example charging a card and recording the payment, the safe pattern uses an idempotency key; see idempotency explained.

For the tables and constraints that transactions protect, start with database design basics. If you need ordering, payments or stock handled correctly in a custom backend, I build that as part of custom API integration.