DEV Community

Igor Nosatov
Igor Nosatov

Posted on

When PostgreSQL Transactions Lie to You: A Detective Story About Phantom Reads

#backend #database #postgres

Or: How I Learned to Stop Worrying and Love SERIALIZABLE

Tags: #postgresql #database #concurrency #debugging

You know that feeling when your code is perfect, your tests are green, but production still betrays you?
Let me tell you about the week I spent chasing ghosts in my database.

🔍 The Crime Scene

It's 3 AM.
My phone explodes with alerts.

Our e-commerce platform just sold the same limited-edition sneaker to 47 different people.

We only had 12 pairs.

The interesting part?
Every single transaction checked the inventory count before updating.
Every one of them saw “enough stock available.”

And somehow, we oversold by 350%.

async function purchaseItem(itemId, quantity) {
  const available = await db.query(
    'SELECT stock FROM inventory WHERE id = $1',
    [itemId]
  );

  if (available.rows[0].stock >= quantity) {
    await db.query(
      'UPDATE inventory SET stock = stock - $1 WHERE id = $2',
      [quantity, itemId]
    );
    return { success: true };
  }

  return { success: false, error: 'Insufficient stock' };
}
Enter fullscreen mode Exit fullscreen mode

🎭 The Plot Twist: Isolation Levels Aren’t What You Think

PostgreSQL has multiple “truth modes,” and the default one is… truth-adjacent.

🍽️ The Restaurant Analogy

READ UNCOMMITTED (Postgres doesn’t support this)

  • You can see the waiter writing down other people’s orders.
  • Chaos. Nobody uses this.

READ COMMITTED (PostgreSQL’s default)

  • You see the menu as it is at the moment you look at it.
  • But someone else can sell out a dish before you order.
  • Each query sees a fresh snapshot.

REPEATABLE READ

  • You get the menu once when you sit down.
  • Even if items sell out, your menu never changes.
  • Phantom reads can still happen.

SERIALIZABLE

  • Time stops.
  • You are the only customer in the restaurant.
  • If anything conflicts, PostgreSQL throws an error.

💥 The Exact Bug That Triggered the Alert 

-- Transaction 1 (Alice)
BEGIN;
SELECT stock FROM inventory WHERE id = 123; -- 12

-- Transaction 2 (Bob)
BEGIN;
SELECT stock FROM inventory WHERE id = 123; -- 12

-- Alice updates
UPDATE inventory SET stock = stock - 12 WHERE id = 123;
COMMIT; -- stock = 0

-- Bob updates (still thinks it's 12)
UPDATE inventory SET stock = stock - 12 WHERE id = 123;
COMMIT; -- stock = -12 ❌
Enter fullscreen mode Exit fullscreen mode

This is a lost update, perfectly legal under READ COMMITTED.

🧰 The Real Fixes (From Quick to Nuclear)

Level 1: SELECT FOR UPDATE 

const result = await client.query(
  'SELECT stock FROM inventory WHERE id = $1 FOR UPDATE',
  [itemId]
);
Enter fullscreen mode Exit fullscreen mode

Level 2: REPEATABLE READ + Retry 

await client.query('BEGIN ISOLATION LEVEL REPEATABLE READ');
// Add retry logic for error 40001
Enter fullscreen mode Exit fullscreen mode

Level 3: SERIALIZABLE 

await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
// Guaranteed correctness, but may require retries
Enter fullscreen mode Exit fullscreen mode

🧩 Bottom Line

  • Display data → READ COMMITTED
  • Update based on previous reads → SELECT FOR UPDATE or REPEATABLE READ
  • Multi-step logic → REPEATABLE READ + retry
  • Financial flows → SERIALIZABLE

Always test concurrent transactions — otherwise, you’re not testing reality.

Top comments (0)