问题描述
我不清楚(通过阅读MySQL文档)以下查询是否在MySQL 5.1的INNODB表上运行，是否会为数据库内部更新的每一行创建WRITE LOCK(总数为5000)或锁定其中的所有行批次.由于数据库的负担非常重，因此非常重要.
It is unclear to me (by reading MySQL docs) if the following query ran on INNODB tables on MySQL 5.1, would create WRITE LOCK for each of the rows the db updates internally (5000 in total) or LOCK all the rows in the batch. As the database has really heavy load, this is very important.
UPDATE `records`
INNER JOIN (
SELECT id, name FROM related LIMIT 0, 5000
) AS `j` ON `j`.`id` = `records`.`id`
SET `name` = `j`.`name`
我希望它是按行排列的，但是由于我不知道有什么方法可以确保做到这一点，所以我决定问一个有更深知识的人.如果不是这种情况，并且数据库将锁定集合中的所有行，请多谢我解释原因.
I'd expect it to be per row but as I do not know a way to make sure it is so, I decided to ask someone with deeper knowledge. If this is not the case and the db would LOCK all the rows in the set, I'd be thankful if you give me explanation why.
推荐答案
UPDATE正在事务中运行-这是atomic操作，这意味着如果其中一行失败(例如，由于唯一约束)它不会更新任何5000行.这是事务数据库的ACID属性之一.
The UPDATE is running in transaction - it's an atomic operation, which means that if one of the rows fails (because of unique constrain for example) it won't update any of the 5000 rows. This is one of the ACID properties of a transactional database.
因此，UPDATE在整个事务的所有行上保持锁定.否则，另一个事务可以根据行的当前值进一步更新行的值(假设更新记录的设置值=值*'2').该语句应产生不同的结果，具体取决于第一个事务是提交还是回滚.因此，它应该等待第一个事务完成所有5000次更新.
Because of this the UPDATE hold a lock on all of the rows for the entire transaction. Otherwise another transaction can further update the value of a row, based on it's current value (let's say update records set value = value * '2'). This statement should produce different result depending if the first transaction commits or rollbacks. Because of this it should wait for the first transaction to complete all 5000 updates.
如果要释放锁，只需(较小)批次进行更新.
If you want to release the locks, just do the update in (smaller) batches.
P.S. autocommit控制每个语句是否在自己的事务中发出，但不影响单个查询的执行
P.S. autocommit controls if each statement is issued in own transaction, but does not effect the execution of a single query
这篇关于使用SELECT进行更新，它将锁定每一行或所有SELECTED记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！