Preventing conflicting updates in Postgres

Question

I have a "users" table with a "state" column that is jsonb. The logic for updating this state is complicated and is implemented in a "true" programming language (e.g. JavaScript).

I have multiple servers providing an API for updating the state. The load balancing between these servers is non-deterministic, so update requests might not always go to the same server.

The API will grab the current state (SELECT), run the function to update it (updateState) and then update the database (UPDATE) with the result.

It might look like this:

const updateHandler = async (id) =>
  const { state } = await db.one(
    `SELECT * FROM users WHERE id = $<id> LIMIT 1`, 
    { id });

  const nextState = updateState(state);

  await db.query(
    `UPDATE users SET state = $<nextState> WHERE id = $<id>`, 
    { id, nextState });
});

Because I have multiple servers, I want to ensure that when the state is updated, it is the result of calling updateState on the state value that is already there. Each server should have to queue (or perhaps throw an error) if another server is already performing an update.

What is the best strategy for doing this?

Answer 1

Using Optimistic Locking Pattern, you need just check the current state to validate your transaction

UPDATE users
SET    state = $<nextState>
WHERE  id = $<id> and state=$<currentState>

Check the nb row updating to detect the conflict :

• 1 row => it's ok
• 0 row => you have a conflict (another process have already update the same id data)