Блокировка данных в базах данных

08.01.2020 / development
Конспект по видам блокировок в СУБД, в основном в MySQL (InnoDB), но часть материала также актуальна и для других СУБД, так как носит теоретический характер.

Блокировка (lock) - отметка о захвате объекта транзакцией с целью предотвращения коллизий и поддержания целостности данных.

БОльшая часть рассмотренного материала относится к движку InnoDB СУБД MySQL. Вся практическая часть тестировалась именно на этом ПО. Но некоторые данные актуальны и для других СУБД.

Классификация блокировок

По области действия

SELECT ... WHERE id=10 FOR UPDATE
SELECT ... WHERE age>18 AND age<25 FOR UPDATE

По строгости

По логике реализации

Можно самостоятельно имитировать данные блокировки, например при помощи LOCK TABLES/UNLOCK TABLES

По типам запросов

Блокировка индексов

InnoDB накладывает блокировки не на сами строки с данными, а на записи индексов.

Блокировка записи индекса

Если осуществляется селективная блокировка с условием WHERE id=10, и в таблице действительно существует запись с таким id, тогда речь идет о блокировке записи индекса (блокировка строки).

Блокировка промежутка

Блокировка промежутка (gap lock) - блокировка интервала между, до или после индексной записи. Блокирует соседний несозданный индекс или промежуток несозданных индексов, с целью предотвращения фантомного чтения. Работает при режиме изоляции транзакций REPEATABLE READMySQL по умолчанию).

Если в таблице есть индекс (например id) со значениями [1-10, 20-30], то есть промежуток между 10 и 20 индексами пуст, то при селективной блокировке WHERE id=15 будет заблокирован весь промежуток несуществующих индексов [11-19].

SELECT * FROM t WHERE id=15

Запрос к performance_schema.data_locks в столбце lock_mode покажет 2 типа блокировки, вторым будет являться GAP, а в столбце lock_data значение индекса, до которого произведена блокировка.

Если в предыдущем примере таблица будет пуста, то селективная блокировка любого индекса приведет к блокировке всего индексного пространства - будет заблокирована вся таблица.

Блокировка следующего ключа

Блокировка следующего ключа (next-key lock) - это блокировка индексов следующих за текущим, предотвращает вставку новых индексов другими транзакциями во время выполнения текущей.

SELECT ... WHERE id>100 FOR UPDATE

Если id является индексом (primary key в MySQL по умолчанию ключ), то вставка новых строк с id>100 будет ожидать разблокировки индекса.

Селективная блокировка

Блокировка при выборке (селективная) работает только в транзакции (START TRANSACTION или SET autocommit=0). После COMMIT или ROLLBACK установленные транзакцией блокировки снимаются.

SELECT ... FOR UPDATE - выбранные строки блокируются для обновления. Параллельный запрос, в условие которого попадает хотя бы одна заблокированная строка, будет ожидать разблокировки этой строки.

Ожидание может закончиться general error 1205 в связи с таймаутом установленном в конфиге (в MySQL innodb_lock_wait_timeout по умолчанию 50 секунд).

SELECT ... FOR SHARE (в прошлом LOCK IN SHARE MODE) - совместная блокировка на чтение. Транзакция, которая заблокировала может изменять, остальные транзакции могут читать, но для изменения заблокированных данных будут ждать разблокировки.

Оба вида блокировки имеют дополнительные возможности:

FOR UPDATE/SHARE может использоваться с OF для указания названия таблицы, например в случае JOIN:

SELECT * FROM t1 JOIN t2 ... FOR UPDATE OF t1 FOR SHARE OF t2

Deadlocks

Deadlock (тупик, тупиковая ситуация) - это ситуация когда разные транзакции не могут быть выполнены, так как каждая из них содержит блокировку необходимую для другой. Обе они держат блокировки и ждут друг друга.

Например, есть 2 транзакции А и Б (точнее потоки, которые ведут транзакции, хотя бы как START TRANSACTION; ...; COMMIT;):

В итоге образуется взаимоблокировка (deadlock), так как А ждет разблокировки от Б, а Б ждет разблокировки от А.

