Создадим таблицу - она будет представлять светофор (пешеходный, с двумя лампочками):
=> CREATE DATABASE mvcc_isolation;
CREATE DATABASE
=> \c mvcc_isolation
You are now connected to database "mvcc_isolation" as user "postgres".
=> CREATE TABLE lights(id serial, lamp text, state text);
CREATE TABLE
=> INSERT INTO lights(lamp,state) VALUES ('red', 'on'), ('green', 'off');
INSERT 0 2
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | off (2 rows)
У нас будут открыты два сеанса, в которых мы будем одновременно запускать разные транзакции.
Первый способ установить уровень изоляции - команда SET TRANSACTION, выполненная в начале транзакции:
=> BEGIN;
BEGIN
=> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET
Проверить текущий уровень можно, посмотрев значение параметра:
=> SHOW transaction_isolation;
transaction_isolation ----------------------- read uncommitted (1 row)
В чем дело? Ведь PostgreSQL не поддерживает чтение "грязных" данных.
Попробуем прочитать "грязные" данные. В первой транзакции гасим красный свет:
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
UPDATE 1
Во втором сеансе откроем еще одну транзакцию с тем же уровнем Read Uncommitted. Можно указать уровень прямо в команде BEGIN:
student$ psql mvcc_isolation
=> BEGIN ISOLATION LEVEL READ UNCOMMITTED;
BEGIN
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | off (2 rows)
Вторая транзакция не видит незафиксированных изменений. Установить уровень Read Uncommitted можно, но работает он так же, как и Read Committed.
Отменим изменение.
=> ROLLBACK;
ROLLBACK
=> ROLLBACK;
ROLLBACK
Уровень Read Committed используется по умолчанию:
=> SHOW default_transaction_isolation;
default_transaction_isolation ------------------------------- read committed (1 row)
=> BEGIN;
BEGIN
=> SHOW transaction_isolation;
transaction_isolation ----------------------- read committed (1 row)
Мы проверили, что транзакция не видит незафиксированных изменений. Посмотрим, что будет при фиксации.
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
UPDATE 1
=> BEGIN;
BEGIN
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | off (2 rows)
=> COMMIT;
COMMIT
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | off 2 | green | off (2 rows)
=> COMMIT;
COMMIT
Итак, в режиме Read Committed операторы одной транзакции видят зафиксированные изменения других транзакций.
Можно ли увидеть изменения, зафиксированные в процессе выполнения одного оператора? Проверим.
=> BEGIN;
BEGIN
=> BEGIN;
BEGIN
Сейчас все лампочки погашены. Запустим долгий запрос и, пока он работает, включим свет во втором сеансе:
=> SELECT pg_sleep(2), * FROM lights ORDER BY id;
=> UPDATE lights SET state = 'on';
UPDATE 2
=> COMMIT;
COMMIT
pg_sleep | id | lamp | state
----------+----+-------+-------
| 1 | red | off
| 2 | green | off
(2 rows)
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | on (2 rows)
=> COMMIT;
COMMIT
Итак, если во время выполнения оператора другая транзакция успела зафиксировать изменения, то они не будут видны. Оператор видит данные в таком состоянии, в котором они находились на момент начала его выполнения.
Что происходит при попытке изменения одной и той же строки двумя транзакциями? Сейчас все лампочки включены.
=> BEGIN;
BEGIN
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
UPDATE 1
=> BEGIN;
BEGIN
=> UPDATE lights SET state = CASE WHEN state = 'on' THEN 'off' ELSE 'on' END WHERE lamp = 'red';
Вторая транзакция ждет завершения первой.
=> COMMIT;
COMMIT
UPDATE 1
=> COMMIT;
COMMIT
Но какой будет результат? Вторая транзакция "щелкает переключателем", и результат зависит от значения, от которого она будет отталкиваться.
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | on (2 rows)
С одной стороны, команда во второй транзакции не должна видеть изменений, сделанных после начала ее выполнения. Но с другой - она не должна потерять изменения, зафиксированные другими транзакциями. Поэтому после снятия блокировки она перечитывает строку, которую пытается обновить.
В итоге, первая транзакция выключает свет, а вторая снова включает его.
Такой результат интуитивно кажется правильным, но достигается он за счет того, что транзакция может увидеть несогласованные данные: часть - на один момент времени, часть - на другой.
Убедимся в отсутствии аномалии неповторяемого чтения.
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT * FROM lights WHERE lamp = 'red';
id | lamp | state ----+------+------- 1 | red | on (1 row)
=> UPDATE lights SET state = 'off' WHERE lamp = 'red' RETURNING *;
id | lamp | state ----+------+------- 1 | red | off (1 row) UPDATE 1
=> SELECT * FROM lights WHERE lamp = 'red';
id | lamp | state ----+------+------- 1 | red | on (1 row)
Повторное чтение измененной строки возвращает первоначальное значение.
Фантомные записи также не должны быть видны. Проверим это.
=> SELECT * FROM lights WHERE state = 'on';
id | lamp | state ----+-------+------- 2 | green | on 1 | red | on (2 rows)
=> INSERT INTO lights(lamp,state) VALUES ('yellow', 'on') RETURNING *;
id | lamp | state ----+--------+------- 3 | yellow | on (1 row) INSERT 0 1
=> SELECT * FROM lights WHERE state = 'on';
id | lamp | state ----+-------+------- 2 | green | on 1 | red | on (2 rows)
Действительно, транзакция не видит добавленной записи, удовлетворяющей первоначальному условию. Уровень изоляции Repeatable Read в PostgreSQL более строгий, чем того требует стандарт SQL.
Уберем желтую лампочку.
=> COMMIT;
COMMIT
=> DELETE FROM lights WHERE lamp = 'yellow';
DELETE 1
Необходимость все время видеть ровно те же данные, что и в самом начале, не позволяет перечитывать измененную строку в случае обновления.
Воспроизведем тот же пример, который мы видели на уровне изоляции Read Committed. Сейчас все лампочки включены.
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
UPDATE 1
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> UPDATE lights SET state = CASE WHEN state = 'on' THEN 'off' ELSE 'on' END WHERE lamp = 'red';
Вторая транзакция ждет завершения первой.
=> COMMIT;
COMMIT
ERROR: could not serialize access due to concurrent update
=> COMMIT;
ROLLBACK
Получаем ошибку. Строка была изменена; обновить неактуальную версию невозможно (это будет потерянным изменением, что недопустимо), а увидеть актуальную версию тоже невозможно (это нарушило бы изоляцию).
Можно ли быть уверенным в том, что следующая команда включит все лампочки?
=> UPDATE lights SET state = 'on' WHERE state = 'off';
Можно ли быть уверенным в том, что следующая команда выключит все лампочки?
=> UPDATE lights SET state = 'off' WHERE state = 'on';
Если одна транзакция включает лампочки, а другая - выключает, в каком состоянии останется таблица после одновременного выполнения двух этих транзакций?
Проверим. Начальное состояние:
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | off 2 | green | on (2 rows)
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> UPDATE lights SET state = 'on' WHERE state = 'off';
UPDATE 1
Первая транзакция включила красную лампочку, и теперь все огни горят.
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | on (2 rows)
Вторая транзакция не видит этих, еще не зафиксированных, изменений и считает, что красный свет выключен. Поэтому она выключает зеленый и для нее все огни погашены.
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> UPDATE lights SET state = 'off' WHERE state = 'on';
UPDATE 1
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | off 2 | green | off (2 rows)
Теперь обе транзакции фиксируют свои изменения...
=> COMMIT;
COMMIT
=> COMMIT;
COMMIT
И оказывается, что одна лампочка включена, а другая - выключена.
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | off (2 rows)
Это пример аномалии конкурентного доступа, которая возможна даже если нет "грязного", неповторяющегося и фантомного чтения.
На этом уровне транзакции могут положиться на то, что на их работу никто не повлияет.
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
=> UPDATE lights SET state = 'on' WHERE state = 'off';
UPDATE 1
Первая транзакция включила зеленую лампочку, все огни горят.
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | on 2 | green | on (2 rows)
Вторая транзакция не видит зафиксированных изменений: для нее красный свет еще горит, и она выключает его.
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
=> UPDATE lights SET state = 'off' WHERE state = 'on';
UPDATE 1
=> SELECT * FROM lights ORDER BY id;
id | lamp | state ----+-------+------- 1 | red | off 2 | green | off (2 rows)
Теперь обе транзакции фиксируют свои изменения...
=> COMMIT;
COMMIT
=> COMMIT;
ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried.
И вторая транзакция получает ошибку.
Действительно, при последовательном выполнении транзакций допустимо два результата:
"Промежуточный" вариант получить невозможно, поэтому выполнение одной из транзакций завершается ошибкой.
Важный момент: чтобы уровень Serializable работал корректно, на этом уровне должны работать все транзакции. Если смешивать транзакции разных уровней, фактически Serializable не будет работать.
Конец демонстрации.