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

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

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

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

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

Установка

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

akshar=# create table accounts (acct_id integer, amount integer); 
CREATE TABLE

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

akshar=# insert into accounts values (1, 500);
INSERT 0 1
akshar=# insert into accounts values (2, 300);
INSERT 0 1

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

akshar=# select * from accounts;
acct_id | amount
---------+--------
1 | 500
2 | 300
(2 rows)

Транзакция

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

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

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

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

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

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

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

akshar=# begin transaction;
BEGIN
akshar=# update accounts set amount=amount-10 where acct_id=1; UPDATE 1
akshar=# update accounts set amount=amount+10 where acct_id=2; UPDATE 1
akshar=# commit;
COMMIT

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

akshar=# select * from accounts;
acct_id | amount
---------+--------
1 | 490
2 | 310
(2 rows)

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

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

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

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

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

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

akshar=# begin transaction;
BEGIN
akshar=# update accounts set amount=amount-10 where acct_id=1; 
UPDATE 1

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

akshar=# begin transaction;
BEGIN
akshar=# update accounts set amount=amount-20 where acct_id=2; 
UPDATE 1

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

akshar=# update accounts set amount=amount+10 where acct_id=2;

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

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

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

akshar=# update accounts set amount=amount+20 where acct_id=1;

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

ERROR: deadlock detected
DETAIL: Process 77716 waits for ShareLock on transaction 173312; blocked by process 76034.
Process 76034 waits for ShareLock on transaction 173313; blocked by process 77716.
HINT: See server log for query details. CONTEXT: while updating tuple (0,3) in relation "accounts"

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

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

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

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

akshar=# update accounts set amount=amount+10 where acct_id=2; 
UPDATE 1

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

akshar=# commit;
ROLLBACK

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

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

akshar=# commit;
COMMIT

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

akshar=# select * from accounts;
acct_id | amount
---------+--------
1 | 480
2 | 320
(2 rows)

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

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

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

Читайте также:

• Не используйте ID, сгенерированные базой данных для доменных сущностей
• Как сделать приложение-чат с Redis, WebSocket и Go
• NoSQL убивает SQL?

Читайте нас в Telegram, VK и Яндекс.Дзен

---

Перевод статьи Akshar Raaj: Understanding why a database deadlock occurs