Database Concurrency 101: Optimistic vs. Pessimistic Locking

In modern software development, we often have to handle multiple users interacting with our application at the exact same time. Optimistic and pessimistic locking are two distinct strategies used to manage concurrency, which is the technical term for when multiple users or systems try to update the exact same database record simultaneously. While optimistic locking assumes conflicts are rare and verifies changes only at the very end of an operation, pessimistic locking assumes conflicts are highly likely and preemptively blocks others from accessing the data until the current operation is finished.

A Relatable Everyday Analogy

To understand the difference, imagine a collaborative digital workspace where you and your colleagues edit text files.

Pessimistic locking is like an old-school file server where only one person can open a document for editing at a time. The moment you open the file, the server locks it. If your colleague tries to open it, they receive a "Read-Only" warning and are completely blocked from making edits until you save, close, and release your lock on the file.

Optimistic locking, on the other hand, is like a modern version control system like Git. Both you and your colleague can open, read, and edit the exact same document simultaneously. However, when you click "Save," the system checks if anyone else has saved a new version of the document since you opened it. If no one has, your save succeeds. If your colleague managed to save their changes first, the system blocks your save, alerts you to a "conflict," and asks you to review and merge the changes before trying again.

Why It Matters in the Tech Industry

In the tech industry, failing to implement a proper locking strategy leads to a highly destructive bug known as a "lost update" or a "race condition"—where the outcome of a process depends on the unpredictable sequence or timing of other events.

Imagine a financial application where two family members try to withdraw $50 from a shared $100 bank account at the exact same millisecond. Without locking, both transactions will read the balance as $100 simultaneously. Both transactions will approve the $50 withdrawal, and both will attempt to write the new balance of $50 back to the database. The bank is now down $100, but the database incorrectly shows a remaining balance of $50 because the second write silently overwrote the first one. By using database locking, software engineers prevent this double-spending, protect system integrity, and ensure transactions are processed safely.

Implementation in Node.js and MySQL

Here is a simple example of how to implement optimistic locking in a Node.js application using a MySQL database client. We do this by adding a version column to our database table and verifying it during our update query.

// A helper function to update a user's bank balance using Optimistic Locking
async function updateAccountBalance(connection, accountId, withdrawAmount, currentVersion) {
  // We attempt to update the balance and increment the version number,
  // but ONLY if the version in the database matches the version we originally read.
  const query = `
    UPDATE bank_accounts 
    SET balance = balance - ?, version = version + 1 
    WHERE id = ? AND version = ?
  `;

  const [result] = await connection.execute(query, [withdrawAmount, accountId, currentVersion]);

  // If no rows were affected, it means the version changed in the background
  // because another transaction updated it first. We must roll back and retry.
  if (result.affectedRows === 0) {
    throw new Error("Concurrency Conflict: The account was updated by another process. Please retry.");
  }

  return "Balance updated successfully!";
}

The Strategic Takeaway

Choosing between these two approaches is always a trade-off between speed and strict prevention. You should default to optimistic locking for applications with low-to-medium write conflicts because it keeps your application fast, highly available, and free of database bottlenecks. However, when dealing with highly critical operations where conflicts are frequent and data corruption is unacceptable—such as reservation systems or payment gateways—pessimistic locking is the superior choice, despite the performance hit of making users wait in a digital queue.

---

Originally published on my blog. You can read the alternative breakdown here.