对node express mysql 同步问题的多个请求 [英] multiple requests to node express mysql synchronization issue

问题描述

使用 expressJS 我有一个连接到 mysql 数据库的端点，我在那里进行查询并获取一组数据，并通过将字段更新为用户 ID 将第一个返回的结果分配给用户.

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 })

当多个请求发送到端点时有时我将同一行分配给 1 个以上的人.

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?

推荐答案

knex 的详细信息我不知道，通过快速搜索，knex 目前不支持使用limit"；关于更新语句，所以只是对一般方法的描述.

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.

因此，首先执行更新操作，将当前用户 ID 分配给第一个未分配用户或已分配相同用户的未处理行:

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

我认为它可以像这样使用原始查询与 knex 一起工作，但还没有尝试过:

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})

这将查找未分配或已分配给同一用户的第一行(最早的 createdAt)行，然后分配该用户.一次性完成.

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.

综合

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.

解决方案的另一种方法是使用互斥锁(例如 async-mutex) 围绕您的原始代码，以确保您的原始选择和更新操作是原子的(一次性发生)，但这很可能会增加您的应用程序的响应时间，因为在某些情况下，一个请求处理操作将不得不等待另一个继续.

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.

这篇关于对node express mysql 同步问题的多个请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！