Подходит ли платформа для начинающих без опыта работы?

Да, курсы разбиты по уровням: Junior, Middle, Senior. Начинающие могут стартовать с базовых тем Python, Docker и алгоритмов и постепенно двигаться к более сложным темам.

Как быстро можно подготовиться к собеседованию на позицию Junior разработчика?

При занятиях 1–2 часа в день — от 2 до 4 недель на основные темы. Платформа анализирует слабые места по результатам квизов и строит персональный план подготовки.

Какие технологии охватывает платформа?

Python, FastAPI, Django, Docker, алгоритмы и структуры данных, Agile/Scrum, SQL, CI/CD, системный дизайн, код-ревью и более 50 других тем для разработчиков.

Платформа бесплатная?

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

Как платформа помогает найти работу программистом?

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

transactions_locks

Транзакции и блокировки

ACID, уровни изоляции, MVCC, SELECT FOR UPDATE, UPSERT

Транзакции и блокировки в PostgreSQL

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

Зачем это нужно? Представьте банковский перевод: деньги должны списаться с одного счёта и зачислиться на другой. Если после списания произойдёт сбой, деньги просто исчезнут. Транзакции гарантируют, что либо произойдут оба действия, либо ни одно из них.

Основы транзакций

Транзакция — это последовательность операций, которая выполняется как единое целое. У транзакции есть чёткие границы:

-- Начало транзакции
BEGIN;  -- или BEGIN TRANSACTION;

-- Операции внутри транзакции
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Завершение
COMMIT;  -- Сохранить изменения (транзакция успешно завершена)
-- или
ROLLBACK;  -- Отменить изменения (транзакция отклонена)

Важные моменты:

Если вы выполните SQL-запрос без явного BEGIN, PostgreSQL автоматически создаст транзакцию для этого запроса и сразу выполнит COMMIT. Это называется autocommit.
После COMMIT или ROLLBACK транзакция завершается. Для выполнения следующих операций нужно снова указать BEGIN.
Некоторые DDL-команды (например, CREATE TABLE, ALTER TABLE) автоматически выполняют COMMIT до и после себя — их нельзя откатить внутри транзакции.

Пример с откатом:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Ой, что-то пошло не так!
ROLLBACK;  -- Все изменения отменены, баланс счёта 1 не изменился

ACID

ACID — это аббревиатура, описывающая четыре ключевых свойства транзакций. Именно они делают базы данных надёжными.

Свойство	Описание	Пример нарушения
Atomicity (Атомарность)	Всё или ничего — либо все операции выполняются, либо ни одна	При переводе деньги списались, но не зачислились на другой счёт
Consistency (Согласованность)	Целостность — данные остаются корректными после транзакции, все ограничения соблюдаются	После транзакции баланс счёта стал отрицательным, хотя есть ограничение `CHECK balance >= 0`
Isolation (Изоляция)	Изоляция — транзакции не мешают друг другу, каждая работает как будто она одна	Две транзакции одновременно прочитали и обновили одну строку, получив некорректный результат
Durability (Долговечность)	После COMMIT данные сохранены навсегда, даже при сбое питания	Сервер перезагрузился сразу после COMMIT, данные потеряны

Как PostgreSQL обеспечивает ACID:

Atomicity: Используется журнал предзаписи (WAL — Write-Ahead Logging). Все изменения сначала записываются в лог, а потом применяются к данным.
Consistency: Проверка ограничений (constraints) происходит в конце каждой операции. При нарушении — автоматический ROLLBACK.
Isolation: Реализуется через MVCC (многоверсионность) и систему блокировок.
Durability: После COMMIT запись в WAL считается надёжно сохранённой на диске. При сбое PostgreSQL восстанавливается по логу.

WAL (Write-Ahead Logging) — это механизм, при котором все изменения сначала записываются в журнал, и только потом применяются к основным данным. Это гарантирует восстановление после сбоев.

Уровни изоляции

Уровень изоляции определяет, насколько транзакции «видят» изменения друг друга. Чем выше уровень, тем сильнее изоляция, но тем ниже производительность при высокой конкуренции.

PostgreSQL поддерживает три уровня изоляции (четвёртый уровень READ UNCOMMITTED в PostgreSQL не реализован — он автоматически повышается до READ COMMITTED):

-- Установить уровень изоляции для следующей транзакции
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Или установить для всех последующих транзакций в сессии
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Важно: Уровень изоляции нужно указывать после BEGIN и перед первым запросом в транзакции. В противном случае получите ошибку.

Проблемы параллелизма

Прежде чем разбирать уровни, поймём, какие аномалии они предотвращают:

