Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как может возникнуть взаимоблокировка в системе реляционных баз данных и как Oracle, SQL Server, PostgreSQL или MySQL восстанавливаются после ситуации взаимоблокировки.
Блокировка базы данных
Системы реляционных баз данных используют различные блокировки для гарантии свойств ACID транзакций .
Например, независимо от того, какую систему реляционных баз данных вы используете, блокировки всегда будут получены при изменении (например, ОБНОВЛЕНИЕ или УДАЛЕНИЕ ) определенной записи таблицы. Без блокировки строки, измененной текущей транзакцией, Атомарность будет нарушена .
Использование блокировки для управления доступом к общим ресурсам может привести к взаимоблокировкам, и только планировщик транзакций не может предотвратить их возникновение.
Взаимоблокировка базы данных
Взаимоблокировка возникает, когда две одновременные транзакции не могут выполняться, поскольку каждая из них ожидает, пока другая снимет блокировку, как показано на следующей диаграмме.
Поскольку обе транзакции находятся на этапе получения блокировки, ни одна из них не снимает блокировку до получения следующей.
Выход из тупиковой ситуации
Если вы используете алгоритм управления параллелизмом, основанный на блокировках, то всегда существует риск возникновения тупиковой ситуации. Взаимоблокировки могут возникать в любой среде параллелизма, а не только в системе баз данных.
Например, многопоточная программа может привести к взаимоблокировке, если два или более потоков ожидают блокировок, которые были получены ранее, так что ни один поток не сможет добиться какого-либо прогресса. Если это происходит в приложении Java, JVM не может просто заставить поток остановить его выполнение и снять блокировки.
Даже если класс Thread предоставляет метод stop , этот метод устарел с Java 1.1, поскольку он может привести к тому, что объекты останутся в несогласованном состоянии после остановки потока. Вместо этого Java определяет метод прерывание , который действует как подсказка, поскольку поток, который прерывается, может просто игнорировать прерывание и продолжить его выполнение.
По этой причине Java-приложение не может выйти из тупиковой ситуации, и разработчик приложения несет ответственность за то, чтобы запросы на получение блокировки были упорядочены таким образом, чтобы никогда не возникало взаимоблокировок.
Однако система базы данных не может принудительно выполнить данный порядок получения блокировки, поскольку невозможно предвидеть, какие другие блокировки захочет получить определенная транзакция в дальнейшем. Сохранение порядка блокировки становится обязанностью уровня доступа к данным, и база данных может помочь только в восстановлении из тупиковой ситуации.
Компонент database engine запускает отдельный процесс, который сканирует текущий график конфликтов на наличие циклов ожидания блокировки (которые вызваны взаимоблокировками). При обнаружении цикла компонент database engine выбирает одну транзакцию и прерывает ее, в результате чего ее блокировки снимаются, чтобы другая транзакция могла выполняться.
В отличие от JVM, транзакция базы данных спроектирована как атомарная единица работы. Следовательно, откат оставляет базу данных в согласованном состоянии.
Приоритет тупиковой ситуации
В то время как база данных выбирает откат одной из двух застрявших транзакций, не всегда возможно предсказать, какая из них будет откатана. Как правило, база данных может выбрать откат транзакции с более низкой стоимостью отката.
Оракул
Согласно документации Oracle , транзакция, обнаружившая откат, является той, оператор которой будет откатан.
SQL Server
SQL Server позволяет вам контролировать, какая транзакция с большей вероятностью будет откатана во время ситуации взаимоблокировки, с помощью переменной DEADLOCK_PRIORITY сеанса.
Сеанс DEADLOCK_PRIORITY может принимать любое целое число от -10 до 10 или предопределенные значения, такие как НИЗКИЙ (-5) , НОРМАЛЬНЫЙ (0) или ВЫСОКИЙ (5) .
В случае взаимоблокировки текущая транзакция будет откатана, если только другие транзакции не имеют меньшего значения приоритета взаимоблокировки. Если обе транзакции имеют одинаковое значение приоритета, SQL Server выполняет откат транзакции с наименьшими затратами на откат.
PostgreSQL
Как объясняется в документации , PostgreSQL не гарантирует, какая транзакция должна быть откатана.
MySQL
MySQL пытается откатить транзакцию , которая изменила наименьшее количество записей , так как освобождение меньшего количества блокировок обходится дешевле.
Вывод
Понимание того, как могут возникать взаимоблокировки при использовании системы баз данных, очень важно, поскольку это позволяет правильно обрабатывать неожиданные откаты транзакций.
Предотвращение отката не всегда возможно, и в случае возникновения взаимоблокировки клиент должен повторить операции, которые ранее были выполнены прерванной транзакцией.
Системы баз данных, использующие механизм управления параллелизмом 2PL (Двухфазная блокировка) (например, SQL Server, MySQL на уровне сериализуемой изоляции), более подвержены блокировкам по сравнению с системами баз данных, которые полагаются на MVCC (Управление параллелизмом с несколькими версиями), такими как Oracle, PostgreSQL или MySQL на любом уровне изоляции ниже сериализуемого.