Database Locks and Transactions

Database transactions and locks are both used to protect data correctness, but they solve different problems. Transactions define failure boundaries, while locks define concurrency behavior.

Table of Contents

• What Problem Are We Solving?
  • Why Transactions Are Not Enough
  • Transactions vs Locks
• Transactions
  • Transaction Boundaries
  • Money Transfer Example
• Race Conditions Under Concurrency
  • Broken Inventory Example
  • Race Condition Timeline
• Locks
  • Row-Level Locks
  • SELECT FOR UPDATE
  • Blocking Behavior
• Safe Update Patterns
  • Why UPDATE Locks Can Be Too Late
  • Atomic Update Pattern
  • When to Use Each Pattern
• Common Mistakes
  • Long-Running Transactions
  • Locking Too Much Data
  • Assuming BEGIN and COMMIT Solve Everything
• Transactions vs Locks Comparison
• Key Takeaways

What Problem Are We Solving?

Many production data bugs come from misunderstanding what transactions actually guarantee. Developers wrap logic in BEGIN and COMMIT and assume the code is now safe. Under concurrent load, the same code can still oversell inventory, double-charge users, or corrupt balances.

The root cause is conceptual: transactions and locks are not the same thing. A transaction controls whether a group of changes succeeds or fails together. A lock controls whether multiple transactions can touch the same data at the same time.

Why Transactions Are Not Enough

A transaction can guarantee that your changes are committed together, but it does not automatically guarantee that the decision you made before the update was safe under concurrency.

For example, an inventory service may read stock = 1, decide that the item is available, and then update the stock. If two transactions read the same value before either update happens, both may decide that the item is available.

Transactions vs Locks

A useful mental model is:

• Transactions protect failure boundaries. They answer: should these changes commit together or roll back together?
• Locks protect concurrency decisions. They answer: who is allowed to read or modify this data while another transaction is using it?

Transactions

Transaction Boundaries

A transaction defines which operations must succeed or fail together. If a statement fails and the transaction rolls back, earlier changes inside the same transaction are discarded.

This is important when partial completion would corrupt business state. A transfer should not debit one account without crediting another. An order should not be marked paid if payment creation failed.

Money Transfer Example

A classic transaction example is moving money between two accounts.

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 persist together or neither persists.

If the application crashes after the debit but before the credit, the database can roll back the transaction and avoid a partially completed transfer.

Race Conditions Under Concurrency

Broken Inventory Example

Now consider an inventory service. The application reads stock, makes a decision in application code, and then updates the row.

BEGIN;

SELECT stock
FROM products
WHERE id = 42;

-- application decides whether stock is available

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

COMMIT;

This code is wrapped in a transaction, but it can still be broken under concurrency.

The issue is the gap between reading the value and updating the value. If two transactions both read stock = 1, both may decide the product is available.

Race Condition Timeline

The failure looks like this:

Initial state:
stock = 1

Transaction A:
SELECT stock FROM products WHERE id = 42;
-- returns 1

Transaction B:
SELECT stock FROM products WHERE id = 42;
-- also returns 1

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

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

Final state:
stock = -1

The transaction boundaries worked. Each transaction committed successfully. But the business invariant failed because both transactions made a decision using stale concurrency state.

Locks

Row-Level Locks

A row-level lock prevents multiple transactions from making conflicting decisions about the same row at the same time.

Instead of letting two transactions read the same stock value and both proceed, the database can force the second transaction to wait until the first transaction finishes.

SELECT FOR UPDATE

In PostgreSQL and many relational databases, SELECT ... FOR UPDATE locks selected rows for the duration of the transaction.

BEGIN;

SELECT stock
FROM products
WHERE id = 42
FOR UPDATE;

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

COMMIT;

Now, if another transaction tries to lock the same product row, it must wait.

Blocking Behavior

The same inventory example becomes safe because decisions are made one transaction at a time.

Transaction A:
BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- row is locked

Transaction B:
BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- blocked until Transaction A finishes

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

Transaction B:
-- resumes and sees updated stock
-- can now decide whether to continue or rollback

This prevents overselling because the second transaction cannot make a decision based on the old stock value while the first transaction is still active.

Safe Update Patterns

Why UPDATE Locks Can Be Too Late

An UPDATE statement does acquire locks, but that may be too late if the application already made a decision based on a previous SELECT.

BEGIN;

SELECT stock
FROM products
WHERE id = 42;

-- race window exists here

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

COMMIT;

The problem is not that UPDATE has no lock. The problem is that the business decision happened before the lock protected the row.

Atomic Update Pattern

One safe alternative is combining the check and mutation into a single atomic statement.

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 pattern is safe because the condition and the update happen together inside one statement. There is no separate read-before-write decision in application code.

This is often the simplest and best approach for counters, quotas, inventory, and rate-limited resources.

When to Use Each Pattern

Common Mistakes

Long-Running Transactions

Long-running transactions hold locks longer than necessary and can block other queries.

For example, opening a transaction, calling an external API, and then committing later is usually dangerous.

BEGIN
  update database row
  call slow payment provider
  wait 5 seconds
COMMIT

During those five seconds, other transactions may be blocked. Keep transactions short and avoid network calls inside critical transaction sections when possible.

Locking Too Much Data

Locks should be as specific as possible. Accidentally locking many rows can reduce throughput and create unnecessary contention.

For example, locking all pending orders when only one order needs to be processed can block workers that could have safely processed different orders.

Assuming BEGIN and COMMIT Solve Everything

A transaction is not a magic correctness wrapper. It protects atomicity, but it does not automatically protect every business invariant under concurrency.

If your logic reads data, makes a decision, and then writes later, you must think about what happens when another transaction changes the same data at the same time.

Transactions vs Locks Comparison

Key Takeaways

• Transactions protect failure boundaries.
• Locks protect concurrency decisions.
• A transaction alone does not always prevent race conditions.
• SELECT ... FOR UPDATE is useful when application logic must make a decision based on current row state.
• Atomic updates are often simpler and safer for counters, inventory, quotas, and limits.
• Keep transactions short to avoid unnecessary lock contention.

The most important idea is this:

Transactions decide what commits together. Locks decide who can make decisions at the same time.

Correct concurrent systems usually require understanding both.