Аномалия	Описание	Пример
Dirty Read (Грязное чтение)	Чтение незакоммиченных данных	Транзакция A обновила строку, но ещё не сделала COMMIT. Транзакция B прочитала эту строку и увидела новые данные. Если A сделает ROLLBACK, B прочитала несуществующие данные
Non-repeatable Read (Неповторяемое чтение)	Повторное чтение той же строки даёт другой результат	Транзакция A прочитала строку. Транзакция B обновила и закоммитила эту строку. Когда A снова прочитала строку, данные изменились
Phantom Read (Фантомное чтение)	Появление новых строк при повторном выполнении запроса	Транзакция A выбрала все строки с `status = 'pending'`. Транзакция B добавила новую строку с `status = 'pending'` и закоммитила. При повторном запросе A увидела новую строку
Write Skew (Перекашивание записи)	Две транзакции читают пересекающиеся данные и обновляют непересекающиеся, нарушая целостность	Два врача видят, что дежурят двое. Каждый решает уйти, думая, что останется хотя бы один. В итоге дежурных нет вообще

READ COMMITTED (по умолчанию)

Это уровень по умолчанию в PostgreSQL. Каждая команда SELECT видит снимок данных на момент начала этой команды, а не на момент начала транзакции.

BEGIN;
-- Видит только закоммиченные данные на момент каждого оператора
SELECT * FROM users WHERE id = 1;  -- Видит последнюю закоммиченную версию
-- Если другая транзакция закоммитила изменения между запросами...
SELECT * FROM users WHERE id = 1;  -- ...может увидеть другую версию
COMMIT;

Предотвращает:

✅ Dirty reads (чтение незакоммиченных данных)

Допускает:

❌ Non-repeatable reads (повторное чтение может вернуть другие данные)
❌ Phantom reads (появление новых строк)

Когда использовать:

Большинство OLTP-приложений (онлайн-транзакции)
Когда важна производительность и допустимы небольшие несогласованности
Чтение данных, которые редко меняются

Пример проблемы:

Время    Транзакция A                    Транзакция B
-----------------------------------------------------------
T1       BEGIN                           —
T2       SELECT balance → 1000           —
T3       —                               BEGIN
T4       —                               UPDATE SET balance = 2000
T5       —                               COMMIT
T6       SELECT balance → 2000           —
         (баланс изменился внутри транзакции!)

REPEATABLE READ

Транзакция видит снимок данных на момент первого запроса в этой транзакции. Все последующие запросы видят ту же версию данных, независимо от изменений в других транзакциях.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Снимок данных на момент первого запроса
SELECT * FROM users WHERE id = 1;  -- Запоминает версию
-- Даже если другая транзакция закоммитит изменения...
SELECT * FROM users WHERE id = 1;  -- ...увидим ту же версию
COMMIT;

Предотвращает:

✅ Dirty reads
✅ Non-repeatable reads

Допускает:

❌ Phantom reads (в PostgreSQL 9.3+ предотвращаются благодаря MVCC)
❌ Write skew

Когда использовать:

Отчёты и аналитика, где нужна согласованность данных на момент снимка
Длительные транзакции чтения
Когда нужно гарантировать, что данные не изменятся в процессе обработки

Особенность PostgreSQL: В отличие от стандарта SQL, в PostgreSQL REPEATABLE READ также предотвращает фантомное чтение благодаря MVCC.

SERIALIZABLE

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

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Полная сериализуемость
-- Транзакции выполняются как если бы были последовательными
-- PostgreSQL отслеживает зависимости между транзакциями

COMMIT;

Предотвращает:

✅ Dirty reads
✅ Non-repeatable reads
✅ Phantom reads
✅ Write skew

Особенность: Может откатить транзакцию с ошибкой сериализации:

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, with conflict out to ...
HINT: The transaction might succeed if retried.

Когда использовать:

Критически важные финансовые операции
Когда целостность данных важнее производительности
Сложные бизнес-правила, которые нельзя нарушить

Как обрабатывать ошибки:

import psycopg2
from psycopg2 import sql
import time

def execute_with_retry(conn, func, max_retries=3):
    """Выполнить транзакцию с повторными попытками при ошибке сериализации"""
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    return func(cur)
        except psycopg2.extensions.TransactionRollbackError as e:
            if e.pgcode == '40001':  # Код ошибки сериализации
                if attempt == max_retries - 1:
                    raise
                time.sleep(0.1 * (2 ** attempt))  # Экспоненциальная задержка
                continue
            raise

Сравнение уровней изоляции

