Solutions To Lost Update Concurrency Problem

What is Concurrency?

Concurrency involves multiple processes or transactions executing at the same time, potentially interacting with shared data. Imagine a social media platform where several users react to a post simultaneously. Each reaction is an operation that affects the post’s reaction count.

The Lost Update Problem

In simple terms, the update of a client's request is overwritten by the update of another. This is also referred to as RACE CONDITIONS

Let’s consider a simple scenario to illustrate the Lost Update Problem. Suppose you have a post with a reaction count of 100. Two users simultaneously decide to like and dislike the post respectively. Ideally, after these reactions are processed, the count should return to 100. However, without proper concurrency control, one of these reactions might be lost, and the final count might not reflect all reactions accurately. This issue occurs because multiple transactions (or operations) are trying to update the same data at the same time without proper coordination.

The Problem in Action

To better understand the Lost Update Problem, let’s walk through a simple scenario using the example of reacting to a social media post.

Imagine a post that currently has 100 likes. Two clients, A and B, both decide to interact with this post at almost the same time, but with different actions—Client A wants to like the post, while Client B decides to un-like it. Below is a table that shows the steps each client takes:

• Step 1: Client A reads the current like count (x = 100) and increments it by 1 in memory (x = 101). At the same time, Client B reads the same current like count (x = 100) and decrements it by 1 in memory (x = 99).

• Step 2: Client B writes the updated count (x = 99) to the database.

• Step 3: Client A then writes its updated count (x = 101) to the database.

The Result: Instead of the expected behaviour, where the final like count should return to 100, it ends up being 101—Client A’s write operation overwrote Client B’s, effectively losing Client B’s update.

This is a classic example of the Lost Update Problem, where concurrent transactions interfere with each other, leading to incorrect data being written to the database.

Solutions to the Problem

To address the Lost Update Problem, various strategies can be employed, each with its trade-offs. Let’s explore four key approaches:

Using Atomic Operations

Atomic operations ensure that an update is performed as a single, indivisible unit. This means that each reaction is handled independently, and the increment operation is performed without interference from other operations.

Example:

-- Increment the reaction count atomically
UPDATE posts SET like_count = like_count + 1 WHERE id = 1;

Sequelize (MySQL/PostgreSQL) Example:

await Post.update(
  { like_count: Sequelize.literal('like_count + 1') },
  { where: { id: 1 } }
);

Mongoose (MongoDB) Example:

await Post.findByIdAndUpdate(
  1,
  { $inc: { like_count: 1 } },
  { new: true }
);

Pros: Simple and efficient, especially for straightforward updates.

Cons: Limited to basic operations and may not handle complex business logic.

Using Pessimistic Locking

Pessimistic locking involves putting a lock on a record or document that is selected by a query until the transaction is committed or rolled back. It prevents other transactions from updating or deleting the record.

This type of approach uses transactions. In SQL-based databases, this is often done using the FOR UPDATE clause.

Raw SQL Query Example with FOR UPDATE:

BEGIN;

SELECT like_count 
FROM posts 
WHERE id = 1 
FOR UPDATE;

UPDATE posts 
SET like_count = like_count + 1 
WHERE id = 1;

COMMIT;

Sequelize (MySQL/PostgreSQL) Example:

const transaction = await sequelize.transaction();

try {
  const post = await Post.findOne({
    where: { id: 1 },
    lock: true, // Locking the row for update
    transaction
  });

  post.like_count += 1;
  await post.save({ transaction });

  await transaction.commit();
} catch (error) {
  await transaction.rollback();
  throw error;
}

Mongoose (MongoDB) Example: Pessimistic locking is not natively supported in MongoDB as it is in SQL databases, but you can simulate it with a custom locking mechanism.

Types of Pessimistic Locks:

• Exclusive Lock (Write Lock): This type of lock prevents other transactions from both reading and writing to the locked resource. It is typically used when the transaction intends to modify the data.

• Shared Lock (Read Lock): A shared lock allows other transactions to read the data but not to modify it. Multiple transactions can acquire a shared lock on the same resource, but no transaction can change it until all shared locks are released.

In Sequelize ORM, the lock option can be used to specify the type of lock when querying the database. The value passed to this option depends on the database being used:

• For MySQL: LOCK.UPDATE for an exclusive lock and LOCK.SHARE for a shared lock.

• For PostgreSQL: LOCK.UPDATE for an exclusive lock and LOCK.KEY_SHARE for a shared lock.

Pros: Guarantees that no other transactions can interfere.
Cons: Can reduce concurrency and lead to potential deadlocks—a situation where two or more transactions are waiting for each other to release lock

await Post.findOne({
  where: { id: 1 },
  lock: sequelize.Transaction.LOCK.UPDATE, // Exclusive Lock
  transaction
});

