Всем привет! Я Олег, fullstack‑разработчик в компании Тензор, тимлид небольшой команды.

Одна из моих обязанностей в роли тимлида — code review. За годы работы многое повидал и выделил для себя типовые ошибки, с которыми сталкиваются начинающие backend‑разработчики. Сегодня расскажу о некоторых из них.

О чем пойдет речь

Разберем типовые ошибки, которые допускаются при работе с SQL‑базами данных в рамках сервиса с высокой нагрузкой и конкурентными запросами.

В качестве БД возьмем PostgreSQL, в примерах кода будет использоваться как SQL-запросы, так и «некий ORM чем‑то напоминающий Sequelize» (все совпадения случайны).

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

Описанные ниже проблемы актуальны не только для монолитов, но и для микросервисной архитектуры.

Набросаем простое приложение для демонстрации...

Пусть это будет простенький «форум». У нас будут пользователи, топики, эмоции к ним. У топиков будет количество просмотров.

// Пользователь
const User = define('User', {
    email: { type: 'string' },
    name: { type: 'string' }
})

// Топик
const Topic = define('Topic', {
    content: { type: 'string' },
    viewCount: { type: 'integer' }
});
Topic.belongsTo(User) // - автор топика

// Эмоция
const Emotion = define('Emotion', {
    emoji: { type: 'string' }
});
Emotion.belongsTo(Topic) // - для какого топика
Emotion.belongsTo(User) // - кто оставил

Опытный глаз наверняка заметил пачку проблем. Это нормально. Мы разберем их дальше.

ACID

Вспомним об ACID. Это такой набор требований к транзакционной (OLTP, Online Transaction Processing) системе, обеспечивающий наиболее надёжную и предсказуемую её работу.

• A — Atomicity, атомарность

• C — Consistency, консистентность

• I — Isolation, изоляция

• D — Durability, устойчивость

Разберем по порядку что они означают и как влияют на решение прикладных задач.

Атомарность

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

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

Самое важное здесь то, что результат фиксируется целиком или никак.

Cогласованность

Для того, что бы говорить о согласованности, нужно ввести новый термин.

Инвариант — некоторое утверждение о данных, которое остается неизменным на протяжении выполнения программы.

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

Примеры:

• Количество денег на счету должно быть больше, либо равен 0

• Номер телефона пользователя должен быть уникальным

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

Важно понимать, что обеспечивается согласованность и выполнение тех инвариантов, которые известны базе данных. Для того, чтобы согласованность достигалась, надо понятно «объяснять» базе данных что требуется сделать.

Давайте подумаем, какие инварианты есть в нашем тестовом приложении с форумом.

• Каждый пользователь должен иметь уникальный email

• У каждого топика должен быть автор

• У каждой эмоции должен быть автор и топик

• Один пользователь может оставить к данному топику лишь одну эмоцию

Разберем их по порядку.

Для контроля уникальности у БД есть инструмент — уникальный индекс. Следует навесить уникальный индекс на полe email таблицы Users.

Для контроля следующего инварианта следует использовать ограничение внешнего ключа — FOREIGN KEY. Это обычно контролируется на уровне ORM при установке связи между моделями. Здесь же не стоит забывать и о такой вещи как каскадное удаление. Что будет, если мы удалим автора из системы? Если мы хотим, чтобы все его топики и эмоции были удалены, стоит на внешних ключах не забыть повесить ON DELETE CASCADE.

Кстати, еще одна вещь о которой часто забывают, это индексы на внешние ключи. Наличие на колонке ограничения внешнего ключа (FOREIGN KEY) не равно наличию там индекса! Без индекса вы будете страдать от двух проблем: а) медленный поиск связанных сущностей, например, поиск всех топиков данного автора; б) медленное каскадное удаление, т.к. при удалении автора базе данных нужно будет найти все топики, ссылающиеся на него что без индекса будет затруднительно.

Для контроля последнего инварианта из нашего списка нам снова понадобится уникальный индекс, только уже не на одну колонку, а на пару колонок (user_id, topic_id).

Уникальные индексы? И так сойдет!

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

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

const emoji = await Emotion.findOne({
    user_id: current_user_id,
    topic_id: current_topic_id
});
if (!emoji) {
    await Emotion.add({ ... })
}

Ну и в чем проблема? Все, вроде, супер...

Приходят два HTTP-запроса в бар...

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

1. Оба запроса попытаются найти в таблице эмоцию для данного пользователя и данного топика

2. Оба запроса не найдут

3. Оба запроса вставят реакцию в таблицу

