multiple requests to node express mysql synchronization issue

Farzan_1202037 0
Viewed 157

Using expressJS I have an endpoint which is connected to a mysql DB where I am making a query and getting a set of data and assigning the first returned result to a user by updating a field to the user id.

The concept is that single row can only be assigned to a single user until the user has processed the row by hitting another endpoint.

Here is the query which gets the latest not processed rows:

const notProcessed = await knex('rows')
                .select('*'')
                .whereRaw(`status='Not-Processed' and (assignedTo=0 or assignedTo=${user.id})`)
                .orderByRaw('createdAt asc')
                .first();

and after I am updating the row to the user by doing:

await knex('rows').update({ assignedTo: user.id }).where({ id: notProcessed.id })

When multiple requests are sent to the endpoint sometimes I have the same row assigned to the more than 1 person.

Can't seem to find the reason or a solution for this. can anyone explain why this is happening and what is the best approach to solve this?

node.js mysql javascript
1 Answers

I don't know knex in detail and from a quick search, knex currently doesn't support using "limit" on update statements so just a description of the general approach.

First do an update for the row matching the criteria, then select that updated row.

So, first do an update operation that assigns the current user id to the first unprocessed row that either has no user assigned or already has the same user assigned:

update rows 
    set assignedTo = user.id 
    where assignedTo=0 or assignedTo=user.id 
    order by createdAt asc 
    limit 1

I think it can work like this with knex using a raw query but haven't tried that:

await knex.raw('update rows set assignedTo = :userid where assignedTo=0 or assignedTo= :userid  order by createdAt asc limit 1', {userid: user.id})

This will look for the first (earliest createdAt) row that is unassigned or already assigned to the same user and then assign that user. This happens in one go.

You can then search for the row assigned to the user:

const notProcessed = await knex('rows')
    .select('*'')
    .whereRaw(`status='Not-Processed' and assignedTo=${user.id}`)
    .orderByRaw('createdAt asc')
    .first();

Notice how now we explicitly look only for a row already assigned to the user.

Combined

await knex.raw('update rows set assignedTo = :userid where assignedTo=0 or assignedTo= :userid  order by createdAt asc limit 1', {userid: user.id})
const notProcessed = await knex('rows')
    .select('*'')
    .whereRaw(`status='Not-Processed' and assignedTo=${user.id}`)
    .orderByRaw('createdAt asc')
    .first();

Obviously you don't need the select if you don't want to work with the row immediately.

The problem is that when multiple requests are handled at the same time, you have to imagine multiple instances of the code running at the same time in parallel. So, with your original code, two requests could do your select at the same time before any of them does an update. So, both of them will have the same row returned.

By immediately updating the row within the statement, even when two statements run in parallel, the database will make sure they don't see the same row.

An alternative approach for a solution would be to use a mutex (like e.g. async-mutex) around your original code to make sure that your original select and update operation is atomic (happens in one go), but this will most likely increase the response time of your application because in some situations one request handling operation will have to wait on another one to continue.

user_1010870
Related