Database Locks and Blocking: Why Two Writers Can't Share One Row

Database

PostgreSQL

Concurrency

Production

2 hours ago1697 words·9 min read

In one sentence: the previous article showed how to change a schema safely. This one goes back to a more basic question — when two people try to change the same row at the same time, what does the database do? The answer gives us three words: lock, blocking, and deadlock.

Reference the previous article in this series:

What You'll Get Out of This

By the end, you'll be able to:

Explain what a "lock" actually is — it's not what most people picture.
Tell the difference between a lock, blocking, and a deadlock.
Understand why one ALTER TABLE can freeze an entire table.
See who is waiting on whom with a single query, and know the most useful way to prevent it.

What You Need to Know

You can write basic SQL (SELECT, UPDATE).
You've read Part 3 (what happens to a SQL statement) and Part 11 (rows live in pages).
You have a local PostgreSQL to practice on.

First, a Quick Reminder: What One UPDATE Does

From Part 3, you know a statement goes through the parser, the optimizer, and the executor. From Part 11, you know rows live inside pages, and pages sit in the buffer pool.

So a single UPDATE is roughly three steps:

txt

Find the row        (in the buffer pool, or read the page in from disk)
Compute the new value
Write it back

One person running this alone: no problem at all.

Now: What If Two People Run It at the Same Time?

Let's use a concrete example everyone has hit: inventory.

txt

products table:  product A, stock = 1

Two customers buy it at almost the same moment:

txt

Customer 1: read stock → 1 → "there's one left!" → write 0
Customer 2: read stock → 1 → "there's one left!" → write 0

Two orders went through, but stock only dropped by one. You sold one item twice. This is an oversell, and it's the classic bug of concurrent writes.

Notice this isn't a database problem. Any "read, then compute, then write" done by two people at once has this problem — a text file, a Redis key, a Postgres row. The database's job is to stop it.

The Database's Fix: Make the Second One Wait

The database's approach is blunt and effective. While customer 1 is changing that row, the database marks the row as taken. When customer 2 shows up and finds it taken, it has to wait — until customer 1 is done.

That waiting has a name: blocking.

The important part: customer 2 didn't do anything wrong. It isn't an error. It's just standing in line. Blocking is normal, and it's the price of correctness.

So What Is a Lock, Really?

Here's a common mental picture: a lock means the database physically locks the row, like a padlock on a box.

That's not what happens. A lock is just a note in the database's memory, something like:

txt

Row 42 is currently being modified by transaction 7.

And you almost never write this note yourself. The moment you type UPDATE, the database takes the lock for you. You've been using locks all along — you just never had to notice.

When Does the Note Get Removed? When the Transaction Ends

Back to "wait until customer 1 is done." What does done mean here?

Not "when the UPDATE statement finishes." It means when the transaction finishes.

A transaction is a group of statements between BEGIN and COMMIT that all take effect together, or not at all.

sql

BEGIN;
UPDATE products SET stock = 0 WHERE id = 42;
-- the note stays up this whole time...
COMMIT;  -- ...and only now is it removed

This leads to the single most important rule in this article:

Keep transactions short. Every extra second a transaction stays open is a second the lock stays up, and a second everyone else waits.

The classic mistake is doing slow work inside a transaction — calling an external API, or worse, waiting for a user to type something:

// Bad
await db.transaction(async (tx) => {
  await tx.update(products).set({ stock: 0 }).where(eq(products.id, 42));
  await callSlowExternalApi(); // the lock is held this whole time
});

Deadlock: Both Sides Hold What the Other Wants

Blocking is one person waiting for another. A deadlock is two people waiting for each other.

Picture a narrow doorway with two people: one carrying a box in, the other carrying a box out. Each is waiting for the other to move. Nobody ever moves.

In databases, the classic example is two transfers in opposite directions:

sql

-- Transaction 1: A → B
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks account 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- wants account 2...

-- Transaction 2: B → A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- locks account 2
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- wants account 1...

