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

• Классификация блокировок
  • По области действия
  • По строгости
  • По логике реализации
  • По типам запросов
• Блокировка индексов
  • Блокировка записи индекса
  • Блокировка промежутка
  • Блокировка следующего ключа
  • Селективная блокировка
  • Deadlocks
    • Как избежать deadlock?
  • Информация о заблокированных строках
  • Эксперимент
• Блокировка на уровне таблиц
  • Намеренная
  • AUTO-INC
  • LOCK TABLES
• Список литературы

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

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

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

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

• строгая - только на строку, например:

SELECT ... WHERE id=10 FOR UPDATE

• гранулярная - на всю таблицу или на все строки на странице. Такая блокировка иногда называется страничной (page locking). Например LOCK TABLES/UNLOCK TABLES
• предикатная - распространяется на область ограниченную предикатами, например:

SELECT ... WHERE age>18 AND age<25 FOR UPDATE

#По строгости

• совместная (share (S) - SELECT ... FOR SHARE) - накладывается на объект если операция (SELECT) безопасна, то есть не меняет данных и не имеет побочных эффектов, другие транзакции могут делать тоже самое
• исключительная/эксклюзивная (exclusive (X) - SELECT ... FOR UPDATE) - накладывается на объект если операция изменяет данные (UPDATE). Только одна транзакция может выполнять такое дейсвтие в данный момент времени. Исключительная блокировка не может быть наложена на совместную.

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

• пессимистичная - предполагает что конфликт обязательно произойдет, поэтому последовательность действий: lock - transaction - unlock
• оптимистичная - предполагает что конфликта не произойдет. Данные считываются, транзакция обрабатывается, производится обновление, затем проверяются конфликты, если их нет то транзакция завершается, иначе повторяется пока не завершится успехом

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

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

• селективная блокировка (SELECT с использованием FOR UPDATE/SHARE)
• блокировка обновления (UPDATE)
• блокировка удаления (DELETE)

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

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

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

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

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

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

Если в таблице есть индекс (например 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) - совместная блокировка на чтение. Транзакция, которая заблокировала может изменять, остальные транзакции могут читать, но для изменения заблокированных данных будут ждать разблокировки.

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

• SKIP LOCKED - позволяет пропустить строки заблокированные другими запросами, если в ответе есть незаблокированные строки
• NOWAIT - позволяет не ждать и выдаст ошибку (error 3572) если строки заблокированы другими тразакциями

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

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

#Deadlocks

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

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

• А делает совместную блокировку: SELECT ... WHERE id=1 FOR UPDATE
• Б делает эксклюзивную блокировку: DELETE ... WHERE id=1
• А делает эксклюзивную блокировку: DELETE ... WHERE id=1

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

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

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

• не оставлять сеанс открытым долгое время без COMMIT/ROLLBACK транзакции
• изменять разные таблицы или разные наборы строк согласовано, чтобы образовывалась очередь обработки
• добавить индексы таблицам, чтобы было меньше сканирования, а значит и меньше блокировок
• в крайнем случае сериализовать транзакции (ставить в очередь) либо через SERIALIZABLE уровень изоляции транзакций, либо вручную, например:

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`='название_таблицы'

• object_name - имя таблицы
• index_name - имя заблокированнго индекса
• lock_type - тип блокировки, TABLE - уровень таблицы, в ином случае уровень строки
• lock_mode - режим блокировки (S, X, IS, IX, возможно также GAP, AUTO-INC)
• lock_data - заблокированные данные

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

#Эксперимент

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

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

• Первый блокирует диапазон индексов и засыпает на 30 секунд:

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 типа намеренных блокировок:

• intention shared lock (IS) - намеренная совместная блокировка
• intention exclusive lock - (IX) намеренная эксклюзивная блокировка

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

• lock_type - TABLE
• lock_mode - IX или IS

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

#AUTO-INC

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

#LOCK TABLES

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

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

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

• READ [LOCAL] - блокировка таблицы для чтения, несколько сессий могут блокировать одновременно, модификатор LOCAL позволяет выполнять не конфликтующие INSERT (без LOCK TABLE). Для innodb READ == READ LOCAL
• WRITE - блокировка для чтения и записи, другие сеансы запросившие блокировку будут ожидать разблокировки

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;

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

• wiki
• MySQL Glossary
• Locking Reads (MySQL)
• InnoDB Locking (MySQL)
• Phantom Rows (MySQL)
• Deadlocks in InnoDB (MySQL)
• LOCK TABLES and UNLOCK TABLES Statements (MySQL)
• MySQL. Использование SKIP LOCKED и NOWAIT
• Блокировки и уровни изоляции транзакций InnoDB в MySQL