Database reference

Что такое транзакция БД?

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

Варианты закрытия транзакции: COMMIT, ROLLBACK.

Источники:

Database transaction, Wikipedia.

Что такое ACID?

ACID (от англ. atomicity, consistency, isolation, durability) — набор требований к транзакционной системе, обеспечивающий наиболее надёжную и предсказуемую её работу: атомарность, согласованность, изоляция, устойчивость; сформулированы в конце 1970-х годов Джимом Греем.

Источники:

См. также:

Что такое транзакция, Habr.
Транзакции и механизмы их контроля, Habr.
Что такое База Данных (БД), Habr.
Требования ACID на простом языке, Habr.
Разбираем ACID по буквам в NoSQL, Habr.
Транзакции, ACID, CAP, GeekBrains.
Блокировка (СУБД), Wikipedia.
Режимы блокировки — здесь хорошо описано, в чем отличие эксклюзивной от разделямой блокировки.

Atomicity — Атомарность

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

Поскольку на практике невозможно одновременно и атомарно выполнить всю последовательность операций внутри транзакции, вводится понятие «отката» (rollback).

Consistency — Согласованность

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

Tip

При разработке использовать:

foreign keys;
constraints для значений полей;
транзакции с промежуточными проверками бизнес-логики и откатом в случае её нарушения.

Isolation — Изолированность

Во время выполнения транзакции параллельные транзакции не должны оказывать влияния на её результат.

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

Возможные эффекты параллельной работы нескольких транзакций

Потерянное обновление. Когда две транзакции записывают разные значения в одну и ту же ячейку, одно из изменений теряется.
Грязное чтение. Когда читаются данные, которые в этот момент изменяются транзакцией, а потом транзакция откатывается и данные исчезают.
Неповторяющееся чтение. Когда несколько раз читаются данные, которые в этот момент изменяются транзакцией — каждый раз данные могут отказаться другими.
Фантомное чтение. Одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. Другая транзакция в интервалах между этими выборками добавляет или удаляет строки, или изменяет столбцы некоторых строк, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк.
Аномалия сериализации. Результат успешной фиксации группы транзакций несовместим со всеми возможными порядками выполнения этих транзакций по одному за раз.

Распространенные способы достижения изолированности транзакций

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

1. Чтение незафиксированных данных (read uncommitted).

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

Warning

На данном уровне нельзя использовать данные, на основе которых делаются важные для приложения выводы и критические решения т.к. выводы эти могут быть далеки от реальности. Данный уровень можно использовать, например, для примерных расчетов чего-либо. Результат COUNT(*) или MAX(*) можно использовать в каких-нибудь нестрогих отчетах.

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

2. Чтение зафиксированных данных (read committed).

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

3. Повторяемое чтение (repeatable read).

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

Небольшой абстрактный пример — сервис генерации подарочных сертификатов (кодов) и их использования

Например, злоумышленник сгенерировал себе код сертификата и пытается его активировать, пытаясь послать несколько запросов подряд на активацию купона. В таком случае у нас запустится несколько параллельно исполняемых транзакций, работающих с одним и тем же купоном. И в некоторых ситуациях может возникнуть двойная или даже тройная активация купона (пользователь получит 2x/3x бонусов).

При repeatable read в данном случае возникнет lock и активация пройдет единожды, а в предыдущих 2 уровнях возможна многократная активация. Подобную проблему можно также решить с помощью запроса SELECT FOR UPDATE, который также заблокирует обновляемую запись (купон).

4. Сериализуемое (serializable).

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

Note

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

Цена за это медленная скорость транзакций из-за частых lock'ов поэтому при плохой архитектуре приложения это может сыграть с Вами злую шутку.

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

Уровень изоляции	Грязные чтения	Неповторяющиеся чтения	Фантомные чтения	Аномалия сериализации
Чтение незафиксированных данных (read uncommitted)	⚠️ Может произойти, но не в PG	⚠️ Может произойти	⚠️ Может произойти	⚠️ Может произойти
Чтение зафиксированных данных (read committed)	✅ Не происходят	⚠️ Может произойти	⚠️ Может произойти	⚠️ Может произойти
Повторяемое чтение (repeatable read)	✅ Не происходят	✅ Не происходят	⚠️ Может произойти, но не в PG	⚠️ Может произойти
Сериализуемое (serializable)	✅ Не происходят	✅ Не происходят	✅ Не происходят	✅ Не происходят

Durability — Надежность

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

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

Как выбрать уровень изоляции транзакции?

Источники:

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

Вывод списка в грид/на форму — обычно read committed, изредка read uncommitted (только в некоторых СУБД имеет смысл, в частности в старых версиях MS SQL, или если не используете RCSI)
Отчёты/формы, состоящие из одного запроса с соединениями/объединениями (join или union) — read committed.
Отчёты/формы, состоящие из нескольких последовательных запросов или использующие временные таблицы — надо оценить, насколько тут нужен repeatable read (и действия должны быть в одной транзакции, иначе repeatable read не имеет смысла). Если не нужен, то read committed, если нужен, то repeatable read.
Пишущие транзакции (с проверкой условий/остатков, из нескольких запросов) — используйте по умолчанию не ниже repeatable read.
В MS SQL лучше (с точки зрения корректности данных и отсутствия взаимоблокировок) в пишущей транзакции для тех таблиц, которые меняются в данной транзакции использовать как можно раньше serializable. В других СУБД в зависимости от того, можете ли нарваться на фантомы.

Важно, что всегда сначала выбираем минимальный уровень, обеспечивающий корректную работу, и только потом смотрим, как это можно улучшить.

После того, как определились с базовым уровнем изоляции начинаем смотреть (правильность, производительность одного потока, производительность параллельных соединений с сервером, взаимоблокировки и блокировки/race condition горячих мест) и искать компромиссы. Тут уже парой абзацев не отделаться (статья превратится в книгу) — ситуаций и компромиссов даже в одной СУБД, даже типовых быстро становятся десятки.

Цикл типичный: сбор информации, гипотеза, проверка, изменение, проверка применимости, внедрение и так по кругу.

N+1 Query Problem

Источники:

Проблема с запросом N+1 возникает, когда платформа доступа к данным, такая как ORM SQLAlchemy, выполнила N дополнительных SQL-инструкций для извлечения тех же данных, которые могли быть получены при выполнении основного SQL-запроса.

Эта проблема возникает, когда коду необходимо загрузить дочерние элементы отношения родитель-потомок ("много" в "один ко многим"). В большинстве ORM по умолчанию включена отложенная загрузка, поэтому запросы выдаются для родительской записи, а затем по одному запросу для КАЖДОЙ дочерней записи.

Чем больше значение N, тем больше запросов будет выполнено, тем больше влияние на производительность. И, в отличие от журнала медленных запросов, который может помочь вам найти медленно выполняющиеся запросы, проблема N + 1 не будет устранена, потому что каждый отдельный дополнительный запрос выполняется достаточно быстро, чтобы не вызвать журнал медленных запросов.

Проблема заключается в том, что каждый дополнительный запрос имеет довольно много накладных расходов. Гораздо быстрее выдать 1 запрос, который возвращает 100 результатов, чем выдавать 100 запросов, каждый из которых возвращает 1 результат.