mysql - 在用户注册过程中正确锁定行

Question

我对使用 InnoDB 还很陌生，同时还使用了它们的锁定机制，我遇到了一个我不太确定的问题。

假设您正在创建一个通用的用户注册系统，因此用户尝试注册并且您执行类似这样的操作来检查他们想要的用户名是否可用:

SELECT COUNT(*) FROM users WHERE username='$username';

然后您发现用户名可用，所以您继续执行此操作:

INSERT INTO users(username,email) VALUES('$username','$email');

现在，在极不可能但可能发生的情况下，某些其他进程会在您将它们插入数据库之前检查相同的用户名并且他们得到了 ok - 显然我们不希望最终有两个用户使用相同的用户名，对吧!？

所以我想到了使用FOR UPDATE 锁来防止这种情况，比如:

autocommit=0;
SELECT COUNT(*) FROM users WHERE username='$username" FOR UPDATE;
INSERT INTO users(username,email) VALUES('$username','$email');
commit();

但是我后来意识到，至少从我的理解来看这是行不通的，因为它只会锁定 SELECT 找到的 行；并且因为它是一个 COUNT 它不引用任何特定的行，即使你将它更改为不使用 COUNT 它仍然不会引用任何行作为用户名找不到。

如果上面的方法是对的，那行不通，我唯一能想到的就是表级锁定，以防止其他进程读取或写入表，但我知道表级锁很糟糕，应该避免了。

有没有更好的方法在不使用表级锁的情况下完成我想做的事情？

Answer 1

您可以尝试使用 UNIQUE index在 username 列。这样，如果您尝试 INSERT username 的重复值，MySQL 将阻止语句执行并抛出重复错误。您的程序可以捕获此错误(有关详细信息，请参阅您的客户端库的文档)并要求用户使用不同的用户名。您可以通过 MySQL 返回给您的客户端库的错误代码来区分重复的用户名和其他错误消息。

同时确保您 use parameterized queries而不是将用户名字符串直接替换到 SQL 语句中。否则，恶意用户将能够 inject arbitrary SQL commands .