Создает ли MySQL InnoDB согласованные снимки для SELECT в нескольких таблицах с UNION, когда уровень изоляции READ COMMITTED

Рассмотрим две таблицы, подобные этой:

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 |
 -------------------

Это всего лишь простые примеры... в реальной системе обе таблицы имеют значительно больше столбцов и значительно больше строк (более 10 тыс. строк в текущей и более 1 млн строк в истории).

Клиентское приложение непрерывно (несколько раз в секунду) вставляет новые строки в текущую таблицу и «перемещает» более старые существующие строки из текущей в историю (удаление/вставка в рамках одной транзакции).

Не блокируя клиента в этом действии, нам нужно получить согласованную сумму значений для каждого отдела в двух таблицах.

С уровнем изоляции транзакций, установленным на REPEATABLE READ, мы могли бы просто сделать:

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

с последующим

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

и сложите два набора результатов вместе. НО каждый запрос будет блокировать вставки в соответствующую таблицу.

Изменение уровня изоляции на READ COMMITTED и выполнение тех же двух SQL-запросов позволит избежать блокировки вставок, но теперь существует риск двойного учета записей при перемещении из текущего в историю во время запроса (поскольку каждый SELECT создает свой собственный снимок).

Тогда вот вопрос... что происходит с уровнем изоляции READ COMMITTED, если я делаю UNION:

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

Будет ли MySQL генерировать непротиворечивый снимок обеих таблиц одновременно (тем самым устраняя риск двойного подсчета) или он по-прежнему сначала сделает снимок одной таблицы, а через некоторое время сделает снимок второй?


mysql innodb transaction-isolation isolation-level
person David B    schedule 10.12.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я еще не нашел убедительной документации, чтобы ответить на мой вопрос, поэтому вместо этого я попытался доказать это. Хотя это и не доказательство в научном смысле, мои выводы свидетельствуют о том, что согласованный моментальный снимок создается для всех таблиц в запросе 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();

В другом сеансе с той же базой данных мы теперь можем опробовать вариант запроса UNION ALL в моем исходном сообщении.

Я изменил его, чтобы (а) замедлить выполнение и (б) вернуть простой набор результатов (две строки), который указывает, были ли какие-либо записи, «перемещенные» во время выполнения запроса, пропущены или подсчитаны дважды.

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)

Я все еще был бы рад услышать любую авторитетную информацию по этому поводу.

person David B    schedule 11.12.2014