node.js, mysql, concurrency

Our problem statement is two requests trying to modify the same data simultaneously, leading to one update overwriting the other. We encountered this issue in one of my previous company called TruckX(Feet management and ELD platform). Here's a breakdown of common solutions in Node.js with MySQL, along with their pros and cons:

1. Optimistic Locking

How it works: Add a version column (integer, starting at 1) to your MySQL table. When a row is fetched, include the version number. In the UPDATE statement, include a WHERE clause that checks if the version number in the database matches the version number retrieved by the application. If they match, the update proceeds, and the version number is incremented. If they don't match, it means another request has already updated the row, and the UPDATE will affect zero rows. Your Node.js code can then detect this and handle the conflict (e.g., retry, return an error).

Example (MySQL):

  ALTER TABLE your_table ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 1;

In your Node.js code:

const [row] = await connection.execute('SELECT data, version FROM your_table WHERE id = ?', [id]);
const new_data = /* ... modify data ... */;
const result = await connection.execute(
'UPDATE your_table SET data = ?, version = ? WHERE id = ? AND version = ?',
 [new_data, row.version + 1, id, row.version]
);
if (result.affectedRows === 0) {
  // Conflict!  Handle it (e.g., retry, error)
}

Pros: Simple to implement, good performance (only a small overhead for the version check).
Cons: Requires modifying the database schema. Your application logic needs to handle the conflict resolution.

2. Pessimistic Locking (using SELECT ... FOR UPDATE)

How it works: When a request fetches the row, it acquires a lock on the row, preventing other transactions from modifying it until the lock is released. MySQL's SELECT ... FOR UPDATE achieves this.
So In your Node.js code:

    await connection.beginTransaction(); // Start a transaction
    const [row] = await connection.execute('SELECT data FROM your_table WHERE id = ? FOR UPDATE', [id]);

    const new_data = /* ... modify data ... */;
    await connection.execute('UPDATE your_table SET data = ? WHERE id = ?', [new_data, id]);
    await connection.commit(); // Release the lock

Pros: Guarantees data integrity.
Cons: Can lead to deadlocks if not handled carefully. Reduces concurrency as other requests might have to wait. Generally higher overhead than optimistic locking. In our project when we used this way, we identified that the lock sometimes results in request timeouts for subsequent request resulting in longer waiting time to fail a request.

3. Application-Level Queuing

How it works: Implement a queue (e.g., using Redis) to handle update requests for a specific row. When a request comes in, add it to the queue. A worker process consumes requests from the queue one by one, ensuring that updates are processed sequentially. One example is AWS SQS queue. It is robust and simple to use.
Pros: Very robust, handles concurrent updates effectively.
Cons: Usually managed queues like AWS SQS are easier for implementation. However, The complexity of implementation is added over time when system scales. The complexity comes with a cost of managing and when the system scale is across multiple geographical regions.

4. Stored Procedure with Row-Level Locking (MySQL 8+):

How it works: You can encapsulate the entire read-modify-write operation within a stored procedure. Inside the stored procedure, you can use row-level locking (similar to SELECT ... FOR UPDATE) to ensure atomicity.
Pros: Good performance, encapsulates the logic on the database server. Sounds like solutions for monolith services that are tightly coupled.
Cons: Requires familiarity with stored procedures. Plus It didnt sound a generic solution, isnt it? very specific solution at database level.

5. Compare and Swap (CAS) Operations (If supported by your MySQL driver):

Some MySQL drivers might provide direct support for CAS operations. These operations allow you to update a row only if a specific condition is met (e.g., the current value of a column matches a certain value). This is similar in concept to optimistic locking but might be handled more efficiently at the database level.

Which approach is best?

Optimistic locking is often the sweet spot. It's relatively simple to implement and offers good performance. It's a good default choice.
Pessimistic locking should be used when data integrity is absolutely critical and the potential for deadlocks is carefully considered. Avoid it if high concurrency is a primary requirement.
Application-level queuing is suitable for very high-volume scenarios where you need to guarantee sequential updates and can tolerate the added complexity. With Managed queues things are more easy
Stored procedures can be a good option if you are comfortable with them and want to encapsulate the logic on the database server.

We need to also consider implementing proper error handling to catch conflicts and other potential issues. Using a connection pool to manage your database connections adds efficiency. So to conclude choose the approach that best suits our specific needs. The requirement of the system evolves over time. Every evolution demands for different a approach.