Handling Concurrent Updates Safely in Node.js: Optimistic vs. Pessimistic Locking

{ “title”: “Handling Concurrent Updates Safely in Node.js: Optimistic vs. Pessimistic Locking”, “summary”: “A practical, code-heavy guide exploring how to prevent lost updates and race conditions in Node.js applications by implementing optimistic and pessimistic locking with PostgreSQL.”, “tags”: [“Node.js”, “Database”, “PostgreSQL”, “Backend”, “Software Architecture”], “body”: “As Node.js developers, we often pride ourselves on the event-driven, asynchronous nature of our applications. It handles thousands of concurrent HTTP requests with ease. However, asynchronous I/O and event loops do not protect us from the classic nemesis of distributed data management: concurrent database modifications.

Imagine an e-commerce flash sale. Two API requests arrive simultaneously to purchase the last item in stock. Both read a stock count of 1. Both decrement it to 0. Both proceed to charge the customer. Suddenly, you’ve oversold an item you don’t have. This is a lost update race condition, and it happens entirely at the database layer, bypassing application-level logic.

In this deep dive, we will explore how to solve this using two primary strategies: Optimistic Locking (using version columns) and Pessimistic Locking (using database-level SELECT ... FOR UPDATE), implemented practically in a Node.js environment using PostgreSQL.

The Anatomy of a Lost Update

To understand why standard database queries fail under concurrency, let’s look at a naive Node.js update flow using a popular query builder or ORM:

// A naive and vulnerable update flow
async function updateInventory(productId, quantityPurchased) {
  // Step 1: Read current state
  const product = await db.query('SELECT stock FROM products WHERE id = $1', [productId]);
  const currentStock = product.rows[0].stock;

  // Step 2: Compute new state in application memory
  if (currentStock < quantityPurchased) {
    throw new Error('Out of stock');
  }
  const newStock = currentStock - quantityPurchased;

  // Step 3: Write new state back
  // DANGER: Another request could have modified 'stock' between Step 1 and Step 3!
  await db.query('UPDATE products SET stock = $1 WHERE id = $2', [newStock, productId]);
}

Between Step 1 and Step 3, time passes. In a high-traffic Node.js API, another asynchronous request can execute Step 1, read the same stale stock value, and overwrite the database in Step 3. The first write is silently lost.

To fix this, we must shift our paradigm to locking.

Strategy 1: Pessimistic Locking (SELECT … FOR UPDATE)

Pessimistic locking assumes that conflicts are frequent. When you read a row you intend to modify, you immediately acquire an exclusive lock on it at the database level. Other transactions attempting to read or lock that row must wait until your transaction commits or rolls back.

How it Works in PostgreSQL

PostgreSQL provides the FOR UPDATE clause for this exact purpose. When appended to a SELECT query inside a transaction block, it locks the selected rows against concurrent updates.

Implementing Pessimistic Locking in Node.js

Let’s build a safe inventory checkout function using native pg transactions:

const { Pool } = require('pg');
const pool = new Pool();

async function purchaseItemPessimistic(productId, quantityPurchased) {
  // Transactions are mandatory for pessimistic locking
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // 1. Select and lock the row exclusively
    const selectQuery = `
      SELECT stock FROM products 
      WHERE id = $1 
      FOR UPDATE
    `;
    const result = await client.query(selectQuery, [productId]);

    if (result.rows.length === 0) {
      throw new Error('Product not found');
    }

    const currentStock = result.rows[0].stock;

    // 2. Validate availability
    if (currentStock < quantityPurchased) {
      throw new Error('Insufficient stock');
    }

    const newStock = currentStock - quantityPurchased;

    // 3. Perform the update within the same locked transaction
    const updateQuery = `
      UPDATE products 
      SET stock = $1 
      WHERE id = $2
    `;
    await client.query(updateQuery, [newStock, productId]);

    // 4. Commit transaction, releasing the lock
    await client.query('COMMIT');
    return { success: true, remainingStock: newStock };

  } catch (error) {
    // Rollback changes on any error
    await client.query('ROLLBACK');
    throw error;
  } finally {
    // Always release the client back to the pool
    client.release();
  }
}

