Handling Concurrency in Data Writes

In this lesson, you will learn how to use optimistic locking to ensure that two simultaneous users don’t accidentally overwrite each other's changes in your database.

The Concurrency Problem

Imagine two users try to update the same document at the exact same time. User A reads a record, changes a field, and starts the save process. Before User A finishes, User B reads the same original version, makes a different change, and saves it. If User A saves last, they might inadvertently overwrite User B’s changes because their "view" of the data was already outdated.

Implementing Optimistic Locking

Instead of blocking the database (which can lead to performance bottlenecks), we use Optimistic Locking. We add a version field to our database records. Every time a record is updated, we increment this version number.

When we attempt an update, we include the version we expect to see. If the version in the database matches our expected version, the update proceeds. If it doesn't match—meaning someone else updated it first—the database update will fail, signaling a conflict.

Practical Implementation

Here is how you might structure an update query in Node.js using this logic:

async function updateUserEmail(userId, newEmail, expectedVersion) {
  const query = `
    UPDATE users 
    SET email = ?, version = version + 1 
    WHERE id = ? AND version = ?
  `;
  
  const result = await db.execute(query, [newEmail, userId, expectedVersion]);

  // If affectedRows is 0, the version didn't match or the user doesn't exist
  if (result.affectedRows === 0) {
    throw new Error("Conflict detected: The record was modified by another process.");
  }
}

In this code, the WHERE clause acts as our gatekeeper. By checking version = ?, we ensure that if anyone else updated the record while we were working, our version variable would no longer match the database, the query would affect zero rows, and we would correctly identify a collision.

By using this pattern, you maintain data integrity without locking the table and slowing down your application for other users.

Key Takeaway: Optimistic locking protects data integrity by using a version identifier to ensure that updates only succeed if the underlying record has not changed since it was last read.