Вуаля! У нас нарушение инварианта! В таблице лежит две реакции от одного пользователя на один топик. Если вам кажется что это ерунда, а не проблема — подставьте вместо эмоций и топиков, например, деньги на банковском счету!

Можно возразить, мол, откуда возьмутся эти конкурентные запросы? Да откуда угодно!

• Плохая связь — представьте, пользователь вашего приложением едет в метро. Нажали кнопку, запрос отправляется, но из‑за плохой связи не уходит. Пользователь жмет кнопку еще раз, и еще. И тут мы выезжаем из тоннеля и все запросы дружно достигают сервера

• Rage clicks — иногда пользователи просто чем‑то недовольны и жмут много раз, этому нет объяснимых причин, так устроен мир.

• Мамкины хакеры

• Ошибки в реализации frontend‑приложения

Да просто поверьте — они будут, я обещаю. При разработке API всегда задавайте себе вопрос — что будет с этой «ручкой» если ее вызовут одновременно несколько клиентов? А если разными аргументами? А если с одинаковыми?

Нормально решить эту проблему можно только уникальным индексом. Если бы он был, мы бы получили исключение при попытке вставки которому могли бы обработать. А если бы мы воспользовались функционалом ON CONFLICT DO NOTHING (см. документацию про INSERT, раздел ON CONFLICT Clause) можно обойтись и без исключений — запрос просто успешно завершится если по указанному ограничению будет конфликт.

Внимание! Индексы не серебряная пуля!

Не стоит думать, что вы можете обмазаться индексами, добавить их на все поля, и тогда точно ваше приложение будет самым быстрым и самым согласованным. К сожалению, это не так, может стать только хуже. Потому что индексы кушают время при вставке записей т.к. их нужно обновлять. А ещё они кушают место на диске вашего сервера.

Паттерн: прочитать, изменить, записать

Еще один популярный и хранящий в себе ошибки паттерн — это «прочитать, изменить, записать». Пример: считаем количество просмотров топиков в нашем форуме. Типовая «наивная» реализация:

const topic = await Topic.findOne({ topic_id: id });
topic.viewCount++;
await topic.save();

Все работает отлично, пока на сцене не появляются наши старые знакомые конкурентные запросы. Два пришедших одновременно запроса получат актуальное состояние из БД. Допустим топик имел 10 просмотров, затем каждый увеличит кол‑во на единицу до 11 и запишет новое значение (11) в базу. Итог: кол‑во просмотров 11, а не 12.

Для решения этой задачи у базы также есть правильный инструмент — атомарные операции. В данном случае нам нужно правильно объяснить базе, что же мы делаем, а именно сказать «увеличь на единицу поле Х записи Y». На SQL это будет выглядеть вот так:

UPDATE Topic
   SET viewCount = viewCount + 1
 WHERE id = $1

На языке вашей любимой ORM (например increment в Sequelize), я уверен, есть способ выразить то же самое. Изучите документацию.

Данной проблеме подвержены не только числа. Аналогичная история возможна когда вы работаете с:

• массивами — вставка или удаление элемента

• JSON‑документами — изменение информации по ключу, вставка, удаление

• редактирование текста

Для массива и JSON в арсенале БД есть подходящие атомарные операции.

Изоляция

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

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

Артефакты доступа к данным

Всего их 4:

• Потерянное обновление — в PostgreSQL не возможно, но стоит упомянуть для полноты картины

• Грязное чтение

• Неповторяющееся чтение

• Фантомное чтение

Потерянное обновление

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

Напомню, что в PostgreSQL такое не возможно. Чуть позже разберемся почему.

Грязное чтение

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

Результат: вторая транзакция считает, что поле y имеет значение 10, но это не так.

Неповторяющееся чтение

Транзакция 2 читает одну и ту же запись несколько раз. Но между этими чтениями другая транзакция изменяет эту запись и успешно фиксируется.

Результат: одна и та же строка в рамках одной транзакции выглядит по-разному, в зависимости от момента, в который она прочитана.

Фантомное чтение

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

Результат: транзакция увидела записи, созданные в рамках параллельной транзакции.

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

Уровни изоляции для транзакций

Символ ✅ означает, что данный уровень изоляции успешно справляется с указанным артефактом доступа к данным.

Желаемый уровень изоляции задается программистом при старте транзакции. Уровень Read committed в PostgreSQL является уровнем изоляции по‑умолчанию.

К сожалению, другие, более строгие, уровни изоляции достаются нам не бесплатно. Их применение может снизить пропускную способность вашего приложения и при разработке надо быть готовым, что БД попросит «повторить» транзакцию соответствующим исключением, что требует от разработчика написания более сложного кода приложения.

