MySQL如何查看元数据锁阻塞在哪里
程序员文章站
2022-03-16 20:08:22
mysql如何查看元数据锁阻塞在哪里
操作步骤:
1、session 1 执行:
start transaction;
select *...
mysql如何查看元数据锁阻塞在哪里
操作步骤:
1、session 1 执行:
start transaction; select *from t1;
2、session 2 在第1步执行完后执行:
drop table t1;
此时session 2的drop语句被阻塞。那么怎么分析查看元数据锁呢?
方法:
1)执行show processlist;,可以看到drop语句在等待元数据锁
mysql> show processlist; +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ | id | user | host | db | command | time | state | info | +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ | 5 | system user | | null | connect | 1050234 | waiting for master to send event | null | | 6 | system user | | null | connect | 983193 | slave has read all relay log; waiting for the slave i/o thread to update it | null | | 8 | root | localhost | yzs | sleep | 93 | | null | | 9 | root | localhost | yzs | query | 3 | waiting for table metadata lock | drop table t1 | | 10 | root | localhost | null | query | 0 | init | show processlist | +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ 5 rows in set (0.00 sec)
2)可以看到当前正在运行的事务的线程是trx_mysql_thread_id:8,那么这个线程在干什么呢?
mysql> select *from information_schema.innodb_trx\g *************************** 1. row *************************** trx_id: 17683 trx_state: running trx_started: 2017-10-18 05:32:46 trx_requested_lock_id: null trx_wait_started: null trx_weight: 0 trx_mysql_thread_id: 8 trx_query: null trx_operation_state: null trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 320 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: repeatable read trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: null trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.03 sec)
3)可以看到这个线程执行的是select语句,如果执行show engine innodb status;可以看到该事务处于sleep状态,也就是说这个事务语句执行完了,但是没有提交。
执行kill 8,将该事务的线程杀掉就可以了。或者检查业务的sql语句,检查下是否有未提交的sql语句。
mysql> select *from performance_schema.events_statements_current\g *************************** 1. row *************************** thread_id: 27 event_id: 15 end_event_id: 15 event_name: statement/sql/select source: mysqld.cc:962 timer_start: 1050544992900922000 timer_end: 1050544993740836000 timer_wait: 839914000 lock_time: 196000000 sql_text: select *from t1 digest: 1aa32397c8ec37230aed78ef16126571 digest_text: select * from `t1` current_schema: yzs object_type: null object_schema: null object_name: null object_instance_begin: null mysql_errno: 0 returned_sqlstate: null message_text: null errors: 0 warnings: 0 rows_affected: 0 rows_sent: 10 rows_examined: 10 created_tmp_disk_tables: 0 created_tmp_tables: 0 select_full_join: 0 select_full_range_join: 0 select_range: 0 select_range_check: 0 select_scan: 1 sort_merge_passes: 0 sort_range: 0 sort_rows: 0 sort_scan: 0 no_index_used: 1 no_good_index_used: 0 nesting_event_id: null nesting_event_type: null
如有疑问请留言或者到本站社区交流讨论,感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!