Движок innodb не может обнаружить deadlock при использовании LOCK TABLES, либо блокировку, установленную отличным от innodb обработчиком таблиц. В остальных случаях при обнаружении взаимоблокировки будет выдана ошибка.

Как избежать deadlock?

SET AUTOCOMMIT=0;
LOCK TABLES;
...
COMMIT;
UNLOCK TABLES;

Информация о заблокированных строках

В MySQL есть возможность получить информацию о блокировках в текущий момент, эта информация храниться в performance_schema.data_locks. Пример запроса:

SELECT `object_name`, `index_name`, `lock_type`, `lock_mode`, `lock_data` 
FROM `performance_schema`.`data_locks` 
WHERE `object_name`='название_таблицы'

Более подробную информацию можно найти здесь.

Эксперимент

Предположим есть таблица, с primary key id, строки в таблице имеют следующие id: [1-9, 11, 13-19, 21-30], то есть нет id 10, 12 и 20

Сделаем 4 потока:

START TRANSACTION;
SELECT * FROM `t1` WHERE id>10 AND id<20 FOR UPDATE;
SELECT SLEEP(30);
COMMIT;
INSERT INTO `t1`(`id`, `name`) VALUES (12,0);
INSERT INTO `t1`(`id`, `name`) VALUES (10,0);
INSERT INTO `t1`(`id`, `name`) VALUES (20,0);

В результате потоки 2 3 4 будут ждать пока первый поток закончит работу. Поток 2 ожидает потому что сработала блокировка индексов, а потоки 3 и 4 ждут потому что действует gap lock.

Если вставить запись с id 20 и повторить запуск потоков, то поток 4 не станет ждать окончания работы потока 1 и сразу сообщит о том, что запись с таким id есть.

Если в четвертом потоке вместо 20 вставить 31 id, тогда четвертый поток не будет ждать первый и вставит новую запись.

Блокировка на уровне таблиц

Намеренная

Намеренные блокировки (intention locks) - это блокировки на уровне таблицы, которые указывают какой тип блокировки требуется транзакции позднее для блокировки в таблице строк/строки.

Основная цель намеренной блокировки показать (намерение) что будет заблокирована строка, чтобы заблокировать создание других намеренных блокировок.

Существует 2 типа намеренных блокировок:

При обращении к performance_schema.data_locks (например в случае селективной блокировки) в первой строке можно увидеть что:

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

AUTO-INC

AUTO-INC lock - специальная блокировка на уровне таблицы, где есть столбцы с AUTO_INCREMENT. Такая блокировка происходит при INSERT запросе: транзакции становятся в очередь чтобы получить последовательные значения автоинкрементируемого столбца.

LOCK TABLES

LOCK TABLES - явная блокировка таблиц для текущей сессии, неявно освобождает любые текущие блокировки в текущей сессии.

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

2 вида блокировки:

WRITE имеет больший приоритет нежели READ. Например: поток 1 запросил READ, за ним поток 2 запросил WRITE, затем поток 3 READ, а поток 4 WRITE. В итоге последовательность очереди будет такая: 1, 2, 4, 3

В текущем сеансе при включенном LOCK TABLES доступ можно получить только к заблокированным таблицам, при обращении к незаблокированным таблицам будет ошибка.

Нельзя ссылаться на одну и ту же таблицу несколько раз, надо заблокировать таблицу с помощью псевдонима, например:

LOCK TABLE t WRITE, t as t1 READ;
INSERT INTO t SELECT * FROM t as t1;

Если запрос выполняет транзакцию, либо соединение сессии разрывается (как обычно или ненормально) то выполняется неявный UNLOCK TABLES.

Если была активная (незавершенная) транзакция, то при завершении сеанса она откатится.

LOCK TABLE неявно фиксирует любую активную транзакцию. UNLOCK TABLE тоже неявно фиксирует любую активную транзакцию, только если она началась после LOCK TABLE.

Использование транзакций и LOCK TABLE/UNLOCK TABLE:

SET autocommit=0;
LOCK TABLES ...;
...
COMMIT;
UNLOCK TABLES;

Список литературы