# Занятие 01: Архитектура / Изоляция

> Исходная страница в нативном Markdown: Архитектура / Изоляция

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dev2_01_arch_isolation.html](https://edu.postgrespro.ru/16/dev2-16/dev2_01_arch_isolation.html)
> **Archive:** [DEV2-handouts-16.zip](https://edu.postgrespro.ru/16/DEV2-handouts-16.zip)
> **Страниц PDF:** 25

## Page 1: Архитектура / Изоляция {#source-page-001}

### Авторские права

© Postgres Professional, 2017–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Транзакции и их свойства
- Уровни изоляции в стандарте SQL
- Особенности реализации PostgreSQL

## Page 3: Транзакции и их свойства {#source-page-003}

**Транзакция — последовательность операций,**
- которые переводят базу данных из одного корректного состояния в другое корректное
  - Согласованность
- при условии, что транзакция выполнена полностью
  - Атомарность
- и без помех со стороны других транзакций
  - Изоляция
  - атомарность: транзакция либо выполняется полностью,
  - либо не оставляет никаких следов
  - согласованность определяется ограничениями целостности
  - и семантикой приложения
  - неполная изоляция приводит к нарушению корректности (аномалиям)
  - при конкурентном выполнении транзакций

### Комментарии преподавателя

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

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

Идеальная изоляция транзакций гарантирует, что на работу приложения не окажут влияния никакие другие конкурентно выполняющиеся транзакции. Однако если ослабить требования к изоляции, то транзакция, которая корректно выполнялась в базе данных в одиночестве, может выдавать некорректные результаты при наличии других транзакций — из-за того, что при выполнении операторы разных транзакций могут чередоваться. Такие некорректные ситуации называются аномалиями.

Реализация полной изоляции — сложная задача, сопряженная с уменьшением пропускной способности системы. На практике как правило применяется именно ослабленная изоляция, и поэтому важно понимать, какие это влечет последствия.

Четвертое свойство ACID — долговечность — мы рассмотрим позже, в теме «Журналирование».

## Page 4: Read Uncommitted {#source-page-004}

**Должен предотвращать аномалии**
  - потерянные изменения:
  - транзакция может перезаписать изменения других транзакций,
  - зафиксированных после ее начала
- Не реализован в PostgreSQL
  - работает как Read Committed

### Комментарии преподавателя

Стандарт SQL допускает четыре уровня изоляции, определяемые в терминах аномалий, которые допускаются при конкурентном выполнении транзакций на этом уровне.

Самый слабый уровень — Read Uncommitted, который (как следует из названия) позволяет увидеть даже незафиксированные данные.

Согласно стандарту на этом уровне должна предотвращаться единственная аномалия:

● Потерянные изменения (lost updates). Если после начала транзакции T1 другая транзакция T2 изменила и зафиксировала строки, транзакция T1 может перезаписать эти изменения.

Этот уровень вводился как требующий наименьших накладных расходов, но PostgreSQL может без потери эффективности работать на более строгом уровне. Поэтому в PostgreSQL такой уровень не реализован и говорить о нем мы больше не будем.

## Page 5: Read Committed {#source-page-005}

**Должен предотвращать аномалии**
  - потерянные изменения
  - грязное чтение:
  - запрос видит незафиксированные изменения других транзакций
- Реализация в PostgreSQL
  - используется по умолчанию

### Комментарии преподавателя

Каждый следующий уровень изоляции строже, чем предыдущий. Поэтому уровень Read Committed должен предотвращать не только потерянные изменения, но и еще одну аномалию:

● «Грязное» чтение (dirty read). Транзакция T1 может читать строки, измененные, но еще не зафиксированные, транзакцией T2. Отмена изменений (ROLLBACK) в T2 приведет к тому, что T1 прочитает данные, которых никогда не существовало.

Существует много других аномалий, которые допускаются на уровне Read Committed. Разработчик должен всегда помнить о возможных проблемах и при необходимости вручную использовать блокировки.

В PostgreSQL (как и во многих других СУБД) именно этот уровень изоляции используется по умолчанию — как компромисс между строгостью изоляции и эффективностью.

Заметим, что в PostgreSQL на уровне Read Committed можно потерять изменения — такой пример будет показан в демонстрации.

## Page 6: Управление уровнем изоляции {#source-page-006}

Для демонстрации мы будем использовать отдельную базу данных для каждой темы.
```sql
=> CREATE DATABASE arch_isolation;
```
```sql
CREATE DATABASE
```
```sql
=> \c arch_isolation
```
```text
You are now connected to database "arch_isolation" as user "student".
```
Уровни изоляции посмотрим на примере с таблицей, представляющей состояние светофора:
```sql
=> CREATE TABLE lights(
  id integer GENERATED ALWAYS AS IDENTITY,
  lamp text,
  state text
);
```
```sql
CREATE TABLE
```
Это будет пешеходный светофор с двумя лампочками:
```sql
=> INSERT INTO lights(lamp,state) VALUES
    ('red', 'on'), ('green', 'off');
```
```sql
INSERT 0 2
```
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
```
Один способ установить уровень изоляции — команда SET TRANSACTION, выполненная в начале транзакции:
```text
=> BEGIN;
```
```text
BEGIN
```
```text
=> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
```text
SET
```
Проверить текущий уровень можно, посмотрев значение параметра:
```text
=> SHOW transaction_isolation;
```
```text
 transaction_isolation
-----------------------
 read committed
(1 row)
```
```text
=> COMMIT;
```
```text
COMMIT
```
А можно указать уровень прямо в команде BEGIN:
```text
=> BEGIN ISOLATION LEVEL READ COMMITTED;
```
```text
BEGIN
```
```text
=> COMMIT;
```
```text
COMMIT
```
По умолчанию используется уровень Read Committed:
```text
=> SHOW default_transaction_isolation;
```
```text
 default_transaction_isolation
-------------------------------
 read committed
(1 row)
```

## Page 7: Page 7 {#source-page-007}

Так что если этот параметр не менялся, можно не указывать уровень явно.
```text
=> BEGIN;
```
```text
BEGIN
```
```text
=> SHOW transaction_isolation;
```
```text
 transaction_isolation
-----------------------
 read committed
(1 row)
```
```text
=> COMMIT;
```
```text
COMMIT
```
### Read Committed и грязное чтение
Попробуем прочитать «грязные» данные. В первой транзакции гасим красный свет:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
Начнем второй сеанс.
```sh
student$ psql arch_isolation
```
В нем откроем еще одну транзакцию с тем же уровнем Read Committed.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
```
Вторая транзакция не видит незафиксированных изменений.
Отменим изменение.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
### Read Committed и чтение зафиксированных изменений
Мы проверили, что транзакция не видит незафиксированных изменений. Посмотрим, что будет при фиксации.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights ORDER BY id;
```

## Page 8: Page 8 {#source-page-008}

```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
```
Пока не видно.
```text
=> COMMIT;
```
```text
COMMIT
```
А теперь?
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)
```
```text
=> COMMIT;
```
```text
COMMIT
```
Итак, в режиме Read Committed операторы одной транзакции видят зафиксированные изменения других транзакций.
Заметьте, что при этом запрос, выполненный повторно в той же транзакции, может выдать другой результат.
Можно ли увидеть изменения, зафиксированные в процессе выполнения одного оператора? Проверим.
Сейчас обе лампочки погашены. Запустим долгий запрос и, пока он работает, включим свет во втором сеансе:
```sql
=> SELECT *, pg_sleep(2) FROM lights ORDER BY id;
```
```sql
=> UPDATE lights SET state = 'on';
```
```sql
UPDATE 2
```
```text
 id | lamp  | state | pg_sleep
----+-------+-------+----------
  1 | red   | off   |
  2 | green | off   |
(2 rows)
```
Итак, если во время выполнения оператора другая транзакция успела зафиксировать изменения, то они не будут видны. Оператор видит данные в таком состоянии, в котором они находились на момент начала его выполнения.
Однако если в запросе вызывается функция с категорией изменчивости volatile, выполняющая собственный запрос, то такой запрос внутри функции будет возвращать данные, не согласованные с данными основного запроса.
```sql
=> CREATE FUNCTION get_state(lamp text) RETURNS text
LANGUAGE sql VOLATILE
RETURN (SELECT l.state FROM lights l WHERE l.lamp = get_state.lamp);
```
```sql
CREATE FUNCTION
```
Повторим эксперимент, но теперь запрос будет использовать функцию:
```sql
=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;
```
```sql
=> UPDATE lights SET state = 'off';
```
```sql
UPDATE 2
```
```text
 id | lamp  | state | get_state | pg_sleep
----+-------+-------+-----------+----------
  1 | red   | on    | on        |
  2 | green | on    | off       |
(2 rows)
```
Правильный вариант здесь — объявить функцию с категорией изменчивости stable.
```sql
=> ALTER FUNCTION get_state STABLE;
```
```sql
ALTER FUNCTION
```

## Page 9: Page 9 {#source-page-009}

```sql
=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;
```
```sql
=> UPDATE lights SET state = 'on';
```
```sql
UPDATE 2
```
```text
 id | lamp  | state | get_state | pg_sleep
----+-------+-------+-----------+----------
  1 | red   | off   | off       |
  2 | green | off   | off       |
(2 rows)
```
Вывод: внимательно следите за категорией изменчивости функций на уровне изоляции Read Committed. Со значениями «по умолчанию» легко получить несогласованные данные.
### Read Committed и потерянные изменения
Что происходит при попытке изменения одной и той же строки двумя транзакциями? Сейчас обе лампочки включены.
Первая транзакция выключает красную лампочку, но пока не завершается.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
Вторая транзакция видит, что обе лампочки горят...
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights;
```
```text
 id | lamp  | state
----+-------+-------
  2 | green | on
  1 | red   | on
(2 rows)
```
... меняет состояние каждой лампочки...
```sql
=> UPDATE lights
SET state = CASE WHEN state = 'on' THEN 'off' ELSE 'on' END;
```
... но зависает, ожидая завершения первой.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
UPDATE 2
```
По окончании первой транзакции вторая смогла изменить данные, и мы ожидаем, что во втором сеансе обе лампочки будут выключены. Проверим.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
=> SELECT * FROM lights;
```
```text
 id | lamp  | state
----+-------+-------
  2 | green | off
  1 | red   | on
(2 rows)
```
Почему так получилось?
Команда UPDATE во второй транзакции блокирует строки таблицы по очереди. Сначала блокируется зеленая лампочка и изменяется ее состояние, а затем команда ждет снятия блокировки красной лампочки первой транзакцией.
При этом команда во второй транзакции не должна видеть изменений, сделанных после начала ее выполнения. С другой стороны, она не должна потерять изменения, зафиксированные другими транзакциями. Поэтому после снятия блокировки она перечитывает строку, которую пытается обновить.

## Page 10: Page 10 {#source-page-010}

В итоге, первая транзакция выключает красную лампочку, а вторая снова включает ее.
- В результате вторая транзакция смогла отработать успешно за счет того, что она видела несогласованные данные: часть — на один момент времени, часть — на другой.
Но если изменение выполняется не в одной команде SQL, то обновление будет потеряно.
```text
=> BEGIN;
```
```text
BEGIN
```
Первая транзакция переводит красную лампочку в мигающий режим. Строка при этом блокируется:
```sql
=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
Во второй транзакции клиент видит, что красная лампочка горит...
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT state AS old_state FROM lights WHERE lamp = 'red';
```
```text
 old_state
-----------
 on
(1 row)
```
... и выключает ее:
```text
=> -- привязка значения параметра
\bind 'off'
```
```sql
=> UPDATE lights SET state = $1 WHERE lamp = 'red';
```
Вторая транзакция ждет завершения первой.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
COMMIT
```
Первый сеанс ожидает увидеть, что красная лампочка мигает. Проверим:
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)
```
В этом случае вторая транзакция записала свои изменения «поверх» изменений первой транзакции. На уровне Read Committed сервер не может это предотвратить, поскольку команда UPDATE фактически содержит предопределенную константу.

## Page 11: Repeatable Read {#source-page-011}

**Должен предотвращать аномалии**
  - потерянные изменения
  - грязное чтение
  - неповторяющееся чтение:
  - повторное чтение строки вернет другие данные,
  - если строка была изменена и зафиксирована другой транзакцией
- Реализация в PostgreSQL
  - также не допускает фантомное чтение:
  - повторный запрос по одному и тому же условию вернет иную выборку,
  - если другая транзакция добавила и зафиксировала новые строки,
  - удовлетворяющие этому условию
  - для предотвращения аномалий транзакции могут обрываться

### Комментарии преподавателя

Согласно стандарту, в дополнение к потерянным изменениям и грязным чтениям уровень изоляции Repeatable Read должен предотвращать аномалию, которую мы видели, когда рассматривали уровень Read Committed:

● Неповторяющееся чтение (non-repeatable read). После того как транзакция T1 прочитала строку, транзакция T2 изменила или удалила эту строку и зафиксировала изменения (COMMIT). При повторном чтении этой же строки транзакция T1 видит, что строка изменена или удалена.

Реализация PostgreSQL более строгая, чем того требует стандарт, и предотвращает аномалию

● Фантомное чтение (phantom read). Транзакция T1 прочитала набор строк по некоторому условию. Затем транзакция T2 добавила строки, также удовлетворяющие этому условию. Если транзакция T1 повторит запрос, она получит другую выборку строк.

На самом деле существуют и другие аномалии (не отраженные в стандарте), которые допускаются в PostgreSQL на уровне Repeatable Read.

От уровня Read Committed этот уровень изоляции отличается и тем, что на нем транзакция может быть оборвана, чтобы не допустить аномалию (такую транзакцию надо повторять). На уровне Read Committed этого не происходит никогда — если стоит выбор между корректностью и эффективностью, предпочтение всегда отдается эффективности.

## Page 12: Repeatable Read и неповторяющееся чтение {#source-page-012}

Убедимся в отсутствии аномалии неповторяющегося чтения.
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)
```
```sql
=> UPDATE lights SET state = 'on' WHERE lamp = 'red' RETURNING *;
```
```text
 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)
