Предисловие:
Когда вы выполняете SQL в MySQL, оператор не выполняется в ожидаемое время.В это время мы обычно входим в базу данных MySQL, чтобы увидеть, есть ли какие-либо проблемы.Одна из команд, которые мы обычно используем, это show processlist. Посмотрите, какие сеансы есть и что они делают. когда ты видишьwaiting for table metadata lockПри обнаружении блокировки метаданных MDL. В этой статье будет представлен процесс создания и устранения неполадок блокировок MDL.
1. Что такое блокировка MDL
Полное название MDL — блокировка метаданных, то есть блокировка метаданных. Основная функция блокировки MDL заключается в поддержании согласованности данных метаданных таблицы.Когда в таблице есть активные транзакции (явные или неявные), метаданные не могут быть записаны. Поэтому, начиная с версии MySQL 5.5, была введена блокировка MDL для защиты информации метаданных таблицы, которая используется для решения или обеспечения согласованности между операциями DDL и операциями DML.
Для введения MDL он в основном решает две проблемы, одна из которых — проблема изоляции транзакций, Например, при повторяющемся уровне изоляции во время двух запросов сеанса A сеанс B изменяет структуру таблицы, а результаты двух запросов будет несовместимым., не может удовлетворить требования повторяемого чтения; другая проблема репликации данных, например, когда сеанс A выполняет несколько операторов обновления, другой сеанс B изменяет структуру таблицы и отправляет ее первой, что приведет к подчиненный для повтора. Если вы сначала повторите изменение, а затем повторите обновление, произойдет ошибка репликации.
Блокировки метаданных — это блокировки уровня сервера, блокировки уровня таблицы и блокировки MDL, которые применяются при каждом выполнении инструкции DML и DDL.Для операций DML требуются блокировки чтения MDL, а для операций DDL требуются блокировки записи MDL (процесс блокировки MDL автоматически контролируется системой и не может быть прямым вмешательством, совместным чтением-чтением, взаимным исключением чтения-записи и взаимным исключением записи-записи), операция применения блокировок MDL сформирует очередь, а приоритет получения блокировок записи в очередь выше, чем у блокировок чтения. Как только произойдет ожидание блокировки записи, будет заблокирована не только текущая операция, но и все последующие операции над таблицей. Как только транзакция применяется для блокировки MDL, блокировка не будет снята до тех пор, пока транзакция не будет выполнена. (Здесь есть особый случай: если транзакция содержит операции DDL, mysql неявно отправит фиксацию до того, как будет выполнен оператор операции DDL, чтобы гарантировать, что операция оператора DDL существует как отдельная транзакция, а также для обеспечения выпуска операции. эксклюзивная блокировка метаданных).
Примечание:Таблицы механизма InnoDB, которые поддерживают транзакции, и таблицы механизма MyISAM, которые не поддерживают транзакции, будут иметь феномен ожидания блокировки метаданных. Как только происходит явление ожидания блокировки метаданных, все последующие доступы к таблице будут заблокированы во время ожидания, что приведет к накоплению подключений и влиянию на бизнес.
2. Смоделируйте и найдите блокировки MDL
Блокировки MDL обычно возникают, когда операция DDL находится в ожидании из-за незафиксированной транзакции, выполняющей операцию DML в таблице. И у MySQL так много сеансов, что я не знаю, какая операция сеанса не выполняется вовремя и влияет на DDL. Обычно, когда мы устраняем такие проблемы, нам часто нужно получить информацию из информацииschema.innodbВыполняемая в данный момент транзакция запрашивается в таблице trx, но когда SQL был выполнен и фиксация отсутствует, SQL в это время не отображается в этой таблице.
В MySQL 5.7 производительностьДобавлены метаданные в библиотеку схемВ таблицу замков специально записывается информация, связанная с MDL. Сначала откройте запись блокировки MDL и выполните следующий SQL, чтобы открыть ее:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';Ниже показан процесс имитации и поиска блокировок MDL:
# 会话1 事务中执行DML操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student_tb (stu_id,stu_name) values (1009,'xin');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time | update_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | from1 | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 2 | 1002 | dfsfd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 3 | 1003 | fdgfg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 4 | 1004 | sdfsdf | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 5 | 1005 | dsfsdg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 6 | 1006 | fgd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 7 | 1007 | fgds | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 8 | 1008 | dgfsa | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 9 | 1009 | xin | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 |
+--------------+--------+----------+---------------------+---------------------+
# 会话2 对该表加字段 执行DDL操作 发现DDL挂起
mysql> alter table student_tb add stu_age int after stu_name;
# 会话3 查询所有会话 发现发生MDL锁
mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| 31 | root | localhost | testdb | Sleep | 125 | | NULL |
| 32 | root | localhost | testdb | Query | 7 | Waiting for table metadata lock | alter table student_tb add stu_age int after stu_name |
| 33 | root | localhost | testdb | Query | 0 | starting | show processlist |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
# 会话3 查看metadata_locks表记录 发现student_tb表有MDL锁冲突
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| TABLE | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSACTION | GRANTED | | 56 | 34 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 57 | 18 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 |
| TABLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 57 | 18 |
| TABLE | testdb | student_tb | 139764477697696 | EXCLUSIVE | TRANSACTION | PENDING | | 57 | 18 |
| TABLE | performance_schema | metadata_locks | 139764544135120 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 20 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
# 会话3 联合其他系统表 查找出会话ID
mysql> select m.*,t.PROCESSLIST_ID from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id=t.thread_id;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| TABLE | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSACTION | GRANTED | | 56 | 34 | 31 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 57 | 18 | 32 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 | 32 |
| TABLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 57 | 18 | 32 |
| TABLE | testdb | student_tb | 139764477697696 | EXCLUSIVE | TRANSACTION | PENDING | | 57 | 18 | 32 |
| TABLE | performance_schema | metadata_locks | 139764544135120 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 22 | 33 |
| TABLE | performance_schema | threads | 139764549217280 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 22 | 33 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
# 结果解读：从上面结果明显可以看出会话31持有student_tb表的SHARED_WRITE锁，
# 需要等待其提交后或手动杀掉该会话方可解除MDL锁。3. Как оптимизировать и избежать блокировок MDL
Как только произойдет блокировка MDL, это окажет большое влияние на бизнес, поскольку все последующие доступы к таблице будут заблокированы, что приведет к отставанию в подключении. Мы должны изо всех сил стараться избегать ежедневных блокировок MDL. Вот несколько советов по оптимизации для справки:
- Включить блокировку MDL записи таблицы metadata_locks.
- Установить блокировку параметровwaitТайм-аут является небольшим значением, так что заблокированный конец активно останавливается.
- Стандартизируйте использование транзакций, своевременно отправляйте транзакции и избегайте использования крупных транзакций.
- Усовершенствованные сигналы мониторинга для своевременного обнаружения блокировок MDL.
- Операции DDL и операции резервного копирования выполняются в часы пиковой нагрузки.
- Используйте меньше инструментов для открытия транзакций для запросов, а графические инструменты должны быть закрыты вовремя.
Суммировать:
Эта статья в основном разделена на три аспекта, чтобы подробно объяснить блокировку MDL.Во-первых, в ней представлены причины и функции блокировки MDL.Затем мы моделируем блокировку MDL и даем методы поиска и решения.И наконец, мы даем некоторые предложения. чтобы избежать блокировки MDL. На самом деле блокировка MDL часто встречается в процессе эксплуатации и обслуживания БД, она не зверь, а только для защиты объектов БД и обеспечения согласованности данных. Я надеюсь, что после прочтения этой статьи у вас будет более четкое представление о блокировках MDL.
Ссылаться на:
- blog.CSDN.net/Wan Premium 123/AR…
- blog.CSDN.net/final KOF198…
- woohoo.brief.com/afraid/99 от 2 инкубационных яиц гусей…