Транзакционные приключения плюшевой семьи медведей

Представьте: уютный магазин игрушек, которым заправляют медведи, где полки ломятся от ярких мячиков, машинок и кубиков. Но что-то пошло не так: мишки начинают толкаться, игрушки пропадают прямо из лап, а корзины странным образом заполняются несогласованными товарами. Настоящий хаос!

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

Что же они сделали? Как справились с конфликтами? И как им удалось навести порядок? Давайте разберёмся вместе, заглянув в закулисье их магазина – мир транзакций, блокировок и многоверсионности.

Часть 1. MVCC — как магазин управляет версиями игрушек

Магазин использует механизм MVCC (многоверсионный контроль параллелизма), чтобы покупатели могли одновременно рассматривать игрушки.
Идея проста: магазин создаёт "снимки" состояния полок для каждого покупателя. Если кто-то меняет игрушку, другие всё ещё видят её в том виде, в каком она была на момент их входа.

Например, если Папа взял мяч:

-- Папа берет мяч (создается новая версия строки)
BEGIN;
UPDATE toys SET stock = stock - 1 WHERE toy_name = 'мяч';

-- Мама начинает транзакцию позже, но видит старую версию
BEGIN;
SELECT stock FROM toys WHERE toy_name = 'мяч';
-- Магазин возвращает количество, актуальное для момента входа Мамы
COMMIT;

-- Папа подтверждает свою покупку
COMMIT;

Такой подход гарантирует, что все покупатели получают последовательные данные, даже если кто-то меняет игрушки.

Часть 2. Уровни изоляции: как покупатели взаимодействуют с полками

READ COMMITTED

Медвежонок Тедди решил взять с полки мяч и положить его в свою корзину. В это время другой Медведь проверяет, сколько мячей осталось на полке.

Медвежонок начинает забирать мяч:

BEGIN;
-- Тедди забирает мяч
UPDATE toys
SET stock = stock - 1
WHERE toy_name = 'мяч';
COMMIT;

Второй Медведь смотрит на остаток:

SELECT stock
FROM toys
WHERE toy_name = 'мяч';

На уровне READ COMMITTED второй Медведь увидит обновлённый остаток только после завершения транзакции Тедди. До этого он увидит старые данные.

REPEATABLE READ

Любопытный Медведь начал считать, сколько кукол на полке. Он открыл транзакцию, чтобы убедиться, что данные останутся неизменными, пока он работает, даже если другие медведи вносят изменения:

Любопытный Медведь начал считать:

BEGIN;
-- Посчитать кукол
SELECT stock
FROM toys
WHERE toy_name = 'кукла';

В это время другой медведь добавляет кукол:

BEGIN;
-- Другой медведь пополняет запас кукол
UPDATE toys
SET stock = stock + 5
WHERE toy_name = 'кукла';
COMMIT;

На уровне REPEATABLE READ Любопытный Медведь не увидит изменений до окончания своей транзакции. Его "снимок" данных остаётся неизменным.

SERIALIZABLE

Папа Медведь, главный управляющий, решил навести полный порядок. Он гарантировал, что все операции выполняются последовательно, как будто они происходят одна за другой:

Папа Медведь управляет процессом:

BEGIN;
-- Зарезервировать игрушки для специального заказа
SELECT *  
FROM toys  
WHERE toy_name = 'поезд'  
FOR UPDATE;  

-- Обновить остаток  
UPDATE toys  
SET stock = stock - 1  
WHERE toy_name = 'поезд';  
COMMIT;  

Если другой медведь пытается взять поезд, пока идёт транзакция Папы Медведя, он видит блокировку:

BEGIN;
-- Попытка зарезервировать тот же поезд
SELECT *  
FROM toys  
WHERE toy_name = 'поезд'  
FOR UPDATE; -- Это заблокируется, пока не завершится транзакция Папы Медведя

На уровне SERIALIZABLE Папа Медведь обеспечивает строгую последовательность. Никаких неожиданностей и фантомных записей.