```
```sql
UPDATE 1
```
Какое значение получит теперь первая транзакция?
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)
```
Повторное чтение измененной строки возвращает первоначальное значение.
```text
=> COMMIT;
```
```text
COMMIT
```
### Repeatable Read и фантомное чтение
Фантомные записи также не должны быть видны. Проверим это.
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights WHERE state = 'off';
```
```text
 id | lamp  | state
----+-------+-------
  2 | green | off
(1 row)
```
```sql
=> INSERT INTO lights(lamp,state) VALUES ('yellow', 'off')
    RETURNING *;
```
```text
 id |  lamp  | state
----+--------+-------
  3 | yellow | off
(1 row)
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM lights WHERE state = 'off';
```
```text
 id | lamp  | state
----+-------+-------
  2 | green | off
(1 row)
```
Действительно, транзакция не видит добавленной записи, удовлетворяющей первоначальному условию. Уровень изоляции Repeatable Read в PostgreSQL более строгий, чем того требует стандарт SQL.

## Page 13: Repeatable Read и потерянные изменения {#source-page-013}

```text
=> COMMIT;
```
```text
COMMIT
```
Уберем желтую лампочку.
```sql
=> DELETE FROM lights WHERE lamp = 'yellow';
```
```sql
DELETE 1
```
Необходимость все время видеть ровно те же данные, что и в самом начале, не позволяет перечитывать измененную строку в случае обновления.
Воспроизведем пример, который мы видели на уровне изоляции Read Committed. Сейчас красная лампочка включена.
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
Клиент запрашивает состояние красной лампочки...
```sql
=> SELECT state AS current_state FROM lights WHERE lamp = 'red';
```
```text
 current_state
