MySQL何时在更新InnoDB表时锁定行？

Question

如果我有这个多重更新查询

UPDATE user u
INNER JOIN user_profile up ON up.user_id = u.id
SET u.name = 'same_name_i_already_had', up.profile.age = 25
WHERE u.id = 10

假设用户表中第10行已经有了名称“same_name_i_already_had”，因此不应更新它。

另一方面，user_profile表中的行具有不同的年龄，因此MySQL应该更新它。

假设MySQL是关系数据库管理系统，并且使用InnoDB作为两个表的引擎，具有其行级锁定系统，

MySQL是否会锁定用户表中的行，尽管不必更新该行的名称字段？

- Emilio Nicolás

Answer 1

它确实锁定了 user 行。您可以使用优秀的 innotop 工具来验证这一点。

• 运行 innotop 并按 "L" 键显示 InnoDB 锁屏幕。
• 打开另一个会话，登录 MySQL 并 START TRANSACTION。
• 执行您展示的 UPDATE，但不要立即 COMMIT。
• 在 innotop 屏幕中查看锁。

例如，在我的测试 VM 上运行 MySQL 5.5，我创建了表 user 和 user_profile，并执行了我上面列出的步骤。下面是输出：

[RO] Locks (? for help) localhost, 08:34.568, InnoDB 10s :-), 0.10 QPS, 2/0/0 con/run/cac thds, 5.5.

__________________________________________ InnoDB Locks __________________________________________
ID  Type    Waiting  Wait   Active  Mode  DB    Table         Index    Ins Intent  Special        
 2  TABLE         0  00:00   02:35  IX    test  user                            0                 
 2  RECORD        0  00:00   02:35  X     test  user          PRIMARY           0  rec but not gap
 2  TABLE         0  00:00   02:35  IX    test  user_profile                    0                 
 2  RECORD        0  00:00   02:35  X     test  user_profile  PRIMARY           0  rec but not gap

- Bill Karwin

---

非常好的答案，但我不理解展示的表锁。您可以解释一下innotop是什么吗？ - Emilio Nicolás

“IX”模式是一种表级锁，意味着“我打算锁定此表中的某些行。”它不会阻止同一表上的行级锁，但会阻止表级锁。请参见http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html以获取InnoDB锁定的完整说明，以及http://innotop.googlecode.com/svn/html/manual.html以获取使用innotop的完整说明。 - Bill Karwin

我收到过的最好的答案。非常感谢！ - Emilio Nicolás

有人如何“执行您展示的UPDATE，但尚未提交”。我是MySQL的新手，正在尝试分析一些更新。 - Peter A

@PeterA，请使用语句“BEGIN;”开始一个事务，以避免每个语句的自动提交。该事务将一直保持打开状态，直到您明确使用“COMMIT;”或“ROLLBACK;”。有关更多信息，请阅读http://dev.mysql.com/doc/refman/5.7/en/commit.html。 - Bill Karwin

Answer 2

几乎可以肯定它会锁定该行。我不知道是否有任何简单的字段在锁定之前检查更改。锁定、写入和解锁更容易、更快速。如果在锁定之前进行检查，则存在竞争条件：这是锁完全避免的情况。

- wallyk