更新/分配外键到预先存在的表行并且不覆盖它[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; 

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

我需要确保的是，在创建订单后，我获得可用的促销代码并更新其记录以反映它已被分配。

我不是 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);

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

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

此外，您还需要确保您的服务器满足事务要求< /a>.

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

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