Уровень	Dirty Read	Non-repeatable Read	Phantom Read	Write Skew	Производительность
READ COMMITTED	❌ Предотвращает	✅ Допускает	✅ Допускает	✅ Допускает	⭐⭐⭐ Высокая
REPEATABLE READ	❌ Предотвращает	❌ Предотвращает	❌ Предотвращает*	✅ Допускает	⭐⭐ Средняя
SERIALIZABLE	❌ Предотвращает	❌ Предотвращает	❌ Предотвращает	❌ Предотвращает	⭐ Низкая

*В PostgreSQL благодаря MVCC

MVCC (Multi-Version Concurrency Control)

MVCC (Multi-Version Concurrency Control) — это механизм многоверсионного управления параллельным доступом. Это сердце PostgreSQL, которое делает его уникальным среди других СУБД.

Основная идея

Вместо того чтобы блокировать данные при чтении, PostgreSQL хранит несколько версий каждой строки. Когда транзакция обновляет строку, старая версия не удаляется — она помечается как устаревшая, и создаётся новая версия.

Время    Транзакция A          Транзакция B         Данные в таблице
-----------------------------------------------------------
T1       BEGIN                 —                    v1 (xid=100)
T2       SELECT → v1           —                    v1 (xid=100)
T3       —                     BEGIN                v1 (xid=100)
T4       —                     UPDATE → v2          v1 (xid=100), v2 (xid=101)
T5       SELECT → v1           —                    v1, v2
T6       —                     COMMIT               v1, v2
T7       SELECT → v1           —                    v1, v2
T8       COMMIT                —                    v1, v2
T9       —                     VACUUM → удаляет v1  v2

Как это работает внутри

Каждая строка в PostgreSQL содержит скрытые системные столбцы:

Столбец	Описание
`xmin`	ID транзакции, которая вставила эту версию строки
`xmax`	ID транзакции, которая удалила/обновила эту версию строки (0 = не удалена)
`ctid`	Физический адрес строки в таблице (блок, смещение)

Правила видимости версий:

Транзакция видит строку, если её xmin меньше текущего ID транзакции и транзакция xmin закоммичена
Транзакция НЕ видит строку, если её xmax меньше текущего ID транзакции и транзакция xmax закоммичена
Транзакция видит последнюю подходящую версию строки

-- Посмотреть скрытые столбцы
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;

-- Пример вывода:
-- xmin=12345, xmax=0, ctid=(0,1), id=1, balance=1000
-- xmax=0 означает, что строка не удалена

Преимущества MVCC

Преимущество	Объяснение
📖 Читатели не блокируют писателей	SELECT выполняется без блокировок, даже если строка обновляется
✍️ Писатели не блокируют читателей	UPDATE не ждёт завершения SELECT, создаётся новая версия
📸 Согласованный снимок	Каждая транзакция видит данные на определённый момент времени
⚡ Высокая производительность	Параллельные операции чтения и записи не мешают друг другу

Недостатки MVCC

Проблема	Описание	Решение
Раздувание таблиц	Старые версии строк занимают место	Регулярный `VACUUM`
Накладные расходы	Хранение нескольких версий требует памяти и диска	Автовакуумизация
Bloat	Таблицы могут разрастаться при интенсивных обновлениях	Мониторинг, `VACUUM FULL` при необходимости

VACUUM

VACUUM очищает старые версии строк, которые больше не нужны ни одной активной транзакции:

-- Обычный VACUUM (не блокирует, работает параллельно)
VACUUM accounts;

-- VACUUM с анализом (обновляет статистику для планировщика)
VACUUM ANALYZE accounts;

-- VACUUM FULL (переписывает таблицу, возвращает место ОС, блокирует)
VACUUM FULL accounts;

-- Проверка состояния VACUUM
SELECT 
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'accounts';

Autovacuum: PostgreSQL автоматически запускает VACUUM и ANALYZE через фоновые процессы. В большинстве случаев ручное вмешательство не требуется, но за логами автовакуума стоит следить.

Пример работы MVCC в действии

-- Сессия 1
BEGIN;
SELECT id, balance FROM accounts WHERE id = 1;  -- Видит v1: balance=1000

-- Сессия 2
BEGIN;
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;

-- Сессия 1 (продолжение)
SELECT id, balance FROM accounts WHERE id = 1;  -- Всё ещё видит v1: balance=1000!
COMMIT;

-- Сессия 3
SELECT id, balance FROM accounts WHERE id = 1;  -- Видит v2: balance=2000

Блокировки строк

Хотя MVCC позволяет читателям и писателям не мешать друг другу, иногда нужно явно заблокировать строки для предотвращения конфликтов записи.

SELECT FOR UPDATE

SELECT FOR UPDATE блокирует выбранные строки от изменений другими транзакциями до завершения текущей транзакции:

BEGIN;

-- Блокировка строк для обновления
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE;