Давайте теперь разберемся, почему же в PostgreSQL работают атомарные операции и почему нет потерянного обновления.

Блокировки

PostgreSQL не страдает от потерянного обновления, т.к. при выполнении UPDATE над строкой, она блокируется до завершения транзакции. Выглядит это примерно так:

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

Опасности блокировок

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

Что бы понять один из возможных механизмов возникновения deadlock, рассмотрим пример. Пусть записи нашего форума публикуются, скажем, в телеграм‑канал. Какая‑то внешняя система периодически приходит к нам, и приносит статистику просмотров постов в канале в виде пар (topic_id, view_count). В одном запросе таких пар может быть сколько угодно. Периодичность обращений нами не регулируется. При успешной обработке пачки нужно вернуть ответ 200 или ошибку, если данные не обработаны.

Реализуем следуюший алгоритм:

1. Стартуем транзакцию

2. Для каждой пары (topic_id, view_count) обновим запись в таблице Topic используя атомарный инкремент для поля viewCount по ключу id

3. Зафиксируем транзакцию

Выглядит отлично. Но, к сожалению, однажды вы обнаружите что ваши процессы стоят в deadlock'е на базе данных. Вот как это получается:

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

На схеме видно, что сначала первая транзакция Т1 обновила запись по ключу 1, затем вторая транзакция Т2 обновила запись по ключу 2. Обе они заблокировали измененные записи. Т1 заблокировала id=1, а Т2 — id =2. Затем Т1 перешла к следующей записи и, вот это сюрприз, там оказалась запись id=2, которую она попыталась изменить и «уперлась» в блокировку, наложенную Т2. С Т2 случилась та же история, только теперь с записью id=1. И вот обе транзакции стоят и ждут освобождения блокировок, наложенных друг другом. И они никогда не дождутся. Тут либо сработает какой‑то таймаут, если разработчик его задал, либо уже прийдет DBA и убьет эти запросы.

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

Используем блокировки себе на пользу

Разработчик может использовать механизм блокировок БД себе на пользу самостоятельно блокирую некоторые записи для решения своих прикладных задач. Рассмотрим задачу, правда уже не на примере форума, а на примере софта, который управляет дежурствами в поликлинике. Пусть у нас есть следующие бизнес‑требования:

1. В поликлинике есть ежедневное дежурство

2. Обычно дежурят несколько врачей

3. Можно брать отгулы, но на дежурстве должен остаться хотя бы 1 врач

Пусть для хранения дежурств у нас будет использоваться следующая таблица

Здесь shift_id это идентификатор смены, а person_id — идентификатор врача. Разработчик, в задачу которого входила реализация API создания отгула, пишет следующий код:

const countOnDuty = await Duty.findAll({ shift_id });
if (countOnDuty.length > 1) {
  await Duty.remove({ shift_id, person_id });
}

Теперь рассмотрим как обычно два конкурирующих запроса. Два врача решили отказаться от одной и той же смены и пойти в отгул. Приложение отправляет запрос, поиск находит 2 записи. 2 > 1 значит можно пойти в отгул и удалить запись смены. Итог: в данной смене дежурит 0 врачей, каждый удалил свою смену, т.к. в момент проверки условие наличия хотя бы одного коллеги на дежурстве было верно.

Как починить? Давайте привлечем блокировки на свою сторону. Например, при поиске всех тех, кто работает в данную смену, заблокируем все найденные записи. Для этого в SQL есть конструкция SELECT... FOR UPDATE. Ее можно выразить и средствами ORM, примерно так:

// напомню, это псевдокод...
transaction(async (t) => { // <-- стартовали транзакцию
  const countOnDuty = await Duty.findAll({ 
    shift_id 
  }, { 
    transaction: t,
    lock: FOR_UPDATE // <-- заблокировали записи
  });
  if (countOnDuty.length > 1) {
    await Duty.remove({ shift_id, person_id }, { transaction: t });
  }
})

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

Фух, на этом пока все!

К сожалению, это еще не все подводные камни, с которыми придется столкнуться начинающему backend‑разработчику. Если вас заинтересовало, как именно устроены блокировки и изоляция транзакций в вашей любимой БД — изучите документацию, у всех свои подходы к реализации. Если вам хочется подробнее изучить тему — рекомендую знаменитого «Кабанчика», книгу Мартина Клеппмана «Высоконагруженные приложения. Программирование, масштабирование, поддержка».

Спасибо за внимание!