Изоляция транзакций по нескольким таблицам с использованием PostgreSQL MVCC
Резюме вопросов
Это вопрос о сериализуемости запросов внутри транзакции SQL.
В частности, я использую PostgreSQL. Можно предположить, что я использую самую последнюю версию PostgreSQL. Из того, что я прочитал, я считаю, что технология, используемая для поддержки того, что я пытаюсь сделать, известна как "MultiVersion Concurrency Control", или "MVCC".
Подводя итог: если у меня есть одна первичная таблица и более одной таблицы, связанной с внешним ключом, подключенной к этой первичной таблице, как я могу гарантировать это для заданного ключа в таблицах и любого количества операторов SELECT используя этот ключ внутри одной транзакции, каждая из которых выбирается из любой из связанных таблиц, я получу данные в том виде, в каком они существовали на момент начала транзакции?
Другие вопросы
Этот вопрос похож, но шире, и вопрос и ответ не касались конкретно PostgreSQL: изоляция транзакций и чтение из нескольких таблиц в SQL Server Express и SQL Server 2005
пример
Допустим, у меня есть 3 таблицы:
bricks
brickworks (primary key)
completion_time (primary key)
has_been_sold
brick_colors
brickworks (primary key, foreign key pointing to "bricks")
completion_time (primary key, foreign key pointing to "bricks")
quadrant (primary key)
color
brick_weight
brickworks (primary key, foreign key pointing to "bricks")
completion_time (primary key, foreign key pointing to "bricks")
weight
Кирпичный завод производит один кирпич за один раз. Это делает кирпичи, которые могут быть разных цветов в каждом из 4-х квадрантов.
Кто-то позже анализирует кирпичи, чтобы определить их цветовую комбинацию, и записывает результаты в таблицу brick_colors.
Кто-то еще анализирует кирпичи, чтобы определить их вес, и записывает результаты в таблицу brick_weight.
В любой момент времени существующий кирпич может иметь или не иметь записанный цвет, а может иметь или не иметь записанный вес.
Приложение существует, и это приложение получает сообщение о том, что кто-то хочет купить конкретный кирпич (уже известный на данный момент приложению по его составному ключу brickworks / complete_time).
Приложение хочет выбрать все известные свойства кирпича В ТОЧНОЕ ВРЕМЯ, КОТОРОЕ НАЧИНАЕТ ЗАПРОС.
Если в MID-TRANSACTION добавлена информация о цвете или весе, приложение НЕ хочет знать об этом.
Прикладная программа хочет выполнить SEPARATE QUERIES (не SELECT с несколькими JOIN для таблиц, связанных с внешним ключом, которые могут возвращать несколько строк из-за таблицы brick_colors).
Этот пример намеренно прост; Желание сделать это без одного SELECT с несколькими JOIN-ами было бы более ясным, если бы мой пример включал, скажем, 10 таблиц, связанных с внешним ключом, и многие или все из них могли бы возвращать несколько строк для одного и того же первичного ключа (как brick_colors в пример как у меня выше).
Попытка решения
Вот что я придумала до сих пор:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY ;
-- All this statement accomplishes is telling the database what rows should be returned from the present point-in-time in future queries within the transaction
SELECT DISTINCT true
FROM bricks b
LEFT JOIN brick_colors bc ON bc.brickworks = b.brickworks AND bc.completion_time = b.completion_time
LEFT JOIN brick_weight bw ON bw.brickworks = b.brickworks AND bw.completion_time = b.completion_time
WHERE b.brickworks = 'Brick-o-Matic' AND b.completion_time = '2017-02-01T07:35:00.000Z' ;
SELECT * FROM brick_colors WHERE b.brickworks = 'Brick-o-Matic' AND b.completion_time = '2017-02-01T07:35:00.000Z' ;
SELECT * FROM brick_weight WHERE b.brickworks = 'Brick-o-Matic' AND b.completion_time = '2017-02-01T07:35:00.000Z' ;
COMMIT ;
Просто кажется расточительным использовать этот первый SELECT с соединениями исключительно в целях обеспечения сериализуемости.
Есть ли другой способ сделать это?
Рекомендации
Управление параллелизмом PostgreSQL
1 ответ
Это суть вашего вопроса:
как я могу гарантировать, что для...... любого количества операторов SELECT..... внутри одной транзакции....... я получу данные такими, какими они были на момент начала транзакции?
Это именно то, что Repeatable Read Isolation Level гарантирует:
Уровень изоляции Repeatable Read видит только данные, зафиксированные до начала транзакции; он никогда не видит ни незафиксированные данные, ни изменения, зафиксированные во время выполнения транзакций параллельными транзакциями. (Тем не менее, запрос видит результаты предыдущих обновлений, выполненных в его собственной транзакции, даже если они еще не зафиксированы.) Это более надежная гарантия, чем требуется стандартом SQL для этого уровня изоляции, и предотвращает все явления описано в таблице 13-1. Как упомянуто выше, это специально разрешено стандартом, который описывает только минимальную защиту, которую должен обеспечивать каждый уровень изоляции.
Этот уровень отличается от Read Committed тем, что запрос в повторяемой транзакции чтения видит моментальный снимок на момент начала транзакции, а не на начало текущего запроса внутри транзакции. Таким образом, последовательные команды SELECT в одной транзакции видят одни и те же данные, т. Е. Они не видят изменений, внесенных другими транзакциями, зафиксированными после запуска их собственной транзакции.
Практический пример - допустим, у нас есть две простые таблицы:
CREATE TABLE t1( x int );
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2( y int );
INSERT INTO t2 VALUES (1),(2),(3);
Ряд таблиц, их структуры, первичные ключи, внешние ключи и т. Д. Здесь не важны.
Позволяет открыть первый сеанс, запустить повторяемый уровень изоляции для чтения и выполнить два простых и отдельных оператора SELECT:
test=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
test=# SELECT * FROM t1;
x
---
1
2
3
(3 wiersze)
test=# SELECT * FROM t2;
y
---
1
2
3
(3 wiersze)
Обратите внимание, что START TRANSACTION Команда автоматически отключает режим автоматической фиксации в сеансе.
Теперь в другом сеансе (с включенным режимом автоматической фиксации по умолчанию) вставьте несколько записей в t1:
test2=# INSERT INTO t1 VALUES(10),(11);
Новые значения были вставлены и автоматически зафиксированы (потому что автокоммит включен).
Теперь вернитесь к первому сеансу и снова запустите SELECT: test = # select * from t1;
x
---
1
2
3
(3 wiersze)
Как вы видите, session1 (с активной повторяемой транзакцией чтения) не видит изменений, зафиксированных после начала перехода.
Давайте сделаем тот же эксперимент с таблицей t2 - перейти ко второму сеансу и выпустить:
test2=# DELETE FROM t2 WHERE y = 2;
DELETE 1
Теперь вернитесь к первому сеансу и снова запустите SELECT:
test=# SELECT * FROM t2;
y
---
1
2
3
(3 wiersze)
Как видите, опять же, session1 (с активной повторяемой транзакцией чтения) не видит изменений, зафиксированных после начала перехода.
А теперь в сеансе 1 завершите транзакцию, выдав команду COMMIT, а затем SELECT:
test=# SELECT * FROM t1;
x
---
1
2
3
(3 wiersze)
test=# SELECT * FROM t2;
y
---
1
2
3
(3 wiersze)
test=# COMMIT;
COMMIT
test=# select * from t1;
x
----
1
2
3
10
11
(5 wierszy)
test=# select * from t2;
y
---
1
3
(2 wiersze)
Как вы видите, когда повторяемая транзакция чтения запущена и активна, вы можете запускать множество отдельных операторов выбора несколько раз, и все эти операторы выбора видят тот же стабильный снимок данных, что и на момент начала транзакции, относительно любых принятых данных. в других сессиях.