---------------
 on
(1 row)
```
...а затем меняет его на сервере:
```text
=> -- привязка значения параметра
\bind 'off'
```
```sql
=> UPDATE lights SET state = $1 WHERE lamp = 'red';
```
Вторая транзакция ждет завершения первой.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
ERROR:  could not serialize access due to concurrent update
```
Во второй транзакции получаем ошибку. Строка была изменена; обновить неактуальную версию невозможно (это будет потерянным изменением, что недопустимо), а увидеть актуальную версию тоже невозможно (это нарушило бы уровень изоляции).
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
Таким образом, потерянное обновление на уровне Repeatable Read не допускается.
### Repeatable Read и другие аномалии
Можно ли быть уверенным в том, что следующая команда включит все лампочки?
```sql
UPDATE lights SET state = 'on' WHERE state != 'on';
```
Можно ли быть уверенным в том, что следующая команда выключит все лампочки?
```sql
UPDATE lights SET state = 'off' WHERE state != 'off';
```

## Page 14: Page 14 {#source-page-014}

Если одна транзакция включает лампочки, а другая — выключает, в каком состоянии останется таблица после одновременного выполнения двух этих транзакций?
Включим красную лампочку и проверим.
```sql
=> UPDATE lights SET state = 'on' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
Начальное состояние:
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
```
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'on' WHERE state != 'on';
```
```sql
UPDATE 1
```
Первая транзакция включила зеленую лампочку, и теперь для нее все огни горят.
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | on
(2 rows)
```
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
Вторая транзакция не видит этих, еще не зафиксированных, изменений и считает, что зеленый свет выключен. Поэтому она выключает красный и для нее все огни погашены.
```sql
=> UPDATE lights SET state = 'off' WHERE state != 'off';
```
```sql
UPDATE 1
```
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)
```
Теперь обе транзакции фиксируют свои изменения...
```text
=> COMMIT;
```
```text
COMMIT
```
```text
=> COMMIT;
```
```text
COMMIT
```
И оказывается, что...
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | on
(2 rows)
```
...одна лампочка включена, а другая — выключена.
Это пример аномалии конкурентного доступа — несогласованная запись (write skew), — которая возможна, даже если нет грязного, неповторяющегося и фантомного чтений.

