Привет, Хабр! Тем временем у нас распродажа в честь черной пятницы. Там найдется много интересных книг по базам данных, и именно о взаимных блокировках при транзакциях в базах данных - сегодняшний пост.
Пару лет назад мы с командой построили событийно-ориентированную систему, работавшую с транзакциями и время от времени застывавшую в загадочных взаимных блокировках. Как-то раз я решил заглянуть в нее поглубже и посмотреть, что же на самом деле там происходит. Этот небольшой пост можно считать отчетом, документирующим мои изыскания.
У сервиса одновременно работает 3 инстанса: по одному на хост EC2 в AWS. Сервис написан на C#/.NET Core 3.1 с использованием BackgroundService. Информация хранится в базе данных Aurora MySql. Сервис подхватывает события предметной области из множества очередей, обрабатывает их, меняя состояние локальных сущностей предметной области, после чего отправляя их обратно на долговременное хранение в базу данных MySql. Все это осуществляется конкурентно, иногда – прямо в транзакционных блоках базы данных, которые выглядят примерно так:
static async Task Main(string[] args)
{
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
await Save(product);
// мы можем обновить и больше сущностей
scope.Complete();
}
}
public static async Task Save(Product product)
{
using (var connection = new MySqlConnection(“...”))
{
await connection.OpenAsync();
// выбрать count(1) из Products где Id = @id
if (await Exists(product, connection))
// обновить Products set ... где Id = @id
await Update(product, connection);
else
// вставить в Products values(...)
await Insert(product, connection);
}
}Мы используем TransactionScope, так как операции в предметной области охватывают множество сущностей предметной области, и соответствующая логика доступа к данным для этих сущностей реализуется в отдельных классах. Единственный способ сложить их в единую транзакцию на уровне предметной области – использовать единицу выполнения работы, например, TransactionScope.
Однако, запуская этот код в продакшене, мы регулярно утыкались во взаимные блокировки:
Поэтому я решил вычленить путь выполнения транзакционного кода и запустить его в цикле Parallel.For, нацелившись на локальную базу данных MySql – и посмотреть, смогу ли я воспроизвести эти взаимные блокировки:
Parallel.For(0, 500,
async index =>
{
var product = new Product
{
Id = id,
Stock = index+1
};
using (var scope = new TransactionScope(
TransactionScopeAsyncFlowOption.Enabled))
{
await Save(product);
scope.Complete();
}
});Прошло не так много итераций, и ошибка воспроизвелась:
MySql.Data.MySqlClient.MySqlException (0x80004005): Взаимная блокировка обнаружена при попытке приобрести блокировку; попытка перезапуска транзакции …
Хммм!
Обратите внимание: взаимная блокировка сама по себе – еще не конец света, так как можно повторно выполнить заблокированную транзакцию, ведь она откатывается обратно в MySql. Это превращается в проблему, если такие блокировки происходят с некоторой регулярностью и начинают тормозить прогресс в выполнении задач и/или приводить к потере данных тем или иным образом. В нашем случае потеря данных не представляла особой проблемы, но сами по себе многократные блокировки вызывали беспокойство. В будущем эта беда вполне могла усугубиться в случае, если бы увеличился объем потока сообщений. Поэтому я решил исследовать эту проблему и по возможности ее устранить.
Сначала я заподозрил, что транзакция попросту протекает слишком долго из-за того, что мы применяем к ней операцию SELECT, а затем либо INSERT, либо UPDATE, именно поэтому возникает слишком продолжительный конфликт при блокировках. Но как рассмотреть, что в действительности здесь происходит?
Отладка блокировок в MySql
Есть как минимум два простейших способа посмотреть, что происходит с блокировками в MySql:
a. Просмотреть самые свежие блокировки, направив к базе данных запрос show engine innodb status. Получим вот такой вывод:
a. =====================================
b. 2021-07-23 21:27:55 0x7f0fec4a3700 INNODB MONITOR OUTPUT
c. =====================================
d. ...
e. ------------------------
f. LATEST DETECTED DEADLOCK
g. ------------------------
h. 2021-07-23 21:26:29 0x7f0fd3558700
i. *** (1) TRANSACTION:
j. TRANSACTION 2631, ACTIVE 0 sec starting index read
k. mysql tables in use 1, locked 1
l. LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
m. MySQL thread id 15, OS thread handle 139706363459328, query id 3531 172.20.0.1 root updating
n. update TrxDb.Products set stock = 495 where Id = 1000 and Version = 1
o.
p. *** (1) HOLDS THE LOCK(S):
q. RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2631 lock mode S locks rec but not gap
r. Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
s. ...
t.
u.
v. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
w. RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2631 lock_mode X locks rec but not gap waiting
x. Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
y. ...
z.
aa.
bb. *** (2) TRANSACTION:
cc. TRANSACTION 2632, ACTIVE 0 sec starting index read
dd. mysql tables in use 1, locked 1
ee. LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
ff. MySQL thread id 9, OS thread handle 139706363754240, query id 3533 172.20.0.1 root updating
gg. update TrxDb.Products set stock = 357 where Id = 1000 and Version = 1
hh.
ii. *** (2) HOLDS THE LOCK(S):
jj. RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2632 lock mode S locks rec but not gap
kk. Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
ll. ...
mm.
nn. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
oo. RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `TrxDb`.`Products` trx id 2632 lock_mode X locks rec but not gap waiting
pp. Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
qq. ...
rr.
ss. *** WE ROLL BACK TRANSACTION (2)
tt. ...
uu. ----------------------------
vv. END OF INNODB MONITOR OUTPUT
ww. ============================Так выводится список самых свежих блокировках, показывается, какие блокировки удерживались при транзакции, и какие блокировки находились в состоянии ожидания на момент взаимной блокировки.
b. Вывести все взаимные блокировки в логи MySql, включив (т.e. установив в “ON”, по умолчанию “OFF”) настройку innodb_print_all_deadlocks в системной переменной. Вывод обычно будет один и тот же, но в этом логе будут учтены все взаимные блокировки. В документации по MySql рекомендуется включать эту настройку только на время отладки, а затем отключать, как только проблема будет решена – вероятно, потому, что логирование взаимных блокировок может негативно повлиять на нормальную производительность транзакций. Правда, я оставил эту настройку включенной в нашей базе данных, даже когда она пошла в продакшен – и не заметил никакого существенного влияния на обработку транзакций.
Так или иначе, в данном случае я вижу, что обе транзакции 1 и 2 держат S (разделяемую) блокировку и ждут X (монопольную) блокировку, чтобы обновить строку. Таким образом, каждая из транзакций ждет блокировку, чтобы внести запись, но в то же время держит и другую блокировку. Эта другая блокировка должна быть высвобождена хотя бы одной из транзакций, прежде, чем они смогут получить монопольные блокировки. Вот вам и ВЗАИМНАЯ БЛОКИРОВКА!!!
Принцип работы этих блокировок очень хорошо документирован в MySql, поэтому, если хотите разобраться подробнее – почитайте там. Это очень глубокая тема, и сложно постоянно держать в голове все пограничные случаи.
Но давайте разберемся, почему S вообще блокируется? Я заинтересовался, не приводит ли к этому ненароком простая операция SELECT, поэтому также направил к SQL транзакцию с единственной SELECT, не фиксируя транзакцию и не откатывая ее назад, чтобы можно было применить диагностические запросы к транзакции, по-прежнему находящейся в состоянии RUNNING:
set autocommit = off;
start transaction;
select * from TrxDb.Products where Id = 1000;Чтобы посмотреть, блокировки какого рода берутся, я выполнил следующий диагностический запрос:
select * from performance_schema.data_locks;
Хмм! Никаких блокировок! Что, если оно блокируется, если одновременно выполняется операция UPDATE в той же транзакции?
set autocommit = off;
Start transaction;
select count(1) from TrxDb.Products where Id = 1000;
update TrxDb.Products set Stock = 8 where Id = 1000 and Version = 1;Результат диагностического запроса:
Oк! Операция UPDATE берет только блокировку X (как и должно быть), а блокировки S по-прежнему не происходит! Блокировку IX игнорируем, так как это плановая блокировка, которая, по-видимому, всегда предоставляется всем транзакциям, которые ее запрашивают. Кажется, она не сказывается ни на каких других блокировках или транзакциях!
Если я запускаю 2 транзакции почти одновременно (в двух отдельных вкладках/сеансах и со слегка отличающимися значениями данных), то вывод диагностического запроса показывает: одна транзакция получает блокировку X, а другая ожидает ее, но по-прежнему не просматривается блокировка S*:
Подождав еще немного, получаю ошибку LOCK WAIT TIMEOUT, а не DEADLOCK:
Интрига закручивается!!!
Влияет ли на блокировки уровень изоляции транзакций?
По умолчанию в MySql принят уровень изоляции REPEATABLE READ, поэтому я предположу, что в TransactionScope по умолчанию также действует уровень изоляции REPEATABLE READ, из-за чего взаимная блокировка становится еще более странной, ведь выполнение SQL-скрипта к взаимной блокировке не приводит. Поэтому я заглянул под капот TransactionScope и нашел вот что:
Здесь внутри создается экземпляр CommittableTransaction
Использующий SERIALISABLE в качестве уровня изоляции, заданного по умолчанию (это также документировано, но на тот момент я еще не удосужился закопаться в документацию так глубоко)!…ну… oк! Значит, вот в чем причина взаимной блокировки?
Я изменил мои SQL-скрипты так, чтобы установить уровень изоляции в значение SERIALISABLE, и снова запустил их одновременно (причина, по которой нужны искусственные задержки – так я хочу повысить вероятность взаимной блокировки, чуть-чуть изменив порядок, в котором приобретаются блокировки. Вероятно, это же происходит и в коде из репозитория, и вот почему должно пройти несколько итераций, прежде, чем начнут всплывать взаимные блокировки. Без этого единственная ошибка, которую я получаю - LOCK WAIT TIMEOUT! ) :
#Транзакция 1
set autocommit = off;
set SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
start transaction;
select * from TrxDb.Products where Id = 1000;
do sleep(8);
update TrxDb.Products set stock = 8 where Id = 1000 and Version = 1;
#Транзакция 2
set autocommit = off;
set SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
start transaction;
do sleep(5);
select * from TrxDb.Products where Id = 1000;
update TrxDb.Products set stock = 8 where Id = 1000 and Version = 1;И…ВЗАИМНАЯ БЛОКИРОВКА!!!
В результатах диагностического запроса показаны блокировки:
Обе транзакции приобрели S-блокировки, а потом остались дожидаться X-блокировки, поскольку, как только принята S-блокировка, никакие другие блокировки не предоставляются. Именно это я и видел в исходном выводе лога с взаимной блокировкой с самого начала.
Но почему операция SELECT принимает S-блокировку при уровне изоляции serialisable**?
Оказывается, что ответ на первый вопрос совсем прост и даже как-то ломает интригу:
SERIALIZABLE
Этот уровень подобен REPEATABLE READ, но InnoDB неявно преобразует все обычные операции SELECT в SELECT … FOR SHARE, если отключена автофиксация. Если автофиксация включена, то SELECT – это самостоятельная транзакция. Соответственно, известно, что она предназначена только для чтения и поддается сериализации, если выполняется как согласованное (неблокирующее) чтение, и ее не нужно блокировать, чтобы могли пройти другие транзакции. (Чтобы принудительно заставить обычную SELECT блокироваться, если другие транзакции модифицировали выбранные ряды, отключите автофиксацию.)Документация MySql
А SELECT…FOR SHARE так устроена, что принимает блокировку S – значит, это и есть первопричина блокировки S!
Как мы это исправили?
Часто взаимные блокировки могут свидетельствовать о том, что при проектировании приложения были допущены ошибки. Но я заметил, что нам, в самом деле, не требуется выполнять на уровне данных операции типа UPSERT. Вместо этого операцию такого типа можно разделить на две независимые операции INSERT и UPDATE, перенеся таким образом акт принятия решения на уровень предметной области.
Разделяя UPSERT, мы, фактически, не просто укорачиваем отдельные транзакции, но и делаем их более явными и простыми. Затем код предметной области может определять, создавать ли новую запись, если записи для данного id пока не существует, либо обновить уже имеющуюся тем состоянием, которое было принесено в событии. С любой практической точки зрения это решение должно приниматься именно на уровне предметной области, а не на уровне данных, поскольку именно в предметной области найдется достаточный контекст и осведомленность о том, хватает ли у нас информации для продолжения работы. Проверка на “существование” не требует что-либо блокировать, поэтому при таком разделении уменьшиться и общее количество блокировок. Может начаться гонка, когда проверка на сервере A сообщает: «товар не существует, создать его», и одновременно на другом сервере B происходит та же проверка, но там этот товар создается раньше, и тогда на сервере A операция не удается из-за нарушения первичного ключа. Но, поскольку наша система основана на сообщениях, простая повторная попытка, сделанная чуть позже, позволит корректно обновить запись, и система снова станет согласованной. С небольшой отложенной обработкой мы справимся.
Разумеется, все это будет варьироваться от системы к системе, поэтому предложенное решение может оказаться правильным и желательным не в любой ситуации. Но при извлечении данных для определения того, требуется ли нам вставка или обновление, все в одной транзакции, по определению приводит к более долгоиграющим транзакциям, поэтому могут создаться условия для конфликта при блокировках и, соответственно, могут случиться взаимные блокировки (что и подчеркивается в этом посте).
Весь код к посту (плюс некоторые дополнительные сценарии) выложен на GitHub!
*Разумеется, здесь я выполняю транзакции не так, как в коде, поскольку вообще не делаю фиксации транзакций (так как хочу отловить их, пока они работают), и у этого есть побочный эффект: транзакции удерживают блокировки дольше, чем требуется. Но тот факт, что код все равно приходит к взаимной блокировке, приводит меня к обоснованной догадке, что на данный момент сделанная мной конфигурация еще не совершенна, но не безнадежна! Если вы знаете лучший или более надежный способ поставить такой эксперимент – высказывайтесь в комментариях!
** Оказывается, что уровень изоляции serialisable весьма недопонимают (я точно недопонимал). Из-за этого можно подумать, что транзакции будут выполняться одна за другой детерминированным образом, поэтому и возникает вопрос: откуда взаимные блоуировки, если все блокировки приобретаются детерминированным образом. Но на самом деле все не совсем так! Если у вас создается впечатление, как будто транзакции происходят одна за другой, это еще не значит, что они физически идут друг за другом. Блокировки, принимаемые в рамках сериализуемой транзакции, не мешают другим сериализуемым транзакциям начаться, они только предотвращают фантомные считывания и грязные считывания незафиксированных данных. Это означает, что транзакции все равно будут перекрываться, и порядок «одна за другой» достигается только ценой взаимных блокировок, ответственность за которые ложится на программиста, то есть, на вас!