Using Serializable Isolation Level

The isolation level in a DBMS determines how transactions interact with each other and the level of visibility they have over one another. Higher isolation levels reduce the chances of concurrency issues but can impact performance.

• Serializable is the highest isolation level, which handles transactions as if they are executed one after another. It effectively prevents concurrency problems like the Lost Update Problem but can reduce throughput due to the strict control.

• Other levels include Repeatable Read, Read Committed, and Read Uncommitted.

• Read Uncommitted is the lowest level, offering maximum performance but introducing more concurrency problems like dirty reads and lost updates.

Raw SQL Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT like_count 
FROM posts 
WHERE id = 1;

UPDATE posts 
SET like_count = like_count + 1 
WHERE id = 1;

COMMIT;

In this example, the SERIALIZABLE isolation level ensures that no other transaction can read or write to the posts table until the transaction is completed.

Sequelize Example:

const transaction = await sequelize.transaction({
  isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE
});

try {
  const post = await Post.findOne({
    where: { id: 1 },
    transaction
  });

  post.like_count += 1;
  await post.save({ transaction });

  await transaction.commit();
} catch (error) {
  await transaction.rollback();
  // Handle concurrency conflict (e.g., retry or notify user)
}

In Sequelize, you can set the isolation level by passing the isolationLevel option to the transaction method.

Mongoose (MongoDB) Example:
Mongoose, being built on MongoDB, doesn't directly support transaction isolation levels like traditional relational databases. However, MongoDB offers some degree of isolation by default when using transactions.

Pros: Ensures complete isolation and prevents all other concurrency problems.
Cons: Can significantly impact performance and reduce concurrency.

Using Optimistic Locking

In this approach, multiple transactions proceed without locking resources. It assumes that conflicts are rare and checks for them only before committing changes. If a conflict is detected (e.g., another transaction has modified the data), the transaction is rolled back, and the operation can be retried.

It is typically implemented using a versioning mechanism where each record or document has a version or last update timestamp field. This field is checked during the update, and if it doesn't match the expected value, the transaction is aborted.

Raw SQL Example:

BEGIN;

SELECT like_count, version 
FROM posts 
WHERE id = 1;

-- Assume version = 5
UPDATE posts 
SET like_count = like_count + 1, 
    version = version + 1 
WHERE id = 1 AND version = 5;

COMMIT;

In this example, the UPDATE statement ensures that the version hasn't changed since it was last read. If the version has changed, the update will fail, signalling a conflict.

Sequelize Example:

const post = await Post.findOne({
  where: { id: 1 }
});

const originalVersion = post.version;

post.like_count += 1;

try {
  await post.save({
    where: {
      id: post.id,
      version: originalVersion
    }
  });
} catch (error) {
  // Handle concurrency conflict (e.g., retry or notify user)
}

In Sequelize, optimistic locking can be implemented by manually checking the version field before updating the record. The update will proceed only if the version in the database matches the original version.

Mongoose Example:

const post = await Post.findById(id);

const originalVersion = post.__v; // Mongoose uses __v for versioning by default

post.like_count += 1;

try {
  await post.save(); // Mongoose automatically checks and updates __v
} catch (error) {
  // Handle concurrency conflict (e.g., retry or notify user)
}

Mongoose has built-in support for optimistic locking through versioning (__v field). When you save a document, Mongoose automatically checks the version and throws an error if there's a version mismatch.

Pros:

• One of the key advantages of optimistic locking is that it allows you to gracefully handle concurrency issues. For example, if a conflict is detected, you can notify the client that the data has been updated by someone else. This allows the client to review the changes and decide how to proceed, which enhances the user experience.

• Allows greater concurrency and is suitable for scenarios where conflicts are rare.

Cons: Requires additional logic to handle conflicts and retries.

More Reads

If you’re interested in diving deeper into the topics covered in this article, here are some additional resources:

• https://on-systems.tech/blog/128-preventing-read-committed-sql-concurrency-errors/

• https://www.geeksforgeeks.org/concurrency-problems-in-dbms-transactions/

• https://www.javatpoint.com/dbms-concurrency-control

Conclusion

Concurrency is an important concept that drives the performance of modern applications but also brings challenges such as the Lost Update Problem. By employing strategies like atomic operations, pessimistic locking, isolation levels, and optimistic locking, you can effectively manage these challenges and ensure data accuracy in concurrent environments.

Ultimately, the best solution depends on your application's specific needs and limitations. Knowing these techniques and how they work together is crucial for building strong, high-performance systems.

If there's anything you believe needs a more detailed explanation, or if you spot any errors, feel free to leave a comment. Your feedback is invaluable!

if you found this article helpful, a little love ❤️ would be greatly appreciated.