** Edited **
从目标表中选择
From 13.2.9.8。 FROM 子句中的子查询:
FROM 子句中的子查询可以返回标量、列、行或表。 FROM 子句中的子查询不能是关联子查询,除非在 JOIN 操作的 ON 子句中使用。
所以,是的,您可以执行上述查询。
问题
这里确实有两个问题。存在并发性,或者确保没有其他人从我们脚下更改数据。这是通过锁定来处理的。处理新值与旧值的实际修改是通过派生表来处理的。
Locking
对于上面的查询,使用 InnoDB,MySQL 首先执行 SELECT,并分别在表中的每一行上获取读(共享)锁。如果 SELECT 语句中有 WHERE 子句,则只有您选择的记录才会被锁定,其中范围也会导致任何间隙被锁定。
A 读锁防止任何其他查询获取写锁,因此在读锁定时无法从其他地方更新记录。
然后,MySQL 分别获取表中每条记录的写(独占)锁。如果 UPDATE 语句中有 WHERE 子句,则只有特定记录会被写锁定,同样,如果 WHERE 子句选择了一个范围,那么您将锁定一个范围。
任何在前一个 SELECT 中具有读锁的记录都会自动升级为写锁。
A 写锁防止其他查询获得读锁或写锁。
您可以使用Innotop要看到这一点,可以通过在锁定模式下运行它,启动一个事务,执行查询(但不要提交它),然后您将在 Innotop 中看到锁。此外,您可以在没有 Innotop 的情况下查看详细信息SHOW ENGINE INNODB STATUS
.
僵局
如果同时运行两个实例,您的查询很容易出现死锁。如果查询 A 获得了读锁,那么查询 B 也获得了读锁,则查询 A 必须等待查询 B 的读锁释放才能获取写锁。但是,查询 B 在完成之前不会释放读锁,并且除非它可以获得写锁,否则它不会完成。查询 A 和查询 B 陷入僵局,因此出现死锁。
因此,您可能希望执行显式表锁,既可以避免大量记录锁(占用内存并影响性能),又可以避免死锁。
另一种方法是在内部 SELECT 上使用 SELECT ... FOR UPDATE。这从所有行上的写锁开始,而不是从读取开始并升级它们。
派生表
对于内部 SELECT,MySQL 创建一个派生临时表。派生表是 MySQL 自动创建的临时表中数据的实际非索引副本(与您显式创建并可以添加索引的临时表相反)。
由于 MySQL 使用派生表,因此这是您在问题中引用的临时旧值。换句话说,这里没有魔法。 MySQL 的处理方式与您在其他地方执行的方式一样,只是使用临时值。
您可以通过对 UPDATE 语句执行 EXPLAIN 来查看派生表(MySQL 5.6+ 中支持)。