Если для данной блокировки существует несколько ожидающих, запрос с более высоким приоритетом удовлетворяется первым, за исключением исключений, связанных с системной переменной max_write_lock_count. Запросы на запись имеют более высокий приоритет, чем запросы на чтение. Однако, если max_write_lock_count установлено в низкое значение (например, 10), запросы на чтение могут быть предпочтительнее ожидающих запросов на запись, если запросы на чтение уже были пропущены в пользу 10 запросов на запись. Обычно такое поведение не наблюдается, потому что max_write_lock_count по умолчанию имеет очень большое значение.

Утверждения получают блокировки метаданных по одной, а не одновременно, и выполняют в процессе обнаружение тупиков.

Операторы DML обычно получают блокировки в порядке упоминания таблиц в операторе.

Операторы DDL, LOCK TABLES, и аналогичные операторы пытаются сократить количество возможных тупиков между одновременными операторами DDL, получая блокировки на явно указанных таблицах в алфавитном порядке. Блокировки могут быть получены в другом порядке для неявно используемых таблиц (таких как таблицы в отношениях внешних ключей, которые также должны быть заблокированы).

Например, RENAME TABLE — это оператор DDL, который получает блокировки в алфавитном порядке:

Оба оператора получают блокировки на tbla и tblc в указанном порядке, но различаются тем, приобретается ли блокировка на оставшемся имени таблицы до или после tblc.

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

Начните с двух таблиц x и x_new с идентичной структурой. Три клиента выполняют операторы, которые включают эти таблицы:

Клиент 1:

LOCK TABLE x WRITE, x_new WRITE;

Оператор запрашивает и получает блокировки на запись в алфавитном порядке на x и x_new.

Клиент 2:

INSERT INTO x VALUES(1);

Оператор запрашивает и ожидает блокировку на запись на x.

Клиент 3:

RENAME TABLE x TO x_old, x_new TO x;

Оператор запрашивает эксклюзивные блокировки в алфавитном порядке на x, x_new и x_old, но ожидает блокировку на x.

Клиент 1:

UNLOCK TABLES;

Оператор освобождает блокировки на запись на x и x_new. Запрос на эксклюзивную блокировку для x клиентом 3 имеет более высокий приоритет, чем запрос на блокировку на запись клиентом 2, поэтому клиент 3 получает свою блокировку на x, затем также на x_new и x_old, выполняет переименование и освобождает свои блокировки. Затем клиент 2 получает свою блокировку на x, выполняет вставку и освобождает свою блокировку.

Порядок получения блокировок приводит к выполнению RENAME TABLE перед INSERT. Таблица x, в которую происходит вставка, является таблицей, которая именовалась x_new, когда клиент 2 выполнил вставку, и была переименована в x клиентом 3:

mysql> SELECT * FROM x;
+------+
| i    |
+------+
|    1 |
+------+

mysql> SELECT * FROM x_old;
Empty set (0.01 sec)

Теперь начните вместо этого с таблицами, названными x и new_x, которые имеют идентичную структуру. Опять же, три клиента выполняют операторы, которые включают эти таблицы:

Клиент 1:

LOCK TABLE x WRITE, new_x WRITE;

Оператор запрашивает и получает блокировки на запись в алфавитном порядке на new_x и x.

Клиент 2:

INSERT INTO x VALUES(1);

Оператор запрашивает и ожидает блокировку на запись на x.

Клиент 3:

RENAME TABLE x TO old_x, new_x TO x;

Оператор запрашивает эксклюзивные блокировки в алфавитном порядке на new_x, old_x и x, но ожидает блокировку на new_x.

Клиент 1:

UNLOCK TABLES;

Оператор освобождает блокировки на запись на x и new_x. Для x единственный ожидающий запрос — от клиента 2, поэтому клиент 2 получает свою блокировку, выполняет вставку и освобождает блокировку. Для new_x единственный ожидающий запрос — от клиента 3, которому разрешено получить эту блокировку (а также блокировку на old_x). Операция переименования всё ещё блокируется для блокировки на x, пока вставка клиента 2 не завершится и не освободит блокировку. Затем клиент 3 получает блокировку на x, выполняет переименование и освобождает свою блокировку.

В этом случае порядок получения блокировок приводит к выполнению INSERT перед RENAME TABLE. Таблица x, в которую происходит вставка, является исходной x, теперь переименованной в old_x операцией переименования:

mysql> SELECT * FROM x;
Empty set (0.01 sec)

mysql> SELECT * FROM old_x;
+------+
| i    |
+------+
|    1 |
+------+

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

Блокировки метаданных расширяются по мере необходимости для таблиц, связанных ограничением внешнего ключа, чтобы предотвратить одновременное выполнение конфликтующих операций DML и DDL на связанных таблицах. При обновлении родительской таблицы блокировка метаданных устанавливается на дочерней таблице во время обновления метаданных внешнего ключа. Метаданные внешнего ключа принадлежат дочерней таблице.

Для обеспечения целостности транзакций сервер не должен допускать выполнения одного сеансу оператора языка определения данных (DDL) над таблицей, используемой в незавершенной явно или неявно начатой транзакции в другом сеансе. Сервер достигает этого, приобретая блокировки метаданных на таблицах, используемых в рамках транзакции, и откладывая освобождение этих блокировок до окончания транзакции. Блокировка метаданных на таблице предотвращает изменения структуры таблицы. Этот подход к блокировке подразумевает, что таблица, используемая транзакцией в одном сеансе, не может использоваться операторами DDL другими сеансами до завершения транзакции.

Это правило применяется не только к транзакционным таблицам, но и к не транзакционным. Предположим, что сеанс начинает транзакцию, использующую транзакционную таблицу t и не транзакционную таблицу nt следующим образом:

START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;

Сервер удерживает блокировки метаданных на обеих t и nt до завершения транзакции. Если другой сеанс пытается выполнить операцию блокировки DDL или записи на любой из таблиц, он блокируется до освобождения блокировки метаданных в конце транзакции. Например, второй сеанс блокируется, если он пытается выполнить любую из этих операций:

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;

То же самое поведение применяется для оператора LOCK TABLES ... READ. То есть, явно или неявно начатые транзакции, обновляющие любую таблицу (транзакционную или не транзакционную), блокируются и заблокированы оператором LOCK TABLES ... READ для этой таблицы.

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

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

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

Для транзакций XA в состоянии PREPARED блокировки метаданных сохраняются при отключении клиента и перезапуске сервера, пока не будет выполнен оператор XA COMMIT или XA ROLLBACK.