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 9.2, 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.