Статья основана на 29 видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.
Данные в СУБД должны соответствовать принципам ACID – атомарность, консистентность, изоляция и надежность.
Эти свойства данных поддерживаются транзакциями, которые переводят базу данных из одного консистентного состояния в другое, при этом не мешая друг другу. Механизм может быть реализован через конкурентный доступ.
Конкурентный доступ
Конкурентный доступ имеет сложные алгоритмы реализации, а также является затратной по времени и памяти операцией.
Требования к конкурентному доступу к данным:
- Гарантия сохранности данных
- Сохранение данных всех завершенных транзакций
- Отмена данных всех незавершенных транзакций
- Корректная работа и в нормальном, и в аварийных режимах.
Конкурентный доступ реализуется через MVCC (Multi-Version Concurrency Control) – сложный и важный механизм
Проблема, которую решает MVCC, связана с параллельной работой большого количества сессий, которые модифицируют данные. MVCC нужен для того, чтобы пользователи не мешали друг другу, а данные сохраняли свою целостность и надежность.
Реализация MVCC
В большинстве баз данных (Oracle Database, MySQL, InndoDB и др.) механизм реализован стандартно: существует сегмент Undo (сегмент отката), в который записываются изменения и удаляются при применении rollback.
- В нем хранятся «противодействия». То есть, если в таблицу был внесен INSERT, то в сегмент отката вносится DELETE и наоборот. При использовании UPDATE в Undo вносится предыдущее значение данных.
- Чтобы обеспечить корректную работу Undo, измененные блоки данных помечаются как грязные. Они помещаются в redo log buffer (в нем хранится информация о последних изменениях, чтобы их можно было восстановить после сбоя системы). В redo журнал записываются не только инструкции изменений (redo), но и их противодействия (undo).
- Информация о завершенных транзакциях постепенно удаляется
- При использовании rollback данные возвращаются обратно в таблицу – это лишняя работа, на которую тратятся ресурсы
- Если количество данных для вставки/удаления/обновления превышает размер сегмента Undo, то произойдет переполнение и откат транзакции

MVCC в PostgreSQL
В PostgreSQL механизм устроен иначе.
На каждое изменение создается новая строка. При отмене транзакции новая строка помечается на удаление, а при успешном завершении транзакции – старая. Данные не удаляются в процессе обработки транзакций. Старые записи удаляются позже автоматически с помощью вакуума, который очищает место для последующей записи туда уже новых транзакций. Именно из-за этого нет кластерного индекса – упорядоченных по этому индексу данных в таблице на диске (только в виде одноразовой операции), так как данные лежат не по порядку.
В каждой таблице есть скрытые колонки:
- xmin – идентификатор транзакции, которая создала данную версию записи
- xmax – идентификатор транзакции, которая удалила данную версию записи
- cmin – порядковый номер команды в транзакции, добавившей запись
- cmax – номер команды в транзакции, удалившей запись
Механизм работы для разных операций:
- INSERT – добавляется новая запись с xmin=txid_current() и xmax=0
- DELETE – в старой версии записи проставляется xmax=txid_current()
- UPDATE в старой версии записи проставляется xmax=txid_current(), то есть делается delete, затем добавляется новая запись с xmin=txid_current() и xmax=0, то есть делается INSERT
Если происходит отмена транзакции, то xmax так и остается с номером пытавшейся изменить его транзакции, проставляется бит отмены транзакции xmax_aborted.
Подробнее про версии записей можно прочитать в статьях:
The Internals of PostgreSQL : Chapter 5 Concurrency Control
Дополнительные атрибуты строки:
- infomask – атрибут, содержащий ряд битов, определяющих свойства данной версии.
- xmin_commited, xmin_aborted, xmax_commited, xmax_aborted – биты отвечающие за commit и rollback транзакции, также участвуют при заморозке (freeze)
- ctid – атрибут, являющийся ссылкой на следующую, версию той же строки. У актуальной, версии строки ctid ссылается на саму эту версию. Номера ctid имеют вид (x,y), где x — номер страницы, а y —порядковый номер в странице.
Отмена транзакции в PostgreSQL выполняется так же быстро, как и фиксация (проставляется бит фиксации или отмены), в отличие от других языков, где нужно возвращать данные из Undo.
При ROLLBACK отката изменений не происходит: все, что транзакция успела изменить в данных, остается без изменений, но помечается другим битом.
При обращении к данным будет проверен статус и в версию строки будет установлен бит xmax_aborted. Сам номер xmax при этом останется, но будет проигнорирован.
Практика
Выведем номер текущей транзакции:
SELECT txid_current();