Pros and Cons of Pessimistic Locking

• Pros:
  • Guarantees consistency; updates will never be lost.
  • Simple mental model—the database handles the queuing of concurrent requests.
• Cons:
  • Reduced Concurrency: Threads block each other, which can lead to connection pool exhaustion and timeouts under heavy load.
  • Deadlocks: If two transactions try to lock Row A and Row B in reverse orders, PostgreSQL will abort one with a deadlock error.

Strategy 2: Optimistic Locking (Version Columns)

Optimistic locking assumes that conflicts are rare. Instead of locking rows upfront, you allow multiple transactions to read and modify data freely. However, every row has a version (or revision) integer column. When updating, you verify that the version hasn’t changed since you read it.

Database Schema Preparation

First, add a version column to your table:

ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

Implementing Optimistic Locking in Node.js

Here is how you implement an optimistic retry loop in your Node.js application:

async function purchaseItemOptimistic(productId, quantityPurchased, maxRetries = 3) {
  let attempts = 0;

  while (attempts < maxRetries) {
    attempts++;

    // 1. Read the data along with the current version
    const productResult = await pool.query(
      'SELECT stock, version FROM products WHERE id = $1', 
      [productId]
    );

    if (productResult.rows.length === 0) {
      throw new Error('Product not found');
    }

    const { stock: currentStock, version: currentVersion } = productResult.rows[0];

    // 2. Perform business logic checks
    if (currentStock < quantityPurchased) {
      throw new Error('Insufficient stock');
    }

    const newStock = currentStock - quantityPurchased;

    // 3. Attempt update, conditionally matching the version and incrementing it
    const updateResult = await pool.query(
      `UPDATE products 
       SET stock = $1, version = version + 1 
       WHERE id = $2 AND version = $3`,
      [newStock, productId, currentVersion]
    );

    // 4. Check if the update actually affected a row
    if (updateResult.rowCount === 1) {
      // Success! The version matched and the update went through.
      return { success: true, remainingStock: newStock };
    }

    // If rowCount === 0, another process updated the record concurrently.
    // We log a warning and retry the loop.
    console.warn(`Concurrency conflict on product ${productId}. Retrying (Attempt ${attempts}/${maxRetries})...`);
    
    // Optional: Add a small jittered backoff here before retrying
  }

  throw new Error('Failed to update record due to high concurrency. Please try again.');
}

Pros and Cons of Optimistic Locking

• Pros:
  • No Database Locks: Read operations do not block write operations, maximizing throughput and scalability.
  • Great for read-heavy systems with low write contention.
• Cons:
  • Requires application-level retry logic.
  • If write contention is extremely high, requests will repeatedly fail and exhaust retries, causing user-facing errors or high CPU utilization.

Comparing Optimistic vs. Pessimistic Locking

Best Practices for Node.js Developers

1. Keep Transactions Short: If using pessimistic locking, never perform external API calls (e.g., calling Stripe or SendGrid) inside your database transaction block. This holds locks open far longer than necessary.
2. Handle Deadlocks Gracefully: PostgreSQL error code 40P01 indicates a deadlock. Ensure your Node.js application catches this specific error code and implements an automatic retry mechanism.
3. Choose Based on Business Domain:
   • Use pessimistic locking when consistency is paramount and race conditions carry severe financial or inventory penalties.
   • Use optimistic locking for social media profiles, blog posts, or collaborative editing tools where concurrent updates to the exact same record are statistically rare.

Conclusion

Concurrency bugs are notoriously difficult to reproduce in local development environments because they only manifest under high load or network latency. By implementing either pessimistic locks via SELECT ... FOR UPDATE or optimistic locks with version columns in your Node.js data access layer, you can guarantee data integrity and build robust backend systems that scale safely.” }