Почему в базе данных происходит взаимоблокировка

Почему в базе данных происходит взаимоблокировка?

Взаимоблокировка в базе данных

Круг вопросов для обсуждения

Попробуем объяснить, что такое взаимная блокировка и почему она возникает в базе данных.

Напишем SQL-инструкции и искусственно вызовем взаимоблокировку, а также обсудим возможность предотвратить или хотя бы минимизировать эти взаимоблокировки.

В качестве базы данных будем использовать PostgreSQL.

Установка

Давайте запустим оболочку PostgreSQL и создадим таблицу под названием accounts (счета):

Теперь вставим в эту таблицу две строчки:

Проверим, что строки вставлены:

Транзакция

Для правильного понимания взаимоблокировки необходимо иметь общее представление о транзакции.

Транзакция — это объект СУБД, который поддерживает базу данных в согласованном, целостном и надёжном состоянии. В «Википедии» даётся такое определение транзакции:

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

Транзакция обеспечивает соответствие требованиям ACID. Прочтите эту статью для понимания ACID.

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

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

Давайте запустим оболочку psql и осуществим транзакцию по переводу денег с одного счёта на другой:

Проверим суммы на обоих счетах:

Это означает, что перевод прошёл успешно и код транзакции верен.

Вызываем взаимоблокировку

А теперь мы искусственно создадим ситуацию взаимоблокировки.

В первой оболочке будет имитироваться процесс, в ходе которого выполняется перевод суммы со счёта 1 на счёт 2. Во второй оболочке будет имитироваться процесс, в ходе которого выполняется перевод суммы со счёта 2 на счёт 1.

Со счёта 1 на счёт 2 переводится сумма 10. Выполняем этот перевод в первой оболочке:

Одновременно, т. е. до завершения первой транзакции, со счёта 2 на счёт 1 переводится сумма 20. Выполняем этот перевод во второй оболочке:

Предположим, СУБД дала первому процессу возможность выполниться вновь. Сымитируем его выполнение, зачислив сумму 10 на счёт 2 в первой оболочке:

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

Это произойдёт потому, что строка с acct_id=2 в этот момент окажется заблокированной из-за того, что процесс 2 производил обновление этой строки. Процесс 1 не может захватить эту блокировку, пока процесс 2 не освободит её.

Дальше база данных даст возможность выполниться второму процессу. Сымитируем его выполнение, зачислив сумму 20 на счёт 1 во второй оболочке:

База данных вернёт ERROR: deadlock detected (ОШИБКА: обнаружена взаимоблокировка).

Опишем, что здесь произошло. Строка базы данных, содержащая acct_id=1, была заблокирована процессом 1 при списании суммы 10 со счёта 1. Дальше процесс 2 попытался произвести обновление в этой же строке, а для этого ему нужна блокировка. Процесс 2 может захватить блокировку только в том случае, если процесс 1 освободит её. Но процесс 1 блокируется в ожидании, когда процесс 2 освободит блокировку строки acct_id=2. Получается, что процесс 1 ожидает освобождения блокировки, захваченной процессом 2, а процесс 2 ожидает освобождения блокировки, захваченной процессом 1. Это и есть взаимоблокировка.

Для базы данных не составляет труда обнаружить взаимную блокировку.

В этом случае в базе данных выдаётся ошибка взаимоблокировки в оболочке процесса 2. После возникновения ошибки взаимоблокировки все блокировки, захваченные этим процессом, освобождаются, а у процесса 1 появляется возможность захватить необходимую ему блокировку.

Посмотрите, что происходит тогда в оболочке процесса 1: заблокированная команда возвращается, а сумма 10 зачисляется на счёт 2:

Попробуем сделать commit в оболочке процесса 2:

При обнаружении взаимоблокировки не происходит успешного выполнения команд, и поэтому коммит может привести базу данных в несогласованное и ненадёжное состояние. СУБД способна правильно оценить это, так что при выполнении commit происходит rollback (откат транзакции).

В случае успешного выполнения команд процесса 1 commit может быть сделан в оболочке процесса 1:

Вы можете проверить, что сумма 10 была списана со счёта 1 и зачислена на счёт 2:

Отладка взаимоблокировок

Параллельное выполнение — это неизбежная реальность. Ситуации, когда несколько процессов одновременно пытаются обновить один и тот же набор строк и блокируются друг другом, вполне возможны. Взаимная блокировка в таких случаях неизбежна.

Устранение и предотвращение взаимоблокировок должно осуществляться на уровне приложения. С этой целью используется код обработки исключений: он отлавливает ошибки взаимоблокировки, давая возможность повторно выполнить неудавшуюся транзакцию.