mysql - 防止查询相互覆盖

Question

我有一个接收汽车预订请求的应用程序，并且在 cars 表中的预订汽车状态应设置为 in_use。

通常会做这样的事情:

def reserve_car(user_id)
  car = Car.find_by(status: 'available')
  car.update_columns(user_id: user_id, status: 'in_use')

  car
end

但是这个解决方案在选择可用汽车和更新其状态之间造成了差距，然后当应用程序必须处理相对大量的请求时就会出现困境，因此同时很少有请求尝试预订同一辆车。

为了降低这种风险，我在同一个 SQL 查询中查找并更新可用的汽车。此外，我还随机排列可用汽车列表顺序以进一步减少它。为了随机化结果，我不使用 ORDER BY RAND() LIMIT 1 ，因为据我所知，它会为每条记录生成随机 id，对其进行排序，并且仅当将结果限制为指定数字时 - 1。这不是高效，因为 cars 表预计将来会有大量记录(100k+)。

所以我想出了这个解决方案:

def reserve_car(user_id)
  sql = <<-SQL
    UPDATE
      cars AS r0,
      (
        SELECT
          r1.id
        FROM
          cars AS r1
          JOIN (
            SELECT
              (
                RAND() * (
                  SELECT
                    MAX(id)
                  FROM
                    cars
                )
              ) AS id
          ) AS r2
        WHERE
          r1.status = 'available'
          AND r1.id >= r2.id
        LIMIT
          1
      ) AS r3
    SET
      r0.status = 'in_use',
      r0.user_id = #{ActiveRecord::Base.connection.quote(user_id)}
    WHERE
      r0.id = r3.id
  SQL

  updates = ActiveRecord::Base.connection.exec_update(sql)

  car = Car.find_by(user_id: user_id, status: 'in_use')

  if car.present?
    car
  else
    raise "Failed to reserve car. Updates: #{updates}"
  end
end

但我经常收到异常“无法预订汽车。更新 0”，尽管事实上我知道有很多可用的汽车。

有什么问题吗？也许有人可以提出更好的解决方案？

谢谢🙏

Answer 1

您没有说明正在使用哪个数据库，但现在大多数主要数据库都有一种方法来执行更新并从更新的行返回数据

例如在oracle中:

update car
set in_use = 1
where in_use = 0 and id = (select min(id) from car where in_use = 0)
returning id into car_id_that_was_set_in_use 

参数 car_id_that_was_set_in_use 将包含预订的汽车的 ID

作为建立锁定并且不会长时间保持事务打开的单个操作，它不应导致任何争用

MySQL 似乎是一个值得注意的异常(exception) - 我没有发现任何迹象表明 MySQL 支持诸如 UPDATE..RETURNING 之类的内容，但还有其他解决方法，例如 innodb 支持 SELECT ..FOR UPDATE 允许您锁定要更新的记录，以及涉及可能类似于以下内容的变量的黑客:

 UPDATE car SET
     in_use = 1, id = @affectedid := id
 WHERE in_use = 0 AND id=(SELECT MIN(id) FROM car WHERE in_use = 0);
 SELECT @affectedid;

尽管测试一下；我从未使用过这个并根据 SO 答案改编

---

您也可以对前端应用程序进行编码以对其进行循环，尽管效率较低。这是伪代码，因为我不做 ruby​​:

int rowsupdated = 0
int potentialId = -1
while(rowsupdated = 0 and potentialId is not null) {
  potentialId = sql_scalar("SELECT MIN(id) FROM car WHERE in_use = 0")
  rowsupdated = sql_nonquery("UPDATE car SET in_use = 1 WHERE in_use = 0 and id = " + potentialId)
}
if(potentialId is null)
  //there was no car to book, we tried them all - potentialId would only be null if there were no more cars
else
  //potentialId now contains the id of the car we booked

while 循环将继续执行，直到预订一辆车。这是幼稚且低效的，但它提出了一个重要的观点，也适用于前面的查询

更新查询必须引用与我们仍然期望的相同的 in_use 值

你不能选择一个id，然后直接设置in_use = 1，而不考虑其他人是否在我们空闲时设置了in_use=1。这称为乐观并发 - 您希望没有其他人更改您要编辑的行上的数据，但您包含了您知道的有关该行的所有数据，因此如果其他人确实更改了该行，则更新将失败并返回 0记录已更新。如果其他人在我们空闲时设置 in_use = 1，则更新会失败，并且我们将 in_use 仍为 0 作为更新的条件，以便更新成功。如果更新返回 0，我们可以假设其他人在我们之前更改了该行。然后，知道我们没有得到该行，我们尝试另一行(或决定覆盖/合并/接受其他人的更改)