-- Другие транзакции не могут обновить эту строку
-- Они будут ждать завершения нашей транзакции
UPDATE accounts SET balance = balance - 100
WHERE id = 1;

COMMIT;

Что блокируется:

❌ UPDATE заблокированных строк — ждёт
❌ DELETE заблокированных строк — ждёт
❌ SELECT FOR UPDATE тех же строк — ждёт
✅ SELECT (без FOR UPDATE) — не блокируется, читает старую версию через MVCC
✅ INSERT — не блокируется (вставляет новые строки)

Варианты блокировок

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

-- FOR UPDATE — эксклюзивная блокировка
-- Блокирует от UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- FOR NO KEY UPDATE — блокировка без ключей
-- Как FOR UPDATE, но не блокирует SELECT FOR KEY SHARE
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;

-- FOR SHARE — разделяемая блокировка
-- Разрешает другим транзакциям тоже взять FOR SHARE, но блокирует UPDATE/DELETE
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

-- FOR KEY SHARE — блокировка ключей
-- Блокирует только DELETE, разрешает UPDATE не-ключевых столбцов
SELECT * FROM accounts WHERE id = 1 FOR KEY SHARE;

Иерархия блокировок (от слабой к сильной):

FOR KEY SHARE ← FOR SHARE ← FOR NO KEY UPDATE ← FOR UPDATE

Более сильная блокировка блокирует более слабые

SKIP LOCKED и NOWAIT

По умолчанию SELECT FOR UPDATE ждёт освобождения блокировки. Это может привести к зависанию:

-- Ждать освобождения блокировки (поведение по умолчанию)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- SKIP LOCKED — пропустить заблокированные строки
-- Полезно для очередей задач: берём доступные, пропускаем занятые
SELECT * FROM tasks 
WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- NOWAIT — немедленно вернуть ошибку если строка заблокирована
-- Полезно для избежания ожидания в UI
SELECT * FROM accounts 
WHERE id = 1 
FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"

Пример очереди задач с SKIP LOCKED:

-- Несколько воркеров могут безопасно брать задачи из очереди
-- Каждый получит уникальные незаблокированные задачи

-- Воркер 1
BEGIN;
SELECT id, payload FROM tasks 
WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED 
LIMIT 1;
-- Получил задачу id=5

-- Воркер 2 (параллельно)
BEGIN;
SELECT id, payload FROM tasks 
WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED 
LIMIT 1;
-- Получил задачу id=7 (не ждёт воркера 1!)

Табличные блокировки

Иногда нужно заблокировать всю таблицу:

-- Явная блокировка таблицы
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

-- Режимы блокировок таблиц (от слабого к сильному):
-- ACCESS SHARE — только чтение (обычный SELECT)
-- ROW SHARE — SELECT FOR UPDATE/SHARE
-- ROW EXCLUSIVE — UPDATE, DELETE, INSERT
-- SHARE UPDATE EXCLUSIVE — VACUUM, CREATE INDEX CONCURRENTLY
-- SHARE — блокирует запись, разрешает чтение
-- SHARE ROW EXCLUSIVE — как SHARE, но строже
-- EXCLUSIVE — только чтение для других
-- ACCESS EXCLUSIVE — полная блокировка (ничего нельзя делать)

Осторожно: LOCK TABLE может вызвать серьёзные проблемы с производительностью. Используйте только когда действительно необходимо.

UPSERT (INSERT ... ON CONFLICT)

UPSERT (UPDATE + INSERT) — это операция, которая вставляет новую строку или обновляет существующую при конфликте уникального ограничения.

Базовый синтаксис

-- Обновить при конфликте
INSERT INTO users (email, name, created_at)
VALUES ('alice@example.com', 'Alice', NOW())
ON CONFLICT (email)              -- Уникальное ограничение
DO UPDATE SET
    name = EXCLUDED.name,        -- EXCLUDED — псевдоним для вставляемых данных
    updated_at = NOW()
RETURNING id, name, updated_at;  -- Вернуть обновлённую строку

Как это работает:

PostgreSQL пытается вставить строку
Если возникает конфликт уникального ограничения (например, email уже существует):
- Выполняется UPDATE существующей строки
- В EXCLUDED доступны значения, которые пытались вставить
Если конфликта нет — строка вставляется

Варианты использования

-- Игнорировать дубликат (ничего не делать)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;
-- Ничего не возвращает, если был конфликт

-- Частичный UPSERT (обновлять только при условии)
INSERT INTO users (email, is_active)
VALUES ('alice@example.com', TRUE)
ON CONFLICT (email)
DO UPDATE SET
    is_active = EXCLUDED.is_active,
    updated_at = NOW()
WHERE users.is_active = FALSE;  -- Обновлять только если пользователь не активен

