为什么MySQL不允许对同一张表同时进行查询和更新?
问题
下面这个看起来没有问题的SQL语句却运行不了
UPDATE `tb1` AS outer_tb1 SET cnt = (
SELECT count(*) FROM `tb1` AS inner_tb1
WHERE inner_tb1.type = outer_tb1.type
);
如果清楚MySQL是如何执行查询的,就知道为什么会这样子了。
因为执行SELECT count(*) FROM tb1 AS inner_tb1 WHERE inner_tb1.type = outer_tb1.type
的时候会获得读锁(共享锁),一旦数据表被加上读锁,其他请求可以对该表再次增加读锁,但是不能增加写锁。(当一个请求在读数据时,其他请求也可以读,但是不能写,因为一旦另外一个线程写了数据,就会导致当前线程读取到的数据不是最新的了。这就是不可重复读现象)
解决
可以通过使用生成表的形式来绕过这种限制,因为MySQL只会把这个表当作一个临时表来处理。
UPDATE `tb1` INNER JOIN(
SELECT type,count(*) AS cnt FROM `tb1`
GROUP BY type
) AS outer_tb1 USING(type)
SET tb1.cnt = outer_tb1.cnt;