Locking Strategies in Databases: Optimistic vs Pessimistic Approaches

Summary

• Introduction
• Why Locking Matters
• Pessimistic Locking
• Optimistic Locking
• Comparison
• Real-World Use Cases
• Conclusion

Introduction

Ever edited a shared document, only to realize someone else made changes right before you hit save? Databases face that exact challenge all the time: how to handle multiple users accessing the same data without breaking consistency.

To solve this, systems use two main approaches: optimistic locking and pessimistic locking. Each has strengths and trade-offs, and the right choice depends on your system’s workload and performance needs.

Why Locking Matters

When multiple users or processes try to read and write the same data, conflicts can occur. For example:

• Two bank transactions updating the same account balance.
• Multiple users editing the same product information in an e-commerce app.
• Parallel microservices writing to a shared database table.

Without a proper concurrency control mechanism, you could end up with lost updates, dirty reads, or inconsistent states.

That’s where optimistic and pessimistic strategies come into play.

Pessimistic Locking

Definition: Pessimistic locking assumes that conflicts are likely, so it locks resources before allowing access.

• When a transaction wants to update data, it places a lock on that data.
• Other transactions must wait until the lock is released.

Pros:

• Guarantees strong consistency.
• Prevents write conflicts completely.
• Useful when conflicts are frequent.

Cons:

• Reduces concurrency — transactions may wait a long time.
• Risk of deadlocks if locks are not managed properly.
• Can hurt performance under high load.

Example: In a banking system, before deducting money from an account, a lock is placed on the account row to prevent other transactions from accessing it until the update is complete.

Node.js Example – Pessimistic Locking with Sequelize

import { Sequelize, DataTypes } from "sequelize";

const sequelize = new Sequelize("db", "user", "pass", {
  dialect: "postgres",
});

const Account = sequelize.define("Account", {
  balance: { type: DataTypes.FLOAT, allowNull: false }
});

async function transferWithPessimisticLock(accountId, amount) {
  const transaction = await sequelize.transaction();

  try {
    // Lock the row until transaction completes
    const account = await Account.findOne({
      where: { id: accountId },
      lock: transaction.LOCK.UPDATE,
      transaction,
    });

    if (!account) throw new Error("Account not found");
    if (account.balance < amount) throw new Error("Insufficient funds");

    account.balance -= amount;
    await account.save({ transaction });

    await transaction.commit();
    console.log("Transfer completed with pessimistic lock ✅");
  } catch (err) {
    await transaction.rollback();
    console.error("Transfer failed:", err.message);
  }
}

Here, Sequelize issues SELECT … FOR UPDATE, which blocks others until the lock is released.

Optimistic Locking

Definition: Optimistic locking assumes that conflicts are rare, so it allows transactions to proceed without locks, but checks for conflicts before committing.

• Instead of blocking, each transaction works on its own copy of the data.
• At commit time, the system verifies if the data has changed. If yes → rollback and retry.

Pros:

• High concurrency and throughput.
• No waiting for locks → better performance in read-heavy systems.
• No deadlocks.

Cons:

• Transactions may fail and need retries (extra overhead).
• Less predictable performance in write-heavy workloads.

Example: In an online store, when updating a product’s stock, the system checks a version number or timestamp before committing. If another update already changed it, the transaction fails and retries.

Node.js Example – Optimistic Locking with Sequelize

const Product = sequelize.define("Product", {
  name: DataTypes.STRING,
  stock: DataTypes.INTEGER,
  version: { type: DataTypes.INTEGER } // version field
}, {
  version: true  // enable optimistic locking
});

async function reduceStockOptimistic(productId, qty) {
  try {
    const product = await Product.findByPk(productId);

    if (product.stock < qty) throw new Error("Not enough stock");

    product.stock -= qty;

    // Sequelize automatically adds WHERE version = current_version
    await product.save();

    console.log("Stock updated successfully ✅");
  } catch (err) {
    if (err.name === "SequelizeOptimisticLockError") {
      console.error("Conflict detected, retrying... 🔄");
      return reduceStockOptimistic(productId, qty); // retry
    }
    console.error("Update failed:", err.message);
  }
}

The generated SQL looks like:

UPDATE "Products" 
SET "stock" = 8, "version" = 2 
WHERE "id" = 1 AND "version" = 1;

If the version doesn’t match, the update fails and retries.

Comparison

Real-World Use Cases

• Pessimistic Locking:

  • Financial transactions (banking, stock trading).
  • Inventory systems with frequent writes.
  • Legacy enterprise applications.

• Optimistic Locking:

  • E-commerce product catalogs (many reads, fewer writes).
  • Social media posts/likes counters.
  • Microservices architectures with distributed databases.

Conclusion

The choice between optimistic and pessimistic locking comes down to workload patterns:

• If conflicts are frequent and correctness is critical → pessimistic.
• If conflicts are rare and scalability matters → optimistic.

Modern systems often mix both approaches, depending on the type of data and operations.