## Page 15: Page 15 {#source-page-015}


## Page 16: Serializable {#source-page-016}

**Должен предотвращать любые аномалии**
  - результат конкурентного выполнения совпадает с результатом
  - выполнения транзакций в какой-либо последовательности
- Реализация в PostgreSQL
  - для предотвращения аномалий транзакции могут обрываться
  - для правильной работы уровень Serializable должен использоваться
  - всеми транзакциями
  - уровень не работает на физических репликах

### Комментарии преподавателя

Полное отсутствие каких бы то ни было аномалий достигается, если команды, выполняемые в конкурентно работающих транзакциях, приводят к такому же результату, какой получился бы в случае последовательного — одна завершилась, следующая началась — выполнения этих транзакций (при каком-нибудь порядке выполнения).

В PostgreSQL этот уровень реализован и работает, если все транзакции в системе используют уровень Serializable.

Следует учитывать, что реализация имеет определенные ограничения:

● запросы не могут выполняться на физических репликах;

● транзакции могут обрываться чаще, чем это действительно необходимо (таким образом, страдает эффективность, поскольку оборванные транзакции приходится выполнять повторно).

## Page 17: Serializable {#source-page-017}

На этом уровне транзакции могут положиться на то, что на их работу никто не повлияет.
```text
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'on' WHERE state != 'on';
```
```sql
UPDATE 1
```
Первая транзакция включила красную лампочку, для нее все огни горят.
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | on
(2 rows)
```
Вторая транзакция не видит незафиксированных изменений: для нее красный свет еще выключен, и она выключает зеленый.
```text
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE state != 'off';
```
```sql
UPDATE 1
```
Для второй транзакции обе лампочки выключены:
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)
```
Теперь обе транзакции фиксируют свои изменения...
```text
=> COMMIT;
```
```text
COMMIT
```
```text
=> COMMIT;
```
```text
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 будет вести себя, как Repeatable Read.

## Page 18: Итоги: стандарт SQL {#source-page-018}

  - неповторя-
  - потерянные
  - обновления
  - грязное
  - фантомное
  - другие
  - аномалии
  - ющееся
  - чтение
  - чтение
  - чтение
- Read Uncommitted ― да да да да
- Read Committed ― ― да да да
- Repeatable Read ― ― ― да да
- Serializable ― ― ― ― ―

### Комментарии преподавателя

Подытожим информацию.

В стандарте SQL определены четыре уровня изоляции транзакций: Read Uncommitted, Read Committed, Repeatable Read, Serializable (по умолчанию).

Таблица показывает соответствие между уровнями изоляции и теми аномалиями, которые они могут допускать.

Отметим, что для обеспечения уровня изоляции Serializable недостаточно исключить три аномалии, определенные в стандарте.

## Page 19: Итоги: PostgreSQL {#source-page-019}

  - неповторя-
  - потерянные
  - обновления
  - грязное
  - фантомное
  - другие
  - аномалии
  - ющееся
  - чтение
  - чтение
  - чтение
- Read Uncommitted
- Read Committed \* ― да да да
- Repeatable Read ― ― ― ― да
- Serializable ― ― ― ― ―
- \* предотвращается не всегда

### Комментарии преподавателя

Реальные СУБД, и PostgreSQL в том числе, не реализуют требования стандарта буквально (которые были сформулированы в те времена, когда вопросы изоляции еще не были изучены теоретиками). Названия уровней изоляции сохраняются, но реализация их строже, чем это требует стандарт.

В PostgreSQL используется изоляция на основе снимков данных (Snapshot Isolation) в сочетании с многоверсионностью. Это определяет следующие свойства:

● Грязное чтение не допускается вообще, причем без ущерба для производительности.

● Уровень Read Committed используется по умолчанию и применяется в большинстве систем. Зачастую он требует ручной установки блокировок, чтобы обеспечить корректность.

● Уровень Repeatable Read не допускает не только неповторяемое чтение, но и фантомное чтение (хотя и не обеспечивает полную изоляцию). Этот уровень удобен для отчетов, поскольку позволяет нескольким запросам видеть согласованные данные, а только читающие транзакции никогда не обрываются.

● Уровень Serializable также полностью реализован, но имеет определенные ограничения.

<https://postgrespro.ru/docs/postgresql/16/transaction-iso>

## Page 20: Практика {#source-page-020}

1. Создайте пустую таблицу. Проверьте, что на уровне изоляции Read Committed не предотвращается аномалия фантомного чтения.
- 2. Откройте транзакцию с уровнем изоляции Repeatable Read (и пока не выполняйте в ней никаких команд). В другом сеансе удалите строку из таблицы. Видна ли удаленная строка в открытой транзакции? Будет ли видна удаленная строка, если после открытия транзакции выполнить запрос к базе данных, не обращаясь к таблицам?
- 3. Убедитесь, что команда DROP TABLE транзакционна.

## Page 21: 1. Фантомное чтение на уровне Read Committed {#source-page-021}

![Source slide page 21: 1. Фантомное чтение на уровне Read Committed](images/page-021.png){#source-figure-021 caption="Source slide page 21: 1. Фантомное чтение на уровне Read Committed"}

```sql
=> CREATE DATABASE arch_isolation;
```
```sql
CREATE DATABASE
```
```sql
=> \c arch_isolation
```
```text
You are now connected to database "arch_isolation" as user "student".
```
Пустая таблица:
```sql
=> CREATE TABLE t(id integer);
```
```sql
CREATE TABLE
```
Во втором сеансе начнем транзакцию Read Committed и выполним запрос.
```sql
=> \c arch_isolation
```
```text
You are now connected to database "arch_isolation" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM t;
```
```text
 id