Transaction 1 holds account 1 and wants account 2. Transaction 2 holds account 2 and wants account 1. They wait forever.

The database notices this and kills one of them (that transaction gets an error and rolls back), so the other can finish. The fix is simple: everyone takes locks in the same order. If both transactions always lock the smaller account id first, the circle can never form.

Back to Your Migration Article: Why One ALTER TABLE Freezes the Whole Table

An ALTER TABLE changes the structure of the table. To do that, it needs the whole table to itself — nobody using it, not even readers.

Normally that's fine: the change is quick and everyone gets on with their day. The real danger is one rule: the lock queue doesn't let you cut in line.

txt

Session A: BEGIN; SELECT * FROM orders;   -- reading, holds its note, never commits
Session B: ALTER TABLE orders ...;        -- needs the whole table → waits for A
Session C: SELECT * FROM orders;          -- a harmless read... but waits behind B!

Session C's plain SELECT would normally never be blocked by A's read. But because B is already waiting in line, C has to queue behind B. So B waits on A, and C waits on B. One long transaction plus one DDL can freeze every reader of the table.

That is exactly the 2 a.m. alert from the previous article — now you can see the machinery underneath it.

Seeing It, and Stopping It

To see who is waiting on whom, one query is enough:

sql

SELECT
  pid,
  state,
  pg_blocking_pids(pid) AS blocked_by,
  left(query, 60)       AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

blocked_by lists the sessions that are holding things up.
If a session's state is idle in transaction, it has finished its work but never committed — it's holding locks while doing nothing.

Prevention comes down to two habits:

1. Keep transactions short (the rule from above).

2. Set lock_timeout before DDL. Without it, an ALTER TABLE that can't get its lock waits forever and drags everyone down with it. With it, it gives up and you can retry later:

sql

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN region text;
-- if it can't get the table within 3 seconds, it errors out instead of freezing it

At the Application Layer: Keeping Transactions Short in Drizzle

The database can't decide how long your transaction stays open — your code does. Move slow work outside the transaction:

// Good: do the slow call first, then open a short transaction
const data = await callSlowExternalApi();

await db.transaction(async (tx) => {
  await tx.update(products).set({ stock: 0 }).where(eq(products.id, 42));
});

And when a transaction touches several rows, take them in a consistent order so deadlocks can't form:

await db.transaction(async (tx) => {
  // Always lock the smaller id first — same order in every transaction
  const [first, second] = [fromId, toId].sort((a, b) => a - b);

  await tx.select().from(accounts).where(eq(accounts.id, first)).for("update");
  await tx.select().from(accounts).where(eq(accounts.id, second)).for("update");
});

Try It Yourself

See blocking. In two psql sessions, have one run BEGIN; UPDATE ... and not commit, then run the same UPDATE in the other. Watch it hang. Run the query above in a third session to see who blocks whom. Then COMMIT in the first and watch the second go through.
Make a deadlock. Create two accounts, then run the two opposite-order transfers above in two sessions. See the error. Now rewrite both to lock the smaller id first and confirm it's gone.
Freeze a table. In session A, BEGIN; SELECT * FROM orders; and leave it open. In session B, run an ALTER TABLE. In session C, run a plain SELECT and watch it wait behind B. Then redo it with SET lock_timeout = '3s'; and see B give up instead.

Summary & Keywords

A lock is a note in memory, not a padlock. It exists because two people doing "read, compute, write" at once would corrupt the data.
Blocking is just waiting in line. It's normal. The danger is waiting forever, or a long queue.
A deadlock is two people waiting for each other. Avoid it by taking locks in a consistent order.
A lock is released when the transaction ends — so keeping transactions short is the most important habit.
One ALTER TABLE can freeze a whole table, because the queue doesn't allow cutting in line. lock_timeout is your safety valve.

Keywords: lock, blocking, deadlock, transaction, ALTER TABLE, lock_timeout, pg_stat_activity, idle in transaction, oversell.

Where to Go Next

We quietly skipped one question: if a writer locks a row, why can a reader still read it at the same time? The answer is MVCC — and that's next.