Если для данной блокировки имеется несколько ожидающих потоков, запрос с более высоким приоритетом удовлетворяется в первую очередь, за исключением случаев, связанных с системной переменной 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.