Часть 3. Как блокировки помогают навести порядок

1. Блокировки на уровне таблицы

Представьте, что медведь вешает на всю полку табличку: «Не трогать, пока я считаю игрушки». Это пример блокировки на уровне таблицы.

PostgreSQL поддерживает несколько режимов таких блокировок, например:

ACCESS SHARE — используется для чтения (SELECT). Другие медведи могут тоже читать, но менять ничего нельзя.
ACCESS EXCLUSIVE — полная блокировка: никто не может ни читать, ни изменять таблицу.

-- Заблокировать таблицу orders для чтения
LOCK TABLE orders IN ACCESS SHARE MODE;

-- Заблокировать таблицу для любых операций
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

Эта блокировка полезна при миграциях или массовых обновлениях данных.

2. Блокировки на уровне строк

Блокировки на уровне строк используются для изолирования изменений конкретных записей. Например:

SELECT ... FOR UPDATE — блокирует строки для обновления.
SELECT ... FOR SHARE — блокирует строки для чтения другими транзакциями, чтобы избежать их изменения.

Однажды Медвежонок Тедди захотел зарезервировать редкую головоломку. Чтобы никто другой не смог её взять, он использовал явную блокировку:
Тедди заблокировал головоломку:

BEGIN;
-- Заблокировать головоломку
SELECT *  
FROM toys  
WHERE toy_name = 'головоломка'  
FOR UPDATE;  

-- Обновить остаток  
UPDATE toys  
SET stock = stock - 1  
WHERE toy_name = 'головоломка';  
COMMIT;  

Другой медведь видит блокировку:

BEGIN;
-- Эта попытка заблокируется, пока Тедди не завершит транзакцию
SELECT *  
FROM toys  
WHERE toy_name = 'головоломка'  
FOR UPDATE;  

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

3. Блокировки на уровне страниц

Если медведи разбивают полки на секции (например, по 10 игрушек в каждой), они могут заблокировать только одну секцию.

PostgreSQL автоматически использует блокировки на уровне страниц для оптимизации. Например, при обновлении нескольких строк в одной странице памяти, блокировка применяется к этой странице.

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

Часть 4. Как магазин справляется с блокировками

Иногда покупатели начинают слишком долго думать, из-за чего магазин блокируется. Это происходит, если:

Мёртвая блокировка (Deadlock) — один покупатель ждёт другого.
Зависшие транзакции — покупатели забывают оплатить покупки.
Магазин борется с этими проблемами:

Deadlock Timeout
Если покупатели ждут друг друга, магазин завершает одну из транзакций.

SET deadlock_timeout = '5s';

Idle Transaction Timeout
Если покупатель завис в процессе, магазин завершает его транзакцию:

SET idle_in_transaction_session_timeout = '10s';

Ограничение подключений
Чтобы магазин не переполнился, он ограничивает число покупателей:

SET max_connections = 100;

Пример мёртвой блокировки:

-- Папа блокирует игрушку
BEGIN;
SELECT * FROM toys WHERE toy_name = 'мяч' FOR UPDATE;

-- Мама блокирует другую игрушку
BEGIN;
SELECT * FROM toys WHERE toy_name = 'кубик' FOR UPDATE;

-- Папа пытается взять кубик, но ждёт Мамину транзакцию
SELECT * FROM toys WHERE toy_name = 'кубик' FOR UPDATE;

-- Мама пытается взять мяч, но ждёт Папину транзакцию
SELECT * FROM toys WHERE toy_name = 'мяч' FOR UPDATE;

-- Магазин определяет мёртвую блокировку и завершает одну из транзакций

Так магазин предотвращает зависания и даёт покупателям завершить свои действия.

Весь этот рассказ о магазине игрушек помогает понять, как транзакции и блокировки в PostgreSQL работают за кулисами. Управление игрушками – это как управление данными в базе: чтобы всё было в порядке, важно следить за очередями, фиксировать изменения и предотвращать конфликты.

Если у вас много покупателей, убедитесь, что ваш магазин настроен правильно:

Настраивайте блокировки так, чтобы покупатели не путались. Например, блокируйте полку с редкой игрушкой, пока её выбирают.
Следите за взаимоблокировками, чтобы никто не остался у кассы с пустыми руками.
И помните: хорошо настроенный магазин всегда доставляет радость своим посетителям – точно так же, как продуманная база данных работает эффективно и стабильно.

---

Повторяем материал

Сценарий:
Ваша транзакция обновляет данные в таблице orders и вставляет данные в таблицу payments.
Во время выполнения транзакции другой пользователь пытается прочитать данные из таблицы orders, а третий пользователь выполняет обновление в таблице products.

Задачи:
1) Объясните, как уровень изоляции транзакций в PostgreSQL (READ COMMITTED, REPEATABLE READ, SERIALIZABLE)
влияет на поведение вашей транзакции и на доступ к таблицам для других пользователей.
2) Что произойдет, если транзакция вашего приложения выполнит SELECT ... FOR UPDATE на записи в таблице orders?
Как это повлияет на параллельные запросы других пользователей?
3) Какие проблемы, связанные с параллелизмом (например, грязное чтение, фантомные записи, потерянные обновления),
могут возникнуть в зависимости от выбранного уровня изоляции, и как PostgreSQL предотвращает их?

---

1. Влияние уровня изоляции на поведение транзакции и доступ к таблицам

1. READ COMMITTED:
Описание: Каждая операция внутри транзакции видит только подтвержденные изменения, сделанные другими транзакциями.
Эффект:
Другой пользователь, читающий таблицу orders, увидит изменения только после фиксации вашей транзакции.
Обновление в таблице products, выполняемое третьим пользователем, не будет заблокировано вашей транзакцией, так как products не участвует в вашей транзакции.
2. REPEATABLE READ:
Описание: Транзакция видит снимок базы данных, актуальный на момент её начала. Изменения других транзакций игнорируются до фиксации.
Эффект:
Другой пользователь не увидит изменений в orders, даже если вы завершите изменения, пока ваша транзакция активна.
Обновления в products третьим пользователем также будут возможны, так как products не блокируется.
3. SERIALIZABLE:
Описание: Этот уровень изоляции эмулирует выполнение транзакций последовательно. PostgreSQL предотвращает любые конфликты, которые могли бы нарушить согласованность данных.
Эффект:
Другой пользователь, читающий таблицу orders, может быть заблокирован до завершения вашей транзакции.
Обновления в products также могут быть заблокированы, если PostgreSQL определит потенциальный конфликт с вашей транзакцией.

2. SELECT ... FOR UPDATE на таблице orders

Ваша транзакция блокирует строки таблицы orders, выбранные запросом SELECT ... FOR UPDATE.
Другие транзакции:

• Не смогут обновить заблокированные строки до завершения вашей транзакции.
• Могут читать данные из таблицы, но не блокированные строки. Эффект: Параллельные запросы на чтение: Работают нормально, если не включают заблокированные строки. Параллельные запросы на обновление: Заблокированы до фиксации или отката вашей транзакции.

3. Проблемы параллелизма и их предотвращение в PostgreSQL

1. Грязное чтение (Dirty Reads):
Проблема: Транзакция читает данные, которые ещё не зафиксированы.
Предотвращение: Исключается на уровне READ COMMITTED и выше.
2. Неповторяющееся чтение (Non-Repeatable Reads):
Проблема: Данные, прочитанные в начале транзакции, могут измениться до её завершения.
Предотвращение: Исключается на уровне REPEATABLE READ и выше.
3. Фантомные записи (Phantom Reads):
Проблема: Данные, соответствующие критерию запроса, могут измениться (например, новые строки появляются).
Предотвращение: Исключается на уровне SERIALIZABLE.
4. Потерянные обновления (Lost Updates):
Проблема: Два пользователя обновляют одни и те же данные, и одно из изменений теряется.
Предотвращение: PostgreSQL использует блокировки (например, SELECT ... FOR UPDATE).