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

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

SELECT ... LOCK IN SHARE MODE устанавливает общие блокировки следующих ключей на всех записях индекса, которые встречает поиск. Однако для операторов, блокирующих строки с использованием уникального индекса для поиска уникальной строки, требуется только блокировка записи индекса.

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

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

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_locks_unsafe_for_binlog включен и уровень изоляции транзакции не равен SERIALIZABLE, 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. Для получения дополнительной информации см. Раздел 14.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 не знает о блокировках строк, возможно получить блокировку таблицы на таблице, на которой в настоящее время другой сеанс имеет блокировки строк. Однако это не ставит под угрозу целостность транзакции, как обсуждается в Разделе 14.7.5.2, «Обнаружение тупиков».

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

В MySQL 5.7, 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.