INSERT/UPDATE 查询中的多个 MySQL 子查询的原子性? [英] Atomicity multiple MySQL subqueries in an INSERT/UPDATE query?

问题描述

我打算编写以下查询:

INSERT INTO summary (user_id, total_points, count_operations)
SELECT 
   15 AS user_id,
   (SELECT SUM(points) FROM operations WHERE user_id = 15) AS total_points,
   (SELECT COUNT(*) FROM operations WHERE user_id = 15) AS count_operations
ON DUPLICATE KEY UPDATE
total_points = VALUES(total_points), 
count_operations = VALUES(count_operations);

整个语句是原子的吗?即 MySQL(带有 MyISAM 引擎)是否在内部锁定 operations 表?

Is the whole statement atomic? i.e. does MySQL (with MyISAM engine) internally locks the operations table?

MySQL 是否有可能按顺序执行两个子查询，这可能导致某些情况下(如果在该时间范围内添加新操作)total_points 和 count_operations 会不一致吗?

Is there a possibility that MySQL would execute the two subsqueries sequentially which could result in some cases (if a new operation is added within that timeframe) that the total_points and count_operations would be inconsistent?

推荐答案

MyISAM 仍然使用 表级锁.

MyISAM still uses table-level locks.

这些存储引擎通过总是请求所有需要的东西来避免死锁在查询开始时立即锁定并始终锁定表的顺序相同.权衡是这种策略减少了并发；其他想要修改表的会话必须等待直到当前 DML 语句完成.

These storage engines avoid deadlocks by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The tradeoff is that this strategy reduces concurrency; other sessions that want to modify the table must wait until the current DML statement finishes.

因此，想要更新您正在使用的表的其他进程必须等到您的事务完成.如果它在表操作"上放置了一个读锁，那么所有后续的写锁都会进入一个队列并等待.

So other processes that want to update the tables you're using have to wait until your transaction is finished. If it puts a read lock on the table "operations", all subsequent write locks go into a queue and wait.

这篇关于INSERT/UPDATE 查询中的多个 MySQL 子查询的原子性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！