Конфликт по уникальному индексу

Можно указывать как имя ограничения, так и столбцы:

-- По имени ограничения
ON CONFLICT CONSTRAINT users_email_key
DO UPDATE SET name = EXCLUDED.name;

-- По столбцам (PostgreSQL найдёт соответствующее ограничение)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- По выражению (частичный индекс)
ON CONFLICT (email) WHERE (is_active = true)
DO UPDATE SET name = EXCLUDED.name;

Практический пример: счётчик посещений

-- Таблица счётчиков
CREATE TABLE visit_counts (
    page_url TEXT PRIMARY KEY,
    visit_count INTEGER DEFAULT 1,
    last_visited TIMESTAMPTZ DEFAULT NOW()
);

-- UPSERT для инкремента счётчика
INSERT INTO visit_counts (page_url, visit_count, last_visited)
VALUES ('/products/123', 1, NOW())
ON CONFLICT (page_url)
DO UPDATE SET
    visit_count = visit_counts.visit_count + 1,
    last_visited = NOW()
RETURNING visit_count;

Важно: ON CONFLICT работает только с уникальными ограничениями (UNIQUE, PRIMARY KEY). Обычные индексы без ограничения не подходят.

LISTEN/NOTIFY (Асинхронные уведомления)

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

Базовое использование

-- Сессия 1: подписка на канал
LISTEN orders_channel;

-- Сессия 2: отправка уведомления
NOTIFY orders_channel, '{"order_id": 42, "action": "created"}';

-- Сессия 1: получение уведомления (в приложении)
-- psycopg2: conn.notifies.get()

Характеристики:

Уведомления асинхронные — получатель не должен опрашивать базу
Уведомления не надёжные — если подписчик не слушает, уведомление теряется
Уведомления не транзакционные — отправляются при COMMIT транзакции
Размер полезной нагрузки до 8000 байт

Пример на Python

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from select import select

conn = psycopg2.connect("dbname=test user=postgres")
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()

# Подписка на канал
cur.execute("LISTEN orders_channel;")
print("Ожидание уведомлений...")

while True:
    # Ждём уведомления с таймаутом 5 секунд
    if select([conn], [], [], 5) != ([], [], []):
        conn.poll()
        while conn.notifies:
            notify = conn.notifies.pop(0)
            print(f"Получено: канал={notify.channel}, payload={notify.payload}")

Практические сценарии

1. Инвалидация кеша:

-- После обновления данных
NOTIFY cache_invalidate, '{"table": "users", "id": 42}';

2. Оповещение воркеров:

-- Новая задача в очереди
NOTIFY task_queue, '{"task_id": 123, "priority": "high"}';

3. Real-time обновления UI:

-- Уведомление клиентам о изменениях
NOTIFY user_updates, '{"user_id": 42, "event": "profile_changed"}';

PG_NOTIFY в триггерах

Автоматическая отправка уведомлений при изменениях:

-- Функция-триггер
CREATE OR REPLACE FUNCTION notify_order_change()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify(
        'orders_channel',
        json_build_object(
            'order_id', NEW.id,
            'action', TG_OP,
            'old', row_to_json(OLD),
            'new', row_to_json(NEW)
        )::text
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггер
CREATE TRIGGER orders_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Write Skew (Перекашивание записи)

Write Skew — это аномалия изоляции, которая возникает когда две транзакции читают пересекающиеся данные, но обновляют непересекающиеся, нарушая целостность бизнес-правила.

Классический пример: дежурство врачей

Врач дежурство    Транзакция A          Транзакция B
-----------------------------------------------------------
T1                BEGIN                 BEGIN
T2                SELECT COUNT(*) → 2   SELECT COUNT(*) → 2
                    (оба дежурят)         (оба дежурят)
T3                -- Можно уйти          -- Можно уйти
                    (останется 1)         (останется 1)
T4                UPDATE SET on_call = on_call - 1
                      WHERE doctor = 'A'
T5                                      UPDATE SET on_call = on_call - 1
                                            WHERE doctor = 'B'
T6                COMMIT                COMMIT

Результат: on_call = 0 (оба ушли, дежурных нет!)

Почему это произошло:

Обе транзакции использовали REPEATABLE READ
Каждая видела снимок с двумя врачами
Обновления не конфликтовали (разные строки)
Бизнес-правило «должен остаться хотя бы один врач» нарушено

Другие примеры Write Skew

Пример 1: Бронирование мест

-- Транзакция A: проверить свободные места
SELECT COUNT(*) FROM bookings WHERE event_id = 1;  -- 99 мест занято

-- Транзакция B: проверить свободные места
SELECT COUNT(*) FROM bookings WHERE event_id = 1;  -- 99 мест занято

-- Обе транзакции видят 1 свободное место
-- A бронирует место 100, B бронирует место 101
-- Результат: 101 место при вместимости 100!

Пример 2: Баланс счетов

-- Правило: сумма балансов двух счетов должна быть >= 0
-- Транзакция A: снять $100 со счёта 1
-- Транзакция B: снять $100 со счёта 2
-- Обе видят общую сумму $150
-- Результат: -$50 (нарушение правила)

Решение Write Skew

Способ 1: SERIALIZABLE

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- Если одна транзакция закоммитится, вторая получит ошибку сериализации

Способ 2: SELECT FOR UPDATE

BEGIN;

-- Явно заблокировать все строки для предотвращения Write Skew
SELECT * FROM doctors 
WHERE on_call = true 
FOR UPDATE;

-- Теперь другие транзакции будут ждать
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- ... проверка и обновление
COMMIT;

Способ 3: Материализованное ограничение

-- Создать таблицу-счётчик с CHECK ограничением
CREATE TABLE doctor_count (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    count INTEGER NOT NULL CHECK (count >= 1)
);

-- Обновлять счётчик атомарно с изменениями
UPDATE doctor_count SET count = count - 1 
WHERE id = 1 AND count > 1;  -- Не даст уйти последнему врачу

Мёртвые блокировки (Deadlocks)

Deadlock возникает когда две или более транзакций ждут друг друга, создавая циклическую зависимость.

Пример deadlock

Время    Транзакция A          Транзакция B
-----------------------------------------------------------
T1       BEGIN                 BEGIN
T2       UPDATE accounts       —
         WHERE id = 1          —
         (блокировка id=1)     —
T3       —                     UPDATE accounts
                               WHERE id = 2
                               (блокировка id=2)
T4       UPDATE accounts       —
         WHERE id = 2          —
         (ждёт id=2)           —
T5       —                     UPDATE accounts
                               WHERE id = 1
                               (ждёт id=1)
                               
DEADLOCK! PostgreSQL откатит одну из транзакций:
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction ...
HINT: See server log for query details.

Как избежать deadlock

1. Одинаковый порядок блокировок:

-- Всегда блокировать строки в одинаковом порядке (например, по ID)
-- Плохо: A блокирует [1,2], B блокирует [2,1]
-- Хорошо: обе блокируют [1,2]

UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);

2. Короткие транзакции:

-- Плохо: долгая транзакция с множеством операций
BEGIN;
SELECT ...;  -- блокировки
UPDATE ...;
-- много кода между запросами
COMMIT;

-- Хорошо: минимизировать время между BEGIN и COMMIT
BEGIN;
SELECT ... FOR UPDATE;
UPDATE ...;
COMMIT;

3. Таймаут блокировок:

-- Установить таймаут ожидания блокировки (мс)
SET lock_timeout = '1s';

-- Если блокировка не освободится за 1 секунду:
-- ERROR: canceling statement due to lock timeout

Мониторинг блокировок

-- Посмотреть текущие блокировки
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Практические примеры и паттерны

1. Перевод денег между счетами

Классический пример с полной блокировкой:

BEGIN;

-- Блокировка счетов в одинаковом порядке (предотвращает deadlock)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;

-- Проверка баланса
UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

-- Проверка что обновление прошло (баланс был достаточным)
-- Обновление второго счёта
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

-- Проверка результата
SELECT id, balance FROM accounts WHERE id IN (1, 2);

2. Резервирование товара (очередь с SKIP LOCKED)

-- Воркер резервирует товар для заказа
BEGIN;

-- Взять первый доступный товар, пропустить заблокированные
SELECT id, quantity 
FROM products 
WHERE id = 1 AND quantity > 0 
FOR UPDATE SKIP LOCKED;

-- Резервирование
UPDATE products
SET quantity = quantity - 1,
    reserved_at = NOW()
WHERE id = 1;

-- Создание записи резервирования
INSERT INTO reservations (product_id, order_id, created_at)
VALUES (1, 42, NOW());

COMMIT;

3. Распределённая очередь задач

-- Таблица задач
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    status TEXT DEFAULT 'pending',
    worker_id TEXT,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ
);

-- Воркер берёт задачу (уникальную, не заблокированную другими)
BEGIN;

SELECT id, payload 
FROM tasks 
WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED 
LIMIT 1;

-- Пометить как выполняемую
UPDATE tasks 
SET status = 'processing',
    worker_id = 'worker-1',
    started_at = NOW()
WHERE id = <полученный_id>;

COMMIT;

-- После выполнения
UPDATE tasks 
SET status = 'completed',
    completed_at = NOW()
