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

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

---

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

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

В PostgreSQL многое на MVCC (версии строк): читатели редко блокируют писателей. Подробнее: Блокировки в PostgreSQL. Таблица по СУБД: справочник 883.

Три классических подхода в теории (блокировки, метки времени, оптимистичный контроль) и выбор стратегии — в Конкурентном доступе. Там же пошагово разобраны оптимистичный контроль (конфликт при UPDATE по version) и пессимистичный FOR UPDATE (очередь ожидания) на примере двух пользователей и одного счёта.

---

О транзакциях

Транзакция — последовательность SQL-команд, которая фиксируется целиком (COMMIT) или откатывается (ROLLBACK).

Аналогия: черновик. Пока не "Сохранить" — другие не видят правок; "Отменить" — ROLLBACK.

По умолчанию часто включён автокоммит: каждый INSERT / UPDATE / DELETE сразу фиксируется.

UPDATE users SET name = 'Анна' WHERE id = 1;  -- при автокоммите сразу COMMIT

В DBeaver, DataGrip — кнопки Commit / Rollback. В коде приложения для связанных шагов транзакцию открывают явно.

Когда нужна явная транзакция:

• все шаги должны пройти все или ни один;
• нельзя показывать промежуточное состояние (перевод, резерв на складе).

-- Без транзакции: после шага 1 деньги уже списаны
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- С транзакцией
BEGIN;  -- PostgreSQL; T-SQL: BEGIN TRANSACTION

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

COMMIT;
-- ROLLBACK;

Для учебных SELECT транзакции обычно не нужны; они критичны в платежах, складе, миграциях.

---

ACID

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

Свойство	Смысл
Atomicity	Все шаги или ни один
Consistency	Из валидного состояния — в валидное
Isolation	Параллельные транзакции не ломают логику друг друга
Durability	После COMMIT данные переживут сбой (WAL и т.п.)

START TRANSACTION;  -- MySQL; PostgreSQL: BEGIN

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT;
-- ROLLBACK;

InnoDB, PostgreSQL, SQL Server и SQLite в транзакционном режиме обеспечивают ACID. У SQLite при записи блокируется файл — ограничение параллелизма, не отказ от ACID.

---

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

Задают, какие аномалии чтения допустимы (грязное чтение, неповторяемое, фантомы). Это настройка I из ACID.

Уровень	Грязное чтение	Неповторяемое	Фантомы
READ UNCOMMITTED	да	да	да
READ COMMITTED	нет	да	да
REPEATABLE READ	нет	нет	зависит¹
SERIALIZABLE	нет	нет	нет

¹ В PostgreSQL REPEATABLE READ на snapshot isolation фантомы по сути не допускает.

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- ...
COMMIT;

По умолчанию: PostgreSQL — READ COMMITTED; InnoDB — обычно REPEATABLE READ.

---

О блокировках

Блокировки изолируют ресурс при параллельном доступе. Уровни — строка, страница, таблица — компромисс параллелизма и накладных расходов.

---

1. Блокировка записи (Row-level locking)

UPDATE employees SET salary = salary * 1.1 WHERE id = 101;

Блокируется строка id = 101. Поддержка — InnoDB, PostgreSQL, Oracle, SQL Server.

---

2. Блокировка страницы (Page-level locking)

Страница (~8–16 КБ) — единица I/O. Реже в современных OLTP; встречается в отдельных режимах SQL Server, устаревших движках.

---

3. Блокировка таблицы (Table-level locking)

-- Диалект: MySQL (в PostgreSQL другой синтаксис явных блокировок таблиц)
LOCK TABLES employees WRITE;
UPDATE employees SET salary = salary + 1000;
UNLOCK TABLES;

Также при ALTER TABLE или движках без row-level lock (MyISAM). В активном OLTP таблицу блокируют редко; допустимо при массовой загрузке.

В PostgreSQL чтение по MVCC обычно не блокирует запись; SELECT … FOR UPDATE, SKIP LOCKED и параллельная пакетная обработка задач — в Блокировки в PostgreSQL.

---

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

1. Чем COMMIT отличается от автокоммита?
2. Что общего у ACID и уровней изоляции — и чем они различаются?
3. Почему перевод между счетами лучше в одной транзакции?
4. Зачем в OLTP избегать блокировки всей таблицы?

---

См. также

• Конкурентный доступ к данным — три подхода в теории (блокировки, timestamp, optimistic)
• Восстановление после сбоя
• Роль базы данных в организации
• Блокировки в PostgreSQL
• Оптимизация и планы
• CRUD и DML

---