----
(0 rows)
```
Вставим новую строку в таблицу (изменения фиксируются).
```sql
=> INSERT INTO t VALUES (1);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 id
----
  1
(1 row)
```
Транзакция Read Committed видит добавленную строку.
Отличие от аномалии неповторяющегося чтения (при которой транзакция видит зафиксированные изменения в уже существовавших строках) состоит в том, что здесь видны строки, которых не было раньше.
```text
=> COMMIT;
```
```text
COMMIT
```
### 2. На какой момент времени видны данные?
Открываем транзакцию Repeatable Read:
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
Добавляем строку в таблицу (изменения фиксируются):
```sql
=> INSERT INTO t VALUES (2);
```
```sql
INSERT 0 1
```
Проверяем содержимое таблицы в открытой транзакции:
```sql
=> SELECT * FROM t;
```
```text
 id
----
  1
  2
(2 rows)
```

## Page 22: Page 22 {#source-page-022}

Почему видны обе строки? Дело в том, что транзакции Repeatable Read (и Serializable) видят данные на момент начала первой команды, а не на момент выполнения BEGIN.
Если сейчас добавить в таблицу еще строк, результат повторной выборки уже не изменится.
```sql
=> INSERT INTO t VALUES (3);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 id
----
  1
  2
(2 rows)
```
```text
=> COMMIT;
```
```text
COMMIT
```
### 3. Транзакционность DDL
Откроем транзакцию и удалим таблицу t.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> DROP TABLE t;
```
```sql
DROP TABLE
```
```sql
=> SELECT * FROM t;
```
```sql
ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
                      ^
```
Таблица удалена.
Теперь откатим транзакцию:
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```sql
=> SELECT * FROM t;
```
```text
 id
----
  1
  2
  3
(3 rows)
```
Удаление отменено, таблица опять существует.

