MVCC в InnoDB: undo, ReadView, history list length и gap-блокировки

В InnoDB после UPDATE в таблице лежит одна версия строки, последняя; прежние хранятся в undo-журнале и собираются обратно по цепочке указателей. Это противоположность PostgreSQL, где все версии лежат в самой странице (MVCC в PostgreSQL). Цена другая: таблица не пухнет, зато растёт undo, пока кто-то держит старый снимок, а блокирующее чтение в REPEATABLE READ запирает промежутки между ключами. Поведение уровней изоляции описано в статье про транзакции.

DBA / 01koddo

Цепочка undo и снимок B с границей 1843

Опыт 1: снимок читает версию из undo

Поднимаем сервер. После инициализации mysqld в контейнере один раз перезапускается, поэтому дождись, пока SELECT 1 проходит дважды подряд: база, созданная до перезапуска, пропадёт.

docker run -d --name dba-innodb-mvcc-undo-and-locks -e MYSQL_ROOT_PASSWORD=pg mysql:8.4
docker exec -it dba-innodb-mvcc-undo-and-locks mysql -uroot -ppg

В сессии A создаём таблицу и смотрим версию и уровень изоляции:

CREATE DATABASE lab; USE lab;
CREATE TABLE acc (id int PRIMARY KEY, balance int) ENGINE=InnoDB;
INSERT INTO acc VALUES (1, 500);
SELECT @@version, @@transaction_isolation;

+-----------+-------------------------+
| @@version | @@transaction_isolation |
+-----------+-------------------------+
| 8.4.13    | REPEATABLE-READ         |
+-----------+-------------------------+

Версия сходится с тегом mysql-8.4.13, на который ссылаются цитаты ниже. Запоминаем счётчик транзакций, потом в сессии B открываем снимок и читаем строку:

docker exec dba-innodb-mvcc-undo-and-locks mysql -uroot -ppg -e "SHOW ENGINE INNODB STATUS\G" | grep "Trx id counter"

-- сессия B
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT * FROM acc;

Trx id counter 1843

+----+---------+
| id | balance |
+----+---------+
|  1 |     500 |
+----+---------+

Пока B не закрыта, сессия A трижды меняет строку, каждый UPDATE в своей транзакции с автокоммитом, и читаем счётчик снова:

-- сессия A
UPDATE acc SET balance = 450;
UPDATE acc SET balance = 400;
UPDATE acc SET balance = 350;

Trx id counter 1851

-- сессия B
SELECT * FROM acc;

+----+---------+
| id | balance |
+----+---------+
|  1 |     500 |
+----+---------+

В таблице теперь 350, а B видит 500. Счётчик ушёл с 1843 на 1851: все транзакции A стартовали уже после снимка, их номера не меньше 1843. Версии 450 и 400 лежат только в undo, версия 500 тоже, и B дошла до неё по цепочке указателей отката.

ReadView: границы снимка и обход цепочки

Снимок в InnoDB называется ReadView. Его границы выставляет ReadView::prepare (тег mysql-8.4.13, read0read.cc):

  m_low_limit_id = trx_sys_get_next_trx_id_or_no();

  ut_a(m_low_limit_no <= m_low_limit_id);

  if (!trx_sys->rw_trx_ids.empty()) {
    copy_trx_ids(trx_sys->rw_trx_ids);
  } else {
    m_ids.clear();
  }

  /* The first active transaction has the smallest id. */
  m_up_limit_id = !m_ids.empty() ? m_ids.front() : m_low_limit_id;

