Любопытный случай взаимной блокировки транзакций в базе данных при использовании TransactionScope

Привет, Хабр! Тем временем у нас распродажа в честь черной пятницы. Там найдется много интересных книг по базам данных, и именно о взаимных блокировках при транзакциях в базах данных - сегодняшний пост.

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

У сервиса одновременно работает 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 весьма недопонимают (я точно недопонимал). Из-за этого можно подумать, что транзакции будут выполняться одна за другой детерминированным образом, поэтому и возникает вопрос: откуда взаимные блоуировки, если все блокировки приобретаются детерминированным образом. Но на самом деле все не совсем так! Если у вас создается впечатление, как будто транзакции происходят одна за другой, это еще не значит, что они физически идут друг за другом. Блокировки, принимаемые в рамках сериализуемой транзакции, не мешают другим сериализуемым транзакциям начаться, они только предотвращают фантомные считывания и грязные считывания незафиксированных данных. Это означает, что транзакции все равно будут перекрываться, и порядок «одна за другой» достигается только ценой взаимных блокировок, ответственность за которые ложится на программиста, то есть, на вас!