WHERE id = <полученный_id>;

4. Оптимистичная блокировка (Optimistic Locking)

Для случаев когда блокировки слишком дороги:

-- Таблица с версией
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    version INTEGER NOT NULL DEFAULT 0
);

-- Чтение
SELECT id, content, version FROM documents WHERE id = 1;
-- version = 5

-- Обновление с проверкой версии
UPDATE documents 
SET content = 'новый текст',
    version = version + 1
WHERE id = 1 AND version = 5;

-- Проверить количество обновлённых строк:
-- 1 строка — успешно
-- 0 строк — конфликт, кто-то обновил документ

Пример на Python:

def update_document(conn, doc_id, new_content, expected_version):
    with conn:
        with conn.cursor() as cur:
            cur.execute("""
                UPDATE documents 
                SET content = %s, version = version + 1
                WHERE id = %s AND version = %s
            """, (new_content, doc_id, expected_version))
            
            if cur.rowcount == 0:
                raise ConflictError("Документ был изменён другим пользователем")

5. Атомарный инкремент счётчика

-- Простой атомарный инкремент (не требует транзакции)
UPDATE counters 
SET value = value + 1 
WHERE name = 'page_views'
RETURNING value;

-- Инкремент с ограничением (не больше максимума)
UPDATE counters 
SET value = value + 1 
WHERE name = 'daily_requests' 
  AND value < 10000
RETURNING value;

Диагностика и отладка

Проверка текущих транзакций

-- Активные транзакции
SELECT 
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    query,
    xact_start,
    state_change,
    NOW() - xact_start AS transaction_duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;

Проверка блокировок

-- Сводка по блокировкам
SELECT 
    mode,
    locktype,
    relation::regclass,
    granted,
    COUNT(*) as count
FROM pg_locks
GROUP BY mode, locktype, relation, granted;

Долгие транзакции

-- Транзакции длящиеся больше 1 минуты
SELECT 
    pid,
    usename,
    NOW() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND NOW() - xact_start > INTERVAL '1 minute'
ORDER BY duration DESC;

Чек-лист: выбор правильного подхода

Задача	Решение
Простая вставка/обновление	`INSERT ... ON CONFLICT DO UPDATE`
Финансовые операции	`SERIALIZABLE` или `SELECT FOR UPDATE`
Очередь задач	`SELECT FOR UPDATE SKIP LOCKED`
Отчёт без блокировок	`READ COMMITTED` (по умолчанию)
Консистентный снимок данных	`REPEATABLE READ`
Критичная целостность	`SERIALIZABLE` с retry
Real-time уведомления	`LISTEN/NOTIFY`
Избежание deadlock	Одинаковый порядок блокировок
Высокая конкуренция записи	Оптимистичная блокировка (version)

Контрольные вопросы

Почему PostgreSQL не реализует READ UNCOMMITTED?
- Потому что MVCC по умолчанию предотвращает dirty reads
В чём разница между FOR UPDATE и FOR SHARE?
- FOR UPDATE — эксклюзивная блокировка, только одна транзакция может взять
- FOR SHARE — разделяемая, несколько транзакций могут взять одновременно
Что происходит при ошибке сериализации?
- Транзакция откатывается, нужно повторить её с начала
Когда нужен VACUUM?
- Для очистки старых версий строк, которые больше не нужны ни одной транзакции
Как SKIP LOCKED помогает в очередях?
- Позволяет нескольким воркерам брать разные задачи без ожидания

Готовы проверить знания на практике? Переходите к упражнениям!

Проверьте свои знания

Вопросы ещё не добавлены

Вопросы для этой подтемы ещё не добавлены.

Свойство

Описание

Пример нарушения

Atomicity (Атомарность)

Всё или ничего — либо все операции выполняются, либо ни одна

При переводе деньги списались, но не зачислились на другой счёт

Consistency (Согласованность)

Целостность — данные остаются корректными после транзакции, все ограничения соблюдаются

После транзакции баланс счёта стал отрицательным, хотя есть ограничение CHECK balance >= 0

Isolation (Изоляция)

Изоляция — транзакции не мешают друг другу, каждая работает как будто она одна

Две транзакции одновременно прочитали и обновили одну строку, получив некорректный результат

Durability (Долговечность)

После COMMIT данные сохранены навсегда, даже при сбое питания

Сервер перезагрузился сразу после COMMIT, данные потеряны

Аномалия

Описание

Пример

Dirty Read (Грязное чтение)

Чтение незакоммиченных данных

Транзакция A обновила строку, но ещё не сделала COMMIT. Транзакция B прочитала эту строку и увидела новые данные. Если A сделает ROLLBACK, B прочитала несуществующие данные