## Page 23: Практика+ {#source-page-023}

1. Команда COMMIT в коде на PL/pgSQL начинает новую транзакцию. Каким будет ее уровень изоляции и есть ли способ его изменить? Исследуйте анонимные блоки и процедуры.

## Page 24: 1. Уровень изоляции в PL/pgSQL-коде {#source-page-024}

```text
=> DO $$
BEGIN
    COMMIT;
    RAISE NOTICE '%', current_setting('transaction_isolation');
END;
$$;
```
```text
NOTICE:  read committed
DO
```
Уровень изоляции новой транзакции назначается в соответствии с параметром default\_transaction\_isolation:
```text
=> SHOW default_transaction_isolation;
```
```text
 default_transaction_isolation
-------------------------------
 read committed
(1 row)
```
Сменить уровень можно командой SET TRANSACTION:
```text
=> DO $$
BEGIN
    COMMIT;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    RAISE NOTICE '%', current_setting('transaction_isolation');
END;
$$;
```
```text
NOTICE:  repeatable read
DO
```
В процедуре такой код тоже сработает, но задать уровень первоначальной транзакции не получится:
```sql
=> CREATE OR REPLACE PROCEDURE test() LANGUAGE plpgsql AS $$
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    RAISE NOTICE '%', current_setting('transaction_isolation');
END;
$$;
```
```sql
CREATE PROCEDURE
```
```text
=> CALL test();
```
```text
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"
PL/pgSQL function test() line 3 at SQL statement
```
Можно в начале блока добавить COMMIT и тем самым начать новую транзакцию:
```text
...
BEGIN
    COMMIT;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
...
```
Но более правильный вариант — задать параметр default\_transaction\_isolation:
```sql
=> CREATE OR REPLACE PROCEDURE test() LANGUAGE plpgsql AS $$
BEGIN
    RAISE NOTICE '%', current_setting('transaction_isolation');
END;
$$;
```
```sql
CREATE PROCEDURE
```
```text
=> SET default_transaction_isolation = 'serializable';
```
```text
SET
```
```text
=> CALL test();
```
```text
NOTICE:  serializable
CALL
```

## Page 25: Page 25 {#source-page-025}