m_low_limit_id — следующий ещё не выданный номер, это и есть «Trx id counter» из опыта: 1843. m_ids — идущие на этот момент транзакции на запись; m_up_limit_id — наименьшая из них. Видимость решает changes_visible (read0types.h):

    if (id < m_up_limit_id || id == m_creator_trx_id) {
      return (true);
    }

    check_trx_id_sanity(id, name);

    if (id >= m_low_limit_id) {
      return (false);

Номер меньше m_up_limit_id или собственный — виден; не меньше m_low_limit_id — не виден; между ними решает поиск в отсортированном m_ids. Для B с границей 1843 все три UPDATE попали под второе условие. Дальше row_vers_build_for_consistent_read (row0vers.cc) вызывает trx_undo_prev_version_build, получает предыдущую версию, берёт её DB_TRX_ID и снова спрашивает changes_visible. Цикл идёт, пока версия не окажется видимой. У PostgreSQL проверка та же по смыслу (xmin:xmax:xip, см. разбор там), но версии ищутся в странице, а здесь на каждый шаг назад нужно прочитать запись undo.

Опыт 2: миллион UPDATE одной строки и history list

Какую цену платит база за старый снимок? Заводим «горячую» строку и процедуру, которая обновляет её в цикле и фиксирует каждые 1000 обновлений:

CREATE TABLE hot (id int PRIMARY KEY, n int) ENGINE=InnoDB;
INSERT INTO hot VALUES (1, 0);
DELIMITER //
CREATE PROCEDURE bump(k int) BEGIN
  DECLARE i int DEFAULT 0; SET autocommit=0;
  WHILE i < k DO
    UPDATE hot SET n = n + 1 WHERE id = 1; SET i = i + 1;
    IF i % 1000 = 0 THEN COMMIT; END IF;
  END WHILE; COMMIT;
END//
DELIMITER ;

Измеряем три величины: длину списка истории (trx_rseg_history_len), размер undo-пространств и размер файла таблицы.

SELECT (SELECT count FROM information_schema.innodb_metrics WHERE name='trx_rseg_history_len') AS history_len,
 (SELECT sum(file_size)/1048576 FROM information_schema.innodb_tablespaces WHERE name LIKE 'innodb_undo%') AS undo_mib,
 (SELECT file_size/1024 FROM information_schema.innodb_tablespaces WHERE name='lab/hot') AS hot_kib;

Сначала миллион обновлений без чужих снимков (CALL bump(1000000)), затем замер сразу и через 40 секунд:

-- до
| history_len | undo_mib | hot_kib  |
|           5 |  32.0000 | 112.0000 |
-- сразу после 1 млн UPDATE
|          69 |  32.0000 | 112.0000 |
-- через 40 секунд
|           0 |  32.0000 | 112.0000 |

Теперь то же самое, но сессия B перед миллионом открыла START TRANSACTION WITH CONSISTENT SNAPSHOT и прочитала n (в этот момент в таблице лежало 1000000):

-- B читает n до и после обновлений
| b_reads       |  1000000 |
| b_reads_after |  1000000 |
-- таблица после ещё одного миллиона
| table_now | 2000000 |
| history_len | undo_mib | hot_kib  |
|        1004 |  96.0000 | 112.0000 |

B видит старое значение, а таблица осталась в 112 КиБ: версии не копились в ней. Копились они в undo: 32 МиБ превратились в 96, прирост 64 МиБ на миллион обновлений, около 67 байт на UPDATE. История выросла до 1004, потому что считает не строки, а транзакции с undo: миллион обновлений в процедуре зафиксирован 1000 раз плюс несколько чужих. Без снимка purge чистил на ходу: в момент замера список был 69.

Закрываем B (COMMIT) и опрашиваем каждые 10 секунд:

 | 1004 | 96.0000 | 112.0000 |
 | 1005 | 96.0000 | 112.0000 |
 | 1005 | 96.0000 | 112.0000 |
 | 1005 | 96.0000 | 112.0000 |
 | 1005 | 96.0000 | 112.0000 |
 | 1005 | 96.0000 | 112.0000 |
 | 0 | 96.0000 | 112.0000 |

Список истории стал 0 между 50 и 60 секундами после закрытия снимка, не сразу. Файлы undo остались по 96 МиБ в сумме. Усечение включено (innodb_undo_log_truncate = 1), но порог innodb_max_undo_log_size равен 1 073 741 824 Б, а файлы ему не достигли, и в опыте они не сжались. Долгая транзакция в InnoDB оставляет след в файловой системе, а не только в метриках.

Опыт 3: gap-блокировка и дедлок на INSERT

В REPEATABLE READ блокирующее чтение запирает не только найденные строки, но и промежутки между ключами. Берём таблицу с ключами 10 и 20, сессия A читает пустой диапазон, B пробует вставить 15 внутри:

CREATE TABLE g (id int PRIMARY KEY, v char(1)) ENGINE=InnoDB;
INSERT INTO g VALUES (10,'a'), (20,'b');
-- сессия A
BEGIN; SELECT * FROM g WHERE id BETWEEN 11 AND 19 FOR UPDATE;
-- сессия B
SET innodb_lock_wait_timeout=8; INSERT INTO g VALUES (15,'x');

Блокировки смотрим из третьей сессии:

SELECT engine_transaction_id AS trx, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks WHERE object_name='g' ORDER BY engine_transaction_id, lock_mode;

| trx  | index_name | lock_type | lock_mode              | lock_status | lock_data |
| 5844 | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
| 5844 | PRIMARY    | RECORD    | X,GAP                  | GRANTED     | 20        |
| 5845 | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
| 5845 | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 20        |

Строки с 11 по 19 нет, но A держит X,GAP на записи 20: запирается промежуток перед ней. B встаёт в очередь с INSERT_INTENTION, а через 8 секунд получает ERROR 1205 (HY000): Lock wait timeout exceeded. Условие ставит lock_rec_insert_check_and_lock (lock0lock.cc):

      const ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION;

      const auto conflicting =
          lock_rec_other_has_conflicting(type_mode, block, heap_no, trx);

Вставка просит у следующей записи блокировку промежутка с намерением вставки. Конфликтует она с чужим GAP на той же записи, поэтому ждёт.

Из этого получается классический дедлок: обе сессии проверяют «такой строки нет» блокирующим чтением, обе получают X,GAP на запись 20, а потом обе вставляют 15. Gap-блокировки между собой не конфликтуют, а вставка конфликтует с чужой:

-- обе сессии
BEGIN; SELECT * FROM g WHERE id = 15 FOR UPDATE;
-- затем по очереди
INSERT INTO g VALUES (15,'a');   -- сессия A: ждёт
INSERT INTO g VALUES (15,'b');   -- сессия B: дедлок

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

В SHOW ENGINE INNODB STATUS раздел LATEST DETECTED DEADLOCK называет обе стороны (строки RECORD LOCKS сокращены до trx id):

*** (1) TRANSACTION:
TRANSACTION 1815, ACTIVE 3 sec inserting
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... trx id 1815 lock_mode X locks gap before rec
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... trx id 1815 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 1816, ACTIVE 2 sec inserting
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... trx id 1816 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... trx id 1816 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

Транзакция (1) — сессия A, (2) — сессия B. Каждая транзакция держит промежуток и ждёт, пока другая его отпустит. Откатилась вторая, вставка первой прошла: в таблице остались 10, 15 (a) и 20. Проверки «если строки нет, вставь» на блокирующем чтении превращаются здесь в генератор дедлоков. Если нужна уникальность, ставь UNIQUE и лови ошибку дубликата, а блокирующее чтение пустого диапазона убери.

Что из этого следует

• Старый снимок раздувает undo, а не таблицу. Таблица hot не выросла ни на килобайт, зато undo прибавил 64 МиБ. Где в PostgreSQL ответом будет bloat и VACUUM (статья про VACUUM), в InnoDB это history list и размер undo-пространств.
• Длинная транзакция опасна даже без записи. B только читала; удерживала снимок она одна. Транзакцию с давним trx_started в information_schema.innodb_trx ищи раньше, чем смотреть на нагрузку.
• Метрика считает транзакции, а не строки. 1004 при миллионе обновлений: число зависит от того, как часто приложение фиксирует.
• Чтение старой версии идёт по шагам. Каждый шаг назад — вызов trx_undo_prev_version_build и новая проверка changes_visible, а длину цепочки задаёт число UPDATE после снимка.
• Gap-блокировки следуют из уровня изоляции. Блокирующее чтение пустого диапазона запирает вставки в него. Какие блокировки ставит PostgreSQL, описано в статье про блокировки.

Чек-лист для InnoDB

1. Следи за trx_rseg_history_len (information_schema.innodb_metrics) и строкой History list length в SHOW ENGINE INNODB STATUS: рост при стабильной нагрузке означает чужой старый снимок.
2. Старого читателя ищи так: SELECT trx_id, trx_state, trx_started FROM information_schema.innodb_trx ORDER BY trx_started.
3. Размер undo смотри в information_schema.innodb_tablespaces (innodb_undo_%) и сверяй с innodb_max_undo_log_size: до порога файлы не сжимаются.
4. Не используй SELECT ... FOR UPDATE для проверки «строки нет»; цепочки gap-блокировок видны в performance_schema.data_locks по X,GAP и INSERT_INTENTION.
5. Убери контейнер: docker rm -f dba-innodb-mvcc-undo-and-locks.