Если вызвать команду повторно, то номер изменится, так как включен autocommit (все команды оборачиваются в отдельные транзакции):

Выведем текущий уровень изоляции транзакций:
SHOW transaction isolation level;

Создадим таблицу и вставим в неё значения:
DROP TABLE IF EXISTS test;
CREATE TABLE test(
i int
);
INSERT INTO test VALUES
(100),
(200);

Далее в транзакции добавим ещё две строки:
BEGIN;
INSERT INTO test VALUES (300);
INSERT INTO test VALUES (400);
COMMIT;

Выведем служебную информацию:
SELECT relname,
n_live_tup,
n_dead_tup,
trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
last_autovacuum FROM pg_stat_user_tables
WHERE relname = 'test';

4 живых строчки, нет мертвых (так как не было запросов, помечающих на удаление)
Обновим таблицу:
UPDATE test
SET i = 301
WHERE i = 300;

Проверим служебную информацию теперь:
SELECT relname,
n_live_tup,
n_dead_tup,
trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
last_autovacuum FROM pg_stat_user_tables
WHERE relname = 'test';

Осталось 4 живых строки, добавилась 1 мертвая. 20% информации – мертвая.
Посмотрим атрибуты строк:
SELECT xmin,
xmax,
cmin,
cmax,
ctid
FROM test;

Видим, какая транзакция какие данные меняла.
Для просмотра более подробной информации создадим расширение:
CREATE EXTENSION pageinspect;
Выведем служебные данные, которые обычно скрыты PostgreSQL:
SELECT lp as tuple,
t_xmin,
t_xmax,
t_field3 as t_cid,
t_ctid
FROM heap_page_items(get_raw_page('test',0));

Видим мертвую строчку, в которой есть ссылка на более актуальную версию (0, 5) – нулевая страница, пятая строка.
При отключении autovacuum абсолютно все изменения будут сохраняться, что невозможно в других СУБД (если вручную не запустим vacuum). Однако напрямую достать данные из мертвой записи нельзя, так как они хранятся в бинарном виде. Придётся восстанавливать побитно:
SELECT lp as tuple,
t_xmin,
t_xmax,
t_field3 as t_cid,
t_ctid,
get_byte(t_data, 0)::bigint as byte0,
get_byte(t_data, 1) as byte1,
get_byte(t_data, 2) as byte2,
get_byte(t_data, 3) as byte3,
get_byte(t_data, 0)::bigint + get_byte(t_data, 1)*2^8 + get_byte(t_data, 2)*2^16 + get_byte(t_data, 3)*2^24 as value
FROM heap_page_items(get_raw_page('test',0));

В колонке value видим значение мертвой строки. Текст восстановить гораздо сложнее.
Выведем инфомаску:
SELECT '(0,'||lp||')' AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin as xmin,
t_xmax as xmax,
(t_infomask & 256) > 0 AS xmin_commited,
(t_infomask & 512) > 0 AS xmin_aborted,
(t_infomask & 1024) > 0 AS xmax_commited,
(t_infomask & 2048) > 0 AS xmax_aborted,
t_ctid
FROM heap_page_items(get_raw_page('test',0))

Выполним отмену транзакции:
BEGIN;
UPDATE test set i = 201 WHERE i = 200;
ROLLBACK;
Проверим журнал записей:
SELECT lp as tuple,
t_xmin,
t_xmax,
t_field3 as t_cid,
t_ctid,
get_byte(t_data, 0)::bigint as byte0,
get_byte(t_data, 1) as byte1,
get_byte(t_data, 2) as byte2,
get_byte(t_data, 3) as byte3,
get_byte(t_data, 0)::bigint + get_byte(t_data, 1)2^8 + get_byte(t_data, 2)2^16 + get_byte(t_data, 3)*2^24 as value
FROM heap_page_items(get_raw_page('test',0));

Транзакция отменена и сохранена.
Презентация к статье здесь.
30 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.
Добавить комментарий