Non-repeatable Read (Неповторяемое чтение)

Повторное чтение той же строки даёт другой результат

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

Phantom Read (Фантомное чтение)

Появление новых строк при повторном выполнении запроса

Транзакция A выбрала все строки с status = 'pending'. Транзакция B добавила новую строку с status = 'pending' и закоммитила. При повторном запросе A увидела новую строку

Write Skew (Перекашивание записи)

Две транзакции читают пересекающиеся данные и обновляют непересекающиеся, нарушая целостность

Два врача видят, что дежурят двое. Каждый решает уйти, думая, что останется хотя бы один. В итоге дежурных нет вообще

Время Транзакция A Транзакция B ----------------------------------------------------------- T1 BEGIN — T2 SELECT balance → 1000 — T3 — BEGIN T4 — UPDATE SET balance = 2000 T5 — COMMIT T6 SELECT balance → 2000 — (баланс изменился внутри транзакции!)

ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, with conflict out to ... HINT: The transaction might succeed if retried.

Уровень

Dirty Read

Non-repeatable Read

Phantom Read

Write Skew

Производительность

READ COMMITTED

❌ Предотвращает

✅ Допускает

⭐⭐⭐ Высокая

REPEATABLE READ

❌ Предотвращает

❌ Предотвращает*

✅ Допускает

⭐⭐ Средняя

SERIALIZABLE

❌ Предотвращает

⭐ Низкая

Время Транзакция A Транзакция B Данные в таблице ----------------------------------------------------------- T1 BEGIN — v1 (xid=100) T2 SELECT → v1 — v1 (xid=100) T3 — BEGIN v1 (xid=100) T4 — UPDATE → v2 v1 (xid=100), v2 (xid=101) T5 SELECT → v1 — v1, v2 T6 — COMMIT v1, v2 T7 SELECT → v1 — v1, v2 T8 COMMIT — v1, v2 T9 — VACUUM → удаляет v1 v2

Столбец

Описание

xmin

ID транзакции, которая вставила эту версию строки

xmax

ID транзакции, которая удалила/обновила эту версию строки (0 = не удалена)

ctid

Физический адрес строки в таблице (блок, смещение)

Преимущество

Объяснение

📖 Читатели не блокируют писателей

SELECT выполняется без блокировок, даже если строка обновляется

✍️ Писатели не блокируют читателей

UPDATE не ждёт завершения SELECT, создаётся новая версия

📸 Согласованный снимок

Каждая транзакция видит данные на определённый момент времени

⚡ Высокая производительность

Параллельные операции чтения и записи не мешают друг другу

Проблема

Описание

Решение

Раздувание таблиц

Старые версии строк занимают место

Регулярный VACUUM

Накладные расходы

Хранение нескольких версий требует памяти и диска

Автовакуумизация

Bloat

Таблицы могут разрастаться при интенсивных обновлениях

Мониторинг, VACUUM FULL при необходимости

Врач дежурство Транзакция A Транзакция B ----------------------------------------------------------- T1 BEGIN BEGIN T2 SELECT COUNT(*) → 2 SELECT COUNT(*) → 2 (оба дежурят) (оба дежурят) T3 -- Можно уйти -- Можно уйти (останется 1) (останется 1) T4 UPDATE SET on_call = on_call - 1 WHERE doctor = 'A' T5 UPDATE SET on_call = on_call - 1 WHERE doctor = 'B' T6 COMMIT COMMIT Результат: on_call = 0 (оба ушли, дежурных нет!)

Время Транзакция A Транзакция B ----------------------------------------------------------- T1 BEGIN BEGIN T2 UPDATE accounts — WHERE id = 1 — (блокировка id=1) — T3 — UPDATE accounts WHERE id = 2 (блокировка id=2) T4 UPDATE accounts — WHERE id = 2 — (ждёт id=2) — T5 — UPDATE accounts WHERE id = 1 (ждёт id=1) DEADLOCK! PostgreSQL откатит одну из транзакций: ERROR: deadlock detected DETAIL: Process 1234 waits for ShareLock on transaction ... HINT: See server log for query details.

Задача

Решение

Простая вставка/обновление

INSERT ... ON CONFLICT DO UPDATE

Финансовые операции

SERIALIZABLE или SELECT FOR UPDATE

Очередь задач

SELECT FOR UPDATE SKIP LOCKED

Отчёт без блокировок

READ COMMITTED (по умолчанию)

Консистентный снимок данных

REPEATABLE READ

Критичная целостность

SERIALIZABLE с retry

Real-time уведомления

LISTEN/NOTIFY

Избежание deadlock

Одинаковый порядок блокировок

Высокая конкуренция записи

Оптимистичная блокировка (version)