MySQL InnoDB锁定只受影响的行？

Question

我有一个包含用户的InnoDB表，像这样：

+--------------+-----------------------+
| user_id      | name                  |
+--------------+-----------------------+
| 1            | Bob                   |
| 1            | Marry                 |
| 2            | Bob                   |
| 1            | John                  |
| 3            | Bob                   |
| 2            | Marry                 |
+--------------+-----------------------+

“每次插入时，我会将user_id增加1，因此例如Bob的下一个user_id将是4。”

“我使用以下查询来执行此操作：”

INSERT INTO users (user_id, name)
SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';

现在我需要确保两个不同的用户不会同时添加一个“Bob”。我不希望有两个用户ID为4的Bobs。每个Bob的用户ID必须不同。

当运行上述插入查询时，是否可以编写和更新锁定所有Bob的行？我不能锁定整个表，因为许多其他用户仍需要完全访问其行。此外，所有行应始终可读。我该如何做到这一点？

- Chad

Answer 1

为了解决你问题的第一部分，需要在user_id和name上创建唯一索引。

alter table `users` add unique index ak_user_id_name(user_id,name);

这将防止重复的用户ID和名称记录。

- txyoji

---

但是在user_id行中可能会有多个1，但每个1都属于不同的用户。就像可能会有多个Bob，但每个Bob都有不同的user_id。 - Chad

这并不能防止那个问题。 它是由user_id和name组成的复合键。 它可以防止重复的user_id/name组合被输入。 因此，1/bob、1/sue、2/bob都是可以的。再输入另一个1/bob将会导致数据库错误。 - txyoji

Answer 2

这个问题的答案与这个是一样的。InnoDB为了确保基于语句的二进制日志记录的安全，会锁定比您预期更多的行。 解决方案：使用基于行的二进制日志记录和读取已提交隔离级别。

- Morgan Tocker

Answer 3

假设使用InnoDB引擎，在选择行时可以使用SELECT ... LOCK IN SHARE MODE来锁定这些行。

- Eric Petroelje

---

据我所知，仅使用 LOCK IN SHARED MODE 是不够的，您必须 {{LOCK TABLE}} 或创建一个唯一索引，并在更新失败时重试，如 @txyoji 的回答。 - xmedeko