Содержание
Блокировка (lock) - отметка о захвате объекта транзакцией с целью предотвращения коллизий и поддержания целостности данных.
InnoDB
СУБД MySQL
. Вся практическая часть тестировалась именно на этом ПО. Но некоторые данные актуальны и для других СУБД.sqlSELECT ... WHERE id=10 FOR UPDATE
LOCK/UNLOCK TABLES
sqlSELECT ... WHERE age>18 AND age<25 FOR UPDATE
SELECT ... FOR SHARE
) - накладывается на объект если операция (SELECT
) безопасна (не меняет данных и не имеет побочных эффектов). Другие транзакции могут делать тоже самоеSELECT ... FOR UPDATE
) - накладывается на объект если операция изменяет данные (UPDATE
). Только одна транзакция может выполнять такое дейсвтие в данный момент времени. Исключительная блокировка не может быть наложена на совместную.lock - transaction - unlock
LOCK/UNLOCK TABLES
SELECT
с использованием FOR UPDATE/SHARE
)UPDATE
)DELETE
)Если осуществляется селективная блокировка с условием WHERE id=10
, и в таблице действительно существует запись с таким id
, тогда речь идет о блокировке записи индекса (блокировка строки).
Блокировка промежутка (gap lock) - блокировка интервала между, до или после индексной записи. Блокирует соседний несозданный индекс или промежуток несозданных индексов, с целью предотвращения фантомного чтения. Работает при режиме изоляции транзакций REPEATABLE READ
(в MySQL
по умолчанию).
Если в таблице есть индекс (например id
) со значениями [1-10, 20-30], то есть промежуток между 10 и 20 индексами пуст, то при селективной блокировке WHERE id=15
будет заблокирован весь промежуток несуществующих индексов [11-19].
sqlSELECT * FROM t WHERE id=15
Запрос к performance_schema.data_locks
в столбце lock_mode
покажет 2 типа блокировки, вторым будет являться GAP
, а в столбце lock_data
значение индекса, до которого произведена блокировка.
Блокировка следующего ключа (next-key lock) - это блокировка индексов следующих за текущим, предотвращает вставку новых индексов другими транзакциями во время выполнения текущей.
Например:
sqlSELECT ... WHERE id>100 FOR UPDATE
Если id
является индексом (primary key
в MySQL по умолчанию ключ), то вставка новых строк с id>100
будет ожидать разблокировки индекса.
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
:
sqlSELECT * FROM t1 JOIN t2 ... FOR UPDATE OF t1 FOR SHARE OF t2
Deadlock (тупик, тупиковая ситуация) - это ситуация когда разные транзакции не могут быть выполнены, так как каждая из них содержит блокировку необходимую для другой. Обе они держат блокировки и ждут друг друга.
Например, есть 2 транзакции А и Б (точнее потоки, которые ведут транзакции, хотя бы как START TRANSACTION; ...; COMMIT;
):
А
делает совместную блокировку: SELECT ... WHERE id=1 FOR UPDATE
Б
делает эксклюзивную блокировку: DELETE ... WHERE id=1
А
делает эксклюзивную блокировку: DELETE ... WHERE id=1
innodb
не может обнаружить deadlock при использовании LOCK TABLES
, либо блокировку, установленную отличным от innodb
обработчиком таблиц. В остальных случаях при обнаружении взаимоблокировки будет выдана ошибка.COMMIT/ROLLBACK
транзакцииSERIALIZABLE
уровень изоляции транзакций, либо вручную, например: sqlSET AUTOCOMMIT=0; LOCK TABLES; ... COMMIT; UNLOCK TABLES;
В MySQL есть возможность получить информацию о блокировках в текущий момент, эта информация храниться в performance_schema.data_locks
. Пример запроса:
sqlSELECT `object_name`, `index_name`, `lock_type`, `lock_mode`, `lock_data` FROM `performance_schema`.`data_locks` WHERE `object_name`='название_таблицы'
S
, X
, IS
, IX
, возможно также GAP
, AUTO-INC
)Предположим есть таблица, с primary key
id
, строки в таблице имеют следующие id
: [1-9, 11, 13-19, 21-30], то есть нет id
10, 12 и 20
Сделаем 4 потока:
Первый блокирует диапазон индексов и засыпает на 30 секунд:
sqlSTART TRANSACTION; SELECT * FROM `t1` WHERE id>10 AND id<20 FOR UPDATE; SELECT SLEEP(30); COMMIT;
Второй вставляет новую строку в заблокированный диапазон:
sqlINSERT INTO `t1`(`id`, `name`) VALUES (12,0);
Третий вставляет запись в незаблокированную область (меньшая сторона):
sqlINSERT INTO `t1`(`id`, `name`) VALUES (10,0);
Четвертый вставляет запись в незаблокированную область (бОльшая сторона):
sqlINSERT 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 типа намеренных блокировок:
IS
) - намеренная совместная блокировкаIX
) намеренная эксклюзивная блокировкаperformance_schema.data_locks
(например в случае селективной блокировки) в первой строке можно увидеть что:
TABLE
IX
или IS
AUTO-INC lock - специальная блокировка на уровне таблицы, где есть столбцы с AUTO_INCREMENT
. Такая блокировка происходит при INSERT
запросе: транзакции становятся в очередь чтобы получить последовательные значения автоинкрементируемого столбца.
LOCK TABLES - явная блокировка таблиц для текущей сессии, неявно освобождает любые текущие блокировки в текущей сессии.
Таблица, созданная в текущей сессии может быть заблокирована независимо от наличия блокировки со стороны других сессий.
2 вида блокировки:
LOCAL
позволяет выполнять не конфликтующие INSERT
(без LOCK TABLE
). Для innodb
READ == READ LOCAL
В текущем сеансе при включенном LOCK TABLES
доступ можно получить только к заблокированным таблицам, при обращении к незаблокированным таблицам будет ошибка.
Нельзя ссылаться на одну и ту же таблицу несколько раз, надо заблокировать таблицу с помощью псевдонима, например:
sqlLOCK TABLE t WRITE, t as t1 READ; INSERT INTO t SELECT * FROM t as t1;
Если запрос выполняет транзакцию, либо соединение сессии разрывается (как обычно или ненормально) то выполняется неявный UNLOCK TABLES
Если была активная (незавершенная) транзакция, то при завершении сеанса она откатится.
Использование транзакций и LOCK/UNLOCK TABLE
:
sqlSET autocommit=0; LOCK TABLES ...; ... COMMIT; UNLOCK TABLES;