mysql - 更新/分配一个外键给预先存在的表行而不是覆盖它 [mysql]

Question

我有一个名为 promotion_codes 的表

CREATE TABLE promotion_codes (
  id int(10) UNSIGNED NOT NULL auto_increment,
  created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  code varchar(255) NOT NULL,
  order_id int(10) UNSIGNED NULL DEFAULT NULL,
  allocated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

此表预先填充了可用代码，这些代码将分配给满足特定条件的订单。

我需要确保在创建 ORDER 后，我获得可用的促销代码并更新其记录以反射(reflect)它已被分配。

我不是 100% 确定如何在同时请求进入时不抓取同一条记录。

我曾尝试在选择期间锁定行并在更新期间锁定行 - 两者似乎仍然允许第二次(同时)尝试获取相同的记录 - 这是我想要避免的

UPDATE promotion_code 
SET allocated_at = "' . $db_now . '", order_id = ' . $donation->id . ' 
WHERE order_id IS NULL LIMIT 1

Answer 1

您可以添加第二个表格，其中包含所有使用过的代码。因此，您可以在分配表中使用唯一约束来确保一个代码不会被分配两次。

CREATE TABLE `used_codes` (`usage` INTEGER PRIMARY KEY auto_increment,
 `id` INTEGER NOT NULL UNIQ, -- This makes sure, that there are no two assignments of one code
  allocated_at datetime NOT NULL);

在used_codes表中添加一个使用过的代码的ID，然后查询使用过的代码。当这两个操作在一个事务中时，当第二次尝试使用相同的代码时，整个事务将失败。

下面的代码我没有测试过，你可以自行调整。

您还需要确保您的服务器符合 requirements for transactions .

-- There are changes which have to be atomic, so don't use autocommit
SET autocommit = 0;
BEGIN TRANSACTION
INSERT INTO `used_codes` (`id`, `allocated_at`) VALUES
  (SELECT `id` FROM `promotion_codes`
    WHERE NOT `id` in (SELECT `id` FROM `used_codes`)
   LIMIT 1), now());
SELECT `code` FROM `promotion_codes` WHERE `id` =
  -- You might need to adjust the extraction of insertion ID, since
  -- I don't know if parallel running transactions can see the maximum
  -- their maximum IDs. But there should be a way to extract the last assigned
  -- ID within this transaction.
  (SELECT `id` FROM `used_codes` HAVING `usage` = max(`usage`));
COMMIT

如果交易成功，您可以使用返回的代码。如果有多个进程运行以使用相同的代码，则只有其中一个成功，而其余的则失败并出现关于重复行的插入错误。在您的软件中，您需要区分重复行错误和其他错误，并重新执行重复错误的语句。