Database Locks and Transactions

Many production data bugs originate not from missing transactions, but from misunderstanding what transactions actually guarantee. Systems often wrap logic in BEGIN and COMMIT and assume correctness follows automatically. Under concurrent load, these systems still oversell inventory, double-charge users, or corrupt balances.

The root cause is conceptual: transactions and locks solve different problems. Transactions define failure boundaries, while locks define concurrency behavior. Treating them as interchangeable leads to systems that appear correct in single-user tests but fail under real traffic.

Transactions Define Boundaries

A transaction defines which operations must succeed or fail together. If any statement inside fails, all prior changes are rolled back. This guarantees atomicity even in the presence of crashes, exceptions, or process termination.
A classic example is a money transfer. Debiting one account and crediting another must be atomic. Partial completion is unacceptable because it violates core business invariants.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

The transaction boundary ensures that both balance updates either persist together or are fully discarded.

Transaction Without Locks (Broken Under Concurrency)

Consider an inventory service that reads stock and then updates it. A plain transaction does not prevent race conditions.

BEGIN;

SELECT stock
FROM products
WHERE id = 42;

-- decision made here in application logic

UPDATE products
SET stock = stock - 1
WHERE id = 42;

COMMIT;

Two concurrent transactions could both read stock = 1 and both decrement, resulting in stock = -1. Atomicity alone does not prevent overselling.

Race Condition Timeline Without Locks

Step-by-step demonstration under concurrent load:

• Transaction A

  BEGIN;
  SELECT stock FROM products WHERE id = 42; -- returns 1

• Transaction B

  BEGIN;
  SELECT stock FROM products WHERE id = 42; -- also returns 1

• Both proceed

  UPDATE products SET stock = stock - 1 WHERE id = 42;
  COMMIT;

• Final state

  stock = -1

Final stock is negative despite correct transaction boundaries.

Locks Define Concurrency Behavior

Adding row-level locks ensures that only one transaction can make a decision based on a row at a time. This is achieved using SELECT ... FOR UPDATE.

BEGIN;

SELECT stock
FROM products
WHERE id = 42
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 42;

COMMIT;

Now, competing transactions are blocked at the lock, ensuring decisions are made serially.

Blocking Behavior Example

• Transaction A

  BEGIN;
  SELECT stock FROM products WHERE id = 42 FOR UPDATE;
  -- row locked

• Transaction B

  BEGIN;
  SELECT stock FROM products WHERE id = 42 FOR UPDATE;
  -- BLOCKED until A finishes

• Transaction A completes

  UPDATE products SET stock = stock - 1 WHERE id = 42;
  COMMIT;

• Transaction B resumes

  -- sees updated stock
  ROLLBACK;

This prevents overselling by enforcing concurrency at the database level.

UPDATE Locks Are Too Late for Read-Before-Write

BEGIN;

SELECT stock
FROM products
WHERE id = 42;

-- race window exists here

UPDATE products
SET stock = stock - 1
WHERE id = 42;

COMMIT;

The lock on UPDATE only occurs at write time, so logical race conditions still exist if a decision was made earlier.

Safe Pattern Without FOR UPDATE

Combining the decision and the update into a single atomic statement avoids the read-before-write gap.

BEGIN;

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

-- check affected rows
-- 1 row → success
-- 0 rows → insufficient stock

COMMIT;

This approach is safe without explicit locks because the check and the mutation are atomic.

Transaction vs Lock Responsibilities

Key Takeaways

• Transactions protect state integrity: either all changes succeed or none do.
• Locks protect decision integrity: only one transaction can act on the data at a time.
• Both are required for correctness in concurrent systems.
• Atomic updates that combine read and write logic can sometimes replace explicit locks.

Designing concurrent systems correctly requires understanding both boundaries and concurrency control. Transactions alone do not prevent logical races, and locks alone do not guarantee atomic rollback. Only by using them together can business invariants be enforced under real-world concurrency.