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

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

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