Статьи

Подробнее о (транзакционных) блокировках метаданных MySQL

Первоначально Автор  Валерий Кравчук

Два года назад Ovais Тарика было объяснено подробно , что существовали виды проблем , прежде чем MySQL введены метаданные блокировок в 5.5.3 и как эти замки помогают предотвратить их. Тем не менее, некоторые последствия блокировки метаданных в MySQL остаются неясными для пользователей — администраторов баз данных и даже разработчиков программного обеспечения, ориентированных на последние версии MySQL. Я решил включить слайд или два в презентацию о блокировках и тупиках InnoDB, которые я планирую сделать (со своим коллегой Нилнанданом Джоши ) 16 апреля на Percona Live 2015.

Я решил сделать это, так как недавно у меня возникла проблема для работы, в которой утверждалось, что поведение SELECT, блокирующего TRUNCATE TABLE, является неправильным, просто потому, что уровень изоляции транзакции был установлен на READ COMMITTED, и поэтому не должно быть никаких блокировок, установленных SELECT и транзакция даже не должны запускаться независимо от значения autocommit (оно было явно установлено в 0 интеллектуальным программным обеспечением).

В руководстве по MySQL четко сказано:

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

Таким образом, реальная задача состояла в том, чтобы показать эти блокировки метаданных, все еще установленные в транзакции, которая началась неявно, с помощью SELECT сразу после SET autocommit = 0 в сеансе. Это был хороший шанс проверить, как блокировки метаданных отображаются в MySQL 5.7 через Performance Schema, поэтому я настроил простой тест.

Прежде всего, я включил инструментарий для блокировки метаданных:

Включение мониторинга блокировок метаданных

[openxs@centos 5.7]$ bin/mysql --no-defaults -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.6-m16 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Затем я настроил простой тест, основанный на деталях этой проблемы (я создал таблицу InnoDB, t и добавил к ней строку перед этим):

Запуск SELECT с autocommit = 0 и READ COMMITTED

mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit, @@tx_isolation;
+--------------+----------------+
| @@autocommit | @@tx_isolation |
+--------------+----------------+
| 0 | READ-COMMITTED |
+--------------+----------------+
1 row in set (0.00 sec)
mysql> select * from t limit 1;
+----+------+
| id | val |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)

Теперь, из другого сеанса, я попытался TRUNCATE таблицы, прежде чем первый сеанс получил возможность сделать явный или неявный COMMIT (В проблеме, которую я упомянул, используемое программное обеспечение просто не заботилось об этом, предполагая, что транзакция не началась. Она работала с MySQL 5.1 действительно хорошо так.)

MySQL

mysql> truncate table t;

Меня не удивило, что TRUNCATE завис. В руководстве четко сказано, что до совершения транзакции мы не снимаем блокировки метаданных. Но давайте проверим их в Performance Schema (с первого сеанса, где мы выполнили SELECT ):

Включены блокировки метаданных

MySQL

mysql> select * from performance_schema.metadata_locksG
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: t
OBJECT_INSTANCE_BEGIN: 140450128308592
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5585
OWNER_THREAD_ID: 27
OWNER_EVENT_ID: 17
*************************** 2. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140450195436144
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5224
OWNER_THREAD_ID: 30
OWNER_EVENT_ID: 8
*************************** 3. row ***************************
OBJECT_TYPE: SCHEMA
OBJECT_SCHEMA: test
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140450195434272
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5209
OWNER_THREAD_ID: 30
OWNER_EVENT_ID: 8
*************************** 4. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: t
OBJECT_INSTANCE_BEGIN: 140450195434368
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:5585
OWNER_THREAD_ID: 30
OWNER_EVENT_ID: 8
*************************** 5. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: performance_schema
OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 140450128262384
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5585
OWNER_THREAD_ID: 27
OWNER_EVENT_ID: 18
5 rows in set (0.00 sec)

Примечание. Блокировка SHARED_READ установлена ​​для таблицы t, а блокировка EXCLUSIVE находится на рассмотрении в той же таблице t выше. TRUNCATE заблокирован (как DDL).

Обратите внимание также на блокировки, связанные с out SELECT из таблицы metadata_locks в выходных данных. Да, доступ к Performance Schema также защищен блокировками метаданных!

Мы можем получить хорошее представление обо всех блокировках метаданных из других сеансов, за исключением нашего текущего, и проверить также все, что мы могли получить о них до MySQL 5.7 (просто состояние потока в выводе SHOW PROCESSLIST ):

Метаданные блокируют информацию в P_S и PROCESSLIST

mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID();
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t |
| SCHEMA | test | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t |
| TABLE | test | t | EXCLUSIVE | PENDING | 30 | 8 | truncate table t |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+
3 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+------------------+
| 5 | root | localhost | test | Query | 0 | starting | show processlist |
| 8 | root | localhost | test | Query | 50 | Waiting for table metadata lock | truncate table t |
+----+------+-----------+------+---------+------+---------------------------------+------------------+
2 rows in set (0.00 sec)

Как только я завершу транзакцию, в которой был выполнен SELECT ,

TRUNCATE завершается, и мы не видим ожидающих блокировки метаданных:

Нет строк и метаданных, ожидающих блокировки

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID();
Empty set (0.01 sec)
mysql> select * from t;
Empty set (0.00 sec)

Подводя итог, MySQL 5.7 позволяет вам детально изучить все блокировки метаданных. Они установлены как для транзакционных, так и для нетранзакционных таблиц, но помните, что если вы используете autocommit = 0 или запускаете транзакцию явно, они освобождаются только тогда, когда происходит фиксация, неявная или явная. Если вы хотите, чтобы отдельный оператор SELECT не блокировал какой-либо DDL после его завершения, убедитесь, что он немедленно завершен COMMIT или используется autocommit = 1 .

Мы можем с уверенностью назвать поведение блокировок метаданных для этого случая «ошибкой» или подать «запрос функции», чтобы изменить его, но сейчас любое программное обеспечение, которое должно работать с MySQL 5.5.3+, должно просто принимать все значения метаданных. замки во внимание.