mysql - MySQL:仅锁定一行以供选择

Question

我有一种“彩票分配”问题。来找我的每个用户都需要获取一个唯一代码，该代码取自可用代码表。每个用户必须获得一个且只有一个代码，并且每个代码都必须提供给一个且仅一个用户。将提供给用户的代码是表上第一个未标记为“已使用”的代码。

此问题与此非常相似：mysql - Locking rows for select query?

但有一个很大的不同：到目前为止，我已经测量到最多18个用户/秒的访问，因此我只需要锁定每个用户一行（我正在为每个用户处理一行）。锁定整个表可能是一个问题。

我读过“ READ COMMITTED”可能有用：https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

但这是我第一次做这样的事情，但是我对如何进行准确的工作以及如何进行热测试感到迷茫，后来又在将代码投入生产之前模拟了这个巨大的负载。

Answer 1

您永远不需要锁定行。

处理唯一性时，请放置UNIQUE约束。总是。也不例外，永远。

关键不是性能而是数据完整性。那就是数据库的用途-包含有效数据，以便您可以创建投影，计划等。

快速实现并不难。不要为了性能而牺牲数据完整性。

免责声明：我没有在这里测试任何SQL。我发布的所有内容均作为教育示例。如果您复制粘贴它可能会起作用，但我不保证此处显示的任何SQL的语法正确性。

问题


  每个用户必须获得一个且只有一个代码，并且必须给出每个代码
  一位和唯一一位用户


这定义了唯一性。有一个代码。一个用户只能有一个代码。让我们设计模型。

解决方案


我们将代码保存到一个表中
我们将使用联结表将用户与代码链接。使用了连接表，因此我们不必alter个用户表即可使用此方法
代码将具有唯一值（由UNIQUE约束处理）
结点表每位用户只允许使用1个代码，但是如果以后需要，同一用户仍可以为其分配其他代码。


使用这种方法：


我们将收到有关重复条目的错误
我们将处理这些错误


为什么我们会出错？因为我们将遇到独特的约束，所以数据库将简单地拒绝该条目。但这就是我们想要的，这就是我们将得到的。锁定行不是这些类型问题的解决方案。慢一点您需要在某个时候解锁该行。容易出现并发问题。数据库处理唯一性的能力比您或任何其他程序员可以做的更好。因此，我们将使用数据库的机制。

代码表

代码表将包含代码。您没有定义代码的外观，所以我假设它是一个字符串。我选择varchar(255)是因为我喜欢它。由于代码必须是唯一的，因此我将为自己提供一些触发条件和独特的约束，以帮助自己。我将使用sha1散列代码的值，将其保存到binary(20)并使该binary列唯一。

我从中得到的是：
-我的代码现在看起来像任何东西，可以是任何类型的字符的组合
-我的索引长度总是固定的，所以我可以放一个unique约束，不用担心
-我可以使用哈希搜索代码，但可以向客户显示更友好的版本
-由于unique约束，数据库中只能有一个代码值

CREATE TABLE codes (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code_value VARCHAR(255) NOT NULL, -- this is the "friendly" value that customers get
    code_hash binary(16) default null, -- this is the hash of the above value
    is_used tinyint not null default '0', -- a small helper field when querying for which codes are "free"
    primary key(id),
    unique(code_hash)
) ENGINE = InnoDB;

处理哈希的触发器，因此我们不必提供值：

DELIMITER $$

CREATE
TRIGGER `codes_before_insert` BEFORE INSERT
ON `codes`
    FOR EACH ROW BEGIN
        SET NEW.code_hash = UNHEX(SHA1(NEW.code_value));
END$$

DELIMITER ;

连接表


使用它，因此我们不必更改任何现有的与用户相关的表
允许每个用户使用一个代码
允许一个用户使用多个代码
包含外键以强制数据完整性


桌子：

CREATE TABLE user2code (
    id int unsigned not null auto_increment,
    user_id int unsigned not null,
    code_id int unsigned not null,
    unique(code_id), -- this part allows for only 1 code to be used, ever
    foreign key(user_id) references users(id) on delete cascade,
    foreign key(code_id) references codes(id) on delete cascade
) ENGINE = InnoDB;

我们将在联结表上放置一个实用程序触发器。使用代码时，我们将更新 codes表并将 is_used设置为 1。这样做是为了在空闲/占用代码之间进行更轻松的导航。我们可以通过将 JOIN表放在 user2code表上来完成此操作，但是我们想提高性能。

DELIMITER $$

CREATE
TRIGGER `user2code_after_insert` AFTER INSERT
ON `user2code`
    FOR EACH ROW BEGIN
        UPDATE `codes` SET is_used = 1 WHERE id = NEW.code_id;
END$$

DELIMITER ;

如何使用

INSERT INTO user2code 
(user_id, code_id) 
VALUES 
(1, (SELECT id FROM codes WHERE is_used = 0 LIMIT 1));

结果：


成功的话就是这样！
如果发生错误，可以通过重复事务来处理错误。您可以重复几次（3-4）次交易，如果交易失败，请让用户稍后重试。这与UX有关，在您的情况下我一无所知。


关键是要知道失败的交易还不错。那是数据库告诉我们的方法，嘿，您不能这样做，这就是原因。

放置锁很危险-不能保证唯一性。只有唯一的约束。

祝好运！