MySQL Select...用于更新索引存在并发问题

这是我上一个问题的后续问题（您可以跳过它，因为我在这篇文章中解释了该问题）：
MySQL InnoDB SELECT...LIMIT 1 FOR UPDATE 与 UPDATE ...LIMIT 1 https://stackoverflow.com/questions/14039853/mysql-innodb-select-limit-1-for-update-vs-update-limit-1

环境：

• Glassfish 上的 JSF 2.1
• JPA 2.0 EclipseLink 和 JTA
• MySQL 5.5 InnoDB 引擎

我有一张桌子：

CREATE TABLE v_ext (
  v_id INT NOT NULL AUTO_INCREMENT,
  product_id INT NOT NULL,
  code VARCHAR(20),
  username VARCHAR(30),
  PRIMARY KEY (v_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

它填充了 20,000 条记录，如下所示 (product_id所有记录均为 54，code是随机生成的且唯一的，用户名设置为 NULL）：

v_id     product_id    code                  username
-----------------------------------------------------
1        54            '20 alphanumerical'   NULL
...
20,000   54            '20 alphanumerical'   NULL

当用户购买产品54时，他从该表中获得一个代码。如果用户多次购买，则每次都会获得一个代码（对用户名没有唯一限制）。因为我正在准备一项高强度的活动，所以我想确保：

• 不会发生并发/死锁
• 性能不受所需锁定机制的影响

从SO问题（参见上面的链接）我发现做这样的查询更快：

START TRANSACTION;
SELECT v_id FROM v_ext WHERE username IS NULL LIMIT 1 FOR UPDATE;
// Use result for next query
UPDATE v_ext SET username=xxx WHERE v_id=...;
COMMIT;

但是，我仅在使用索引时才发现死锁问题username柱子。我认为添加索引会有助于加快一点速度，但它会在大约 19,970 条记录后产生死锁（实际上在这个行数上非常一致）。是否有一个原因？我不明白。谢谢。

从纯粹的理论角度来看，看起来您没有锁定正确的行（第一个语句中的条件与更新语句中的条件不同；此外，您只锁定了一行，因为LIMIT 1，而您稍后可能会更新更多行）。

尝试这个：

START TRANSACTION;
SELECT v_id FROM v_ext WHERE username IS NULL AND v_id=yyy FOR UPDATE;
UPDATE v_ext SET username=xxx WHERE v_id=yyy;
COMMIT;

[edit]

至于你陷入僵局的原因，这是可能的答案（从手册 http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html):

如果你没有适合你的语句的索引并且MySQL必须扫描 整个表来处理语句，表的每一行 被锁定 (...)

如果没有索引，则SELECT ... FOR UPDATE语句可能会锁定整个表，而使用索引时，它只会锁定某些行。由于您没有在第一个语句中锁定正确的行，因此在第二个语句期间会获取额外的锁。

显然，如果整个表被锁定（即没有索引），则不会发生死锁。 在第二个设置中肯定会发生死锁。