📬 Пост 48. Блокировка записей в БД, конкурентное чтение и как не получить рассинхрон

📝 Представим таблицу accounts. На счёте пользователя 10 000 ₽. Почти одновременно приходят два запроса: сервис A хочет списать 7 000 ₽; сервис B хочет списать 5 000 ₽. Оба запроса читают баланс: balance = 10 000 Оба делают вывод: Денег хватает, можно списывать. Но если операции выполняются некорректно, итоговый баланс может стать отрицательным или одно из изменений вообще потеряется.

📍 В чём проблема Это пример конкурентного доступа: несколько транзакций одновременно работают с одними и теми же данными. Наиболее известный сценарий это lost update, или потерянное обновление: Транзакция A: SELECT balance → 10 000 Транзакция B: SELECT balance → 10 000 A рассчитывает: 10 000 - 7 000 = 3 000 B рассчитывает: 10 000 - 5 000 = 5 000 A: UPDATE accounts SET balance = 3000 B: UPDATE accounts SET balance = 5000 Финальный баланс 5 000 ₽. Изменение транзакции A потерялось, хотя обе транзакции могли завершиться успешно. В другом варианте обе операции могут примениться последовательно: 10 000 - 7 000 - 5 000 = -2 000

🔏 Пессимистическая блокировка Один из вариантов решения это заблокировать строку перед чтением и изменением: BEGIN; SELECT balance FROM accounts WHERE id = 123 FOR UPDATE; FOR UPDATE сообщает базе данных: Я собираюсь изменить эту запись. Не позволяй другим транзакциям одновременно изменить её или получить конфликтующую блокировку. После этого выполняются проверка и списание: UPDATE accounts SET balance = balance - 7000 WHERE id = 123; COMMIT; Если вторая транзакция тоже выполнит: SELECT balance FROM accounts WHERE id = 123 FOR UPDATE; она будет ждать завершения первой: A → FOR UPDATE → блокировка строки A → проверка баланса A → UPDATE A → COMMIT B → FOR UPDATE → получает освобождённую строку B → перечитывает актуальный баланс ❗️ Важно: блокировка удерживается до COMMIT или ROLLBACK. Поэтому транзакция должна быть короткой. Не следует держать её во время сетевых вызовов, ожидания пользователя или долгих вычислений.

❓ Можно ли читать заблокированную запись Да, обычный SELECT часто может прочитать запись, даже если другая транзакция удерживает блокировку: SELECT balance FROM accounts WHERE id = 123; В СУБД с MVCC такой запрос обычно получит последнюю зафиксированную версию строки. Незакоммиченные изменения другой транзакции он не увидит. Но запрос с блокировкой: SELECT balance FROM accounts WHERE id = 123 FOR UPDATE; будет ждать освобождения строки или завершится ошибкой, если используется режим NOWAIT.

🔐 Оптимистическая блокировка Другой подход это не блокировать строку при чтении, а проверять её версию во время обновления. Добавим поле: version = 10 Читаем: SELECT balance, version FROM accounts WHERE id = 123; Получаем: balance = 10 000 version = 10 После расчёта обновляем запись: UPDATE accounts SET balance = 3000, version = 11 WHERE id = 123 AND version = 10; Если другая транзакция уже изменила запись, условие version = 10 не выполнится: UPDATE → 0 rows affected Это означает: Пока выполнялся расчёт, запись изменилась. Дальше сервис может: 🔹 перечитать актуальную версию; 🔹 пересчитать операцию; 🔹 повторить попытку; 🔹 вернуть конфликт клиенту.

Что такое deadlock Блокировки могут привести к взаимной блокировке: A заблокировала строку 1 B заблокировала строку 2 A ждёт строку 2 B ждёт строку 1 Ни одна транзакция не может продолжить работу. Это deadlock. Обычно СУБД обнаруживает такую ситуацию и принудительно откатывает одну из транзакций. Приложение должно уметь обработать эту ошибку и при необходимости повторить операцию.

#БД#Транзакции#Системный_анализ