MySQL 何时在更新 InnoDB 表时锁定一行？
如果我有这个多重更新查询，
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作为RDBMS，InnoDB以其行级锁定系统作为两个表的引擎，
MySQL是否锁定用户表中的行尽管不必更新该行的名称字段？
If I have this multiupdate query
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
Let's suppose the row 10 in user table already has the name 'same_name_i_already_had', so it shouldn't be updated.
On the other hand, the row in user_profile table has a different age, so MySQL should update it.
Assuming MySQL as RDBMS and InnoDB with its row level locking system as the engine of both tables,
Does MySQL lock the row in user table in spite of not having to update the name field of that row?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
它确实锁定了
user中的行。您可以使用优秀的 innotop 工具来验证这一点。例如，我在运行 MySQL 5.5 的测试虚拟机上创建了表 user 和 user_profile，并执行了上面列出的步骤。这是输出：
It does lock the row in
user. You can verify this using the excellent innotop tool.For example, I created tables user and user_profile on my test VM running MySQL 5.5, and I performed the steps I list above. Here's the output:
几乎可以肯定，无论如何它都会锁定该行。我不知道有什么简单的字段会首先检查更改。锁定、写入和解锁更加简单快捷。如果在锁定之前进行了检查，那么就会出现竞争条件：锁完全避免的情况。
Almost surely it locks the row regardless. I don't know of any simple fields which are checked for a change first. It is easier and faster to just lock, write, and unlock. If there were a check before locking, then there is a race condition: something which a lock completely avoids.