mysql - 当隔离级别为 READ COMMITTED 时，MySQL InnoDB 是否使用 UNION 在多个表上为 SELECT 创建一致的快照

Question

考虑两个这样的表:

TABLE: current
 -------------------
| id | dept | value |
|----|------|-------|
|   4|    A |    20 |
|   5|    B |    15 |
|   6|    A |    25 |
 -------------------

TABLE: history
 -------------------
| id | dept | value |
|----|------|-------|
|   1|    A |    10 |
|   2|    C |    10 |
|   3|    B |    20 |
 -------------------

这些只是简单的示例...在实际系统中，两个表都有更多的列和更多的行(当前超过 10k 行，历史超过 100 万行)。

客户端应用程序不断(每秒几次)将新行插入当前表，并将较旧的现有行从当前表“移动”到历史记录(在单个事务中删除/插入)。

在不阻塞此事件中的客户端的情况下，我们需要在两个表中对每个部门的值进行一致的总和。

将事务隔离级别设置为 REPEATABLE READ 我们可以这样做:

SELECT dept, sum(value) FROM current GROUP BY dept;

其次是

SELECT dept, sum(value) FROM history GROUP BY dept;

并将两组结果相加。但是每个查询都会阻止对其各自表的插入。

将隔离级别更改为 READ COMMITTED 并执行相同的两个 SQL 将避免阻塞插入，但现在如果在我们查询时从当前移动到历史记录，则存在条目被重复计算的风险(因为每个 SELECT 都会创建自己的快照).

那么问题来了......如果我执行 UNION，隔离级别 READ COMMITTED 会发生什么:

SELECT dept, sum(value) FROM current GROUP BY dept
UNION ALL
SELECT dept, sum(value) FROM history GROUP BY dept;

MySQL 会同时为两个表生成一致的快照(从而消除重复计算的风险)还是会先对一个表进行快照，然后稍后对第二个进行快照？

Answer 1

我还没有找到任何决定性的文件来回答我的问题，所以我开始尝试证明它。虽然不是科学意义上的证明，但我的发现表明为 UNION 查询中的所有表创建了一个一致的快照。

这是我所做的。

创建表

DROP TABLE IF EXISTS `current`;

CREATE TABLE IF NOT EXISTS `current` (
  `id` BIGINT NOT NULL COMMENT 'Unique numerical ID.',
  `dept` BIGINT NOT NULL COMMENT 'Department',
  `value` BIGINT NOT NULL COMMENT 'Value',
  PRIMARY KEY (`id`));


DROP TABLE IF EXISTS `history`;

CREATE TABLE IF NOT EXISTS `history` (
  `id` BIGINT NOT NULL COMMENT 'Unique numerical ID.',
  `dept` BIGINT NOT NULL COMMENT 'Department',
  `value` BIGINT NOT NULL COMMENT 'Value',
  PRIMARY KEY (`id`));

创建一个过程，在当前表中设置 10 个条目 (id = 0, .. 9)，然后进入一个紧密循环，将 1 个新行插入当前表并将最旧的行从当前表“移动”到历史记录。每次迭代都在一个事务中执行，因此当前表保持稳定在 10 行，而历史表增长很快。在任何时间点 min(current.id) = max(history.id) + 1

DROP PROCEDURE IF EXISTS `idLoop`;

DELIMITER $$
CREATE PROCEDURE `idLoop`()
BEGIN

DECLARE n bigint;

-- Populate initial 10 rows in current table if not already there
SELECT IFNULL(MAX(id), -1) + 1 INTO n from current;
START TRANSACTION;
WHILE n < 10 DO
  INSERT INTO current VALUES (n, n % 10, n % 1000);
  SET n = n + 1;
END WHILE;
COMMIT;

-- In tight loop, insert new row and 'move' oldest current row to history
WHILE n < 10000000 DO
  START TRANSACTION;
  -- Insert new row to current
  INSERT INTO current values(n,  n % 10, n % 1000);
  -- Move oldest row from current to history
  INSERT INTO history SELECT * FROM current WHERE id = (n - 10);
  DELETE FROM current where id = (n - 10);
  COMMIT;
  SET n = n + 1;
END WHILE;

END$$
DELIMITER ;

开始运行这个过程(这个调用在一段时间内不会返回——这是故意的)

call idLoop();

在同一数据库的另一个 session 中，我们现在可以在我的原始帖子中尝试 UNION ALL 查询的变体。

我已将其修改为 (a) 减慢执行速度，并且 (b) 返回一个简单的结果集(两行)，该结果集指示在查询运行时是否有任何“移动”的条目被遗漏或重复计算。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT 'HST' AS src, MAX(id) AS idx, COUNT(*) AS cnt, SUM(value) FROM history WHERE dept IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
UNION ALL
SELECT 'CRT' AS src, MIN(id) AS idx, COUNT(*) AS cnt, SUM(value) FROM current WHERE dept IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);

sum(value) 和 where dept in (...) 只是为了增加查询的工作量并降低查询速度。

积极结果的指示是两个 idx 值是否相邻，如下所示:

+-----+--------+--------+------------+
| src | idx    | cnt    | SUM(value) |
+-----+--------+--------+------------+
| HST | 625874 | 625875 |  312569875 |
| CRT | 625875 |     10 |       8795 |
+-----+--------+--------+------------+
2 rows in set (1.43 sec)

我仍然很高兴听到有关这方面的任何权威信息。