SELECT ... FROM является согласованным чтением, читая моментальный снимок базы данных и не устанавливая никаких блокировок, если уровень изоляции транзакции не установлен на SERIALIZABLE. Для уровня SERIALIZABLE поиск устанавливает общие блокировки смежных ключей на записях индекса, которые он встречает. Однако для операторов, которые блокируют строки с помощью уникального индекса для поиска уникальной строки, требуется только блокировка записи индекса.

SELECT ... FOR UPDATE и SELECT ... FOR SHARE операторы, использующие уникальный индекс, приобретают блокировки для просматриваемых строк и освобождают блокировки для строк, которые не подходят для включения в результирующий набор (например, если они не соответствуют критериям, заданным в разделе WHERE). Однако в некоторых случаях строки могут не быть разблокированы сразу, потому что связь между строкой результата и ее исходным источником теряется во время выполнения запроса. Например, в UNION просматриваемые (и заблокированные) строки из таблицы могут быть вставлены во временную таблицу перед оценкой их соответствия результатам. В этой ситуации связь строк во временной таблице со строками в исходной таблице теряется, и последние строки не разблокируются до конца выполнения запроса.

Для (SELECT с FOR UPDATE или FOR SHARE), UPDATE и DELETE операторов блокировки зависят от того, использует ли оператор уникальный индекс с уникальным условием поиска или условие поиска диапазона.

Для записей индекса, которые встречает поиск, SELECT ... FOR UPDATE блокирует другие сеансы от выполнения SELECT ... FOR SHARE или чтения в определенных уровнях изоляции транзакций. Согласованные чтения игнорируют любые блокировки, установленные на записях, существующих в представлении чтения.

UPDATE ... WHERE ... устанавливает эксклюзивную блокировку смежных ключей на каждой встреченной записи. Однако для операторов, которые блокируют строки с помощью уникального индекса для поиска уникальной строки, требуется только блокировка записи индекса.

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

DELETE FROM ... WHERE ... устанавливает эксклюзивную блокировку смежных ключей на каждой встреченной записи. Однако для операторов, которые блокируют строки с помощью уникального индекса для поиска уникальной строки, требуется только блокировка записи индекса.

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

Перед вставкой строки устанавливается тип блокировки пробела, называемый блокировкой намерения вставки. Эта блокировка сигнализирует о намерении вставки таким образом, что нескольким транзакциям, вставляющим в тот же пробел индекса, не нужно ждать друг друга, если они не вставляют в одно и то же место в пробеле. Предположим, что существуют записи индекса со значениями 4 и 7. Отдельные транзакции, которые пытаются вставить значения 5 и 6, каждый раз блокируют пробел между 4 и 7 с блокировками намерения вставки перед получением эксклюзивной блокировки на вставленной строке, но не блокируют друг друга, потому что строки не конфликтуют.

Если возникает ошибка дублирования ключа, устанавливается общая блокировка на записи дублирующего индекса. Это использование общей блокировки может привести к тупику, если несколько сеансов пытаются вставить одну и ту же строку, если другой сеанс уже имеет эксклюзивную блокировку. Это может произойти, если другой сеанс удаляет строку. Предположим, что таблица InnoDB t1 имеет следующую структуру:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

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

Сеанс 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Сеанс 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Сеанс 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Сеанс 1:

ROLLBACK;

Первая операция сеанса 1 приобретает эксклюзивную блокировку для строки. Операции сеансов 2 и 3 оба приводят к ошибке дублирования ключа, и оба запрашивают общую блокировку для строки. Когда сеанс 1 откатывается, он освобождает свою эксклюзивную блокировку для строки, и запросы на общую блокировку для сеансов 2 и 3 выполняются. В этот момент сеансы 2 и 3 тупикуют: ни один из них не может получить эксклюзивную блокировку для строки из-за общей блокировки, удерживаемой другим.

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

Сеанс 1:

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

Сеанс 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Сеанс 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Сеанс 1:

COMMIT;

Первая операция сеанса 1 приобретает эксклюзивную блокировку для строки. Операции сеансов 2 и 3 оба приводят к ошибке дублирования ключа, и оба запрашивают общую блокировку для строки. Когда сеанс 1 подтверждается, он освобождает свою эксклюзивную блокировку для строки, и запросы на общую блокировку для сеансов 2 и 3 выполняются. В этот момент сеансы 2 и 3 тупикуют: ни один из них не может получить эксклюзивную блокировку для строки из-за общей блокировки, удерживаемой другим.

INSERT ... ON DUPLICATE KEY UPDATE отличается от простого INSERT тем, что при возникновении ошибки дублирования ключа на строку, подлежащую обновлению, устанавливается эксклюзивная блокировка, а не общая. Эксклюзивная блокировка записи индекса берется для дублирующего значения первичного ключа. Эксклюзивная блокировка смежных ключей берется для дублирующего значения уникального ключа.

REPLACE выполняется как INSERT, если не происходит коллизии по уникальному ключу. В противном случае на строку, подлежащую замене, устанавливается эксклюзивная блокировка смежных ключей.

INSERT INTO T SELECT ... FROM S WHERE ... устанавливает эксклюзивную блокировку записи индекса (без блокировки пробела) на каждой вставленной строке в T. Если уровень изоляции транзакции равен READ COMMITTED, InnoDB выполняет поиск в S как согласованное чтение (без блокировок). В противном случае InnoDB устанавливает общие блокировки смежных ключей на строки из S. InnoDB должен устанавливать блокировки в последнем случае: во время восстановления с использованием двоичного журнала на основе операторов каждый оператор SQL должен выполняться точно так же, как он был выполнен изначально.

CREATE TABLE ... SELECT ... выполняет SELECT с общими блокировками смежных ключей или как согласованное чтение, как и для INSERT ... SELECT.

При использовании SELECT в конструкциях REPLACE INTO t SELECT ... FROM s WHERE ... или UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB устанавливает общие блокировки смежных ключей на строки из таблицы s.

InnoDB устанавливает эксклюзивную блокировку конца индекса, связанного со столбцом AUTO_INCREMENT, при инициализации предварительно указанного столбца AUTO_INCREMENT в таблице.

С помощью innodb_autoinc_lock_mode=0, InnoDB использует специальный режим блокировки таблицы AUTO-INC, где блокировка приобретается и удерживается до конца текущего оператора SQL (а не до конца всей транзакции) во время доступа к счетчику автоинкремента. Другие клиенты не могут вставлять в таблицу, пока удерживается блокировка таблицы AUTO-INC. То же самое поведение происходит для “оптовых вставок” с innodb_autoinc_lock_mode=1. Блокировки табличного уровня AUTO-INC не используются с innodb_autoinc_lock_mode=2. Более подробную информацию см. в разделе 17.6.1.6, «Обработка AUTO_INCREMENT в InnoDB».

InnoDB извлекает значение предварительно инициализированного столбца AUTO_INCREMENT без установки каких-либо блокировок.

Если ограничение FOREIGN KEY определено в таблице, любая вставка, обновление или удаление, которые требуют проверки условия ограничения, устанавливает общие блокировки записей на уровне строки на записях, на которые оно смотрит, чтобы проверить ограничение. InnoDB также устанавливает эти блокировки в случае, если ограничение не выполняется.

LOCK TABLES устанавливает блокировки таблиц, но именно верхний слой MySQL над слоем InnoDB устанавливает эти блокировки. InnoDB учитывает блокировки таблиц, если innodb_table_locks = 1 (по умолчанию) и autocommit = 0, а слой MySQL над InnoDB знает о блокировках на уровне строк.

В противном случае, автоматическое обнаружение тупиковых ситуаций InnoDB не может обнаружить тупиковые ситуации, в которые вовлечены такие блокировки таблиц. Кроме того, поскольку в этом случае верхний слой MySQL не знает о блокировках на уровне строк, возможно получить блокировку таблицы на таблице, на которой в другой сессии в данный момент имеются блокировки на уровне строк. Однако это не ставит под угрозу целостность транзакции, как обсуждается в разделе 17.7.5.2, «Обнаружение тупиковых ситуаций».

LOCK TABLES приобретает две блокировки на каждой таблице, если innodb_table_locks=1 (по умолчанию). В дополнение к блокировке таблицы на уровне MySQL, он также приобретает блокировку таблицы InnoDB. Чтобы избежать приобретения блокировок таблиц InnoDB, установите innodb_table_locks=0. Если блокировка таблицы InnoDB не приобретена, LOCK TABLES выполняется даже если некоторые записи таблиц блокируются другими транзакциями.

В MySQL 8.4, innodb_table_locks=0 не оказывает влияния на таблицы, явно заблокированные с помощью LOCK TABLES ... WRITE. Оно оказывает влияние на таблицы, заблокированные для чтения или записи с помощью LOCK TABLES ... WRITE неявно (например, через триггеры) или с помощью LOCK TABLES ... READ.

Все блокировки InnoDB, удерживаемые транзакцией, освобождаются при подтверждении или прерывании транзакции. Таким образом, нет большого смысла вызывать LOCK TABLES на InnoDB таблицах в режиме autocommit=1, поскольку приобретенные блокировки таблиц InnoDB будут освобождены немедленно.

Нельзя заблокировать дополнительные таблицы в середине транзакции, так как LOCK TABLES выполняет неявное COMMIT и UNLOCK TABLES.