本文主要是介绍Mysql数据库管理-阻塞lock问题分析处理 session和schema_table_locks_waits，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
1 schema_table_lock_waits
1 首先启用mdl锁等待事件相关的instruments
15:31: [sys]> select name from performance_schema.setup_instruments limit 10;
+---------------------------------------------------------+
| name |
+---------------------------------------------------------+
| wait/synch/mutex/pfs/LOCK_pfs_share_list |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync |
+---------------------------------------------------------+
10 rows in set (0.00 sec)
15:32: [sys]>
call sys.ps_setup_enable_instrument('wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit');
+-----------------------+
| summary |
+-----------------------+
| Enabled 2 instruments |
+-----------------------+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.02 sec)
mysql>call sys.ps_setup_enable_instruments('wait/lock/metadata/sql/mdl');
此视图可以查询到blocking pid信息，并给出解决方案
15:23: [sys]> select * from schema_table_lock_waits \G
*************************** 1. row ***************************
object_schema: ytt
object_name: t
waiting_thread_id: 50
waiting_pid: 10
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: drop table t
waiting_query_secs: 654
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 49
blocking_pid: 9
blocking_account: root@localhost
blocking_lock_type: SHARED_READ
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.00 sec)
15:23: [sys]> kill 9;
Query OK, 0 rows affected (0.00 sec)
15:24: [sys]> select * from schema_table_lock_waits \G
Empty set (0.00 sec)
15:24: [sys]> select * from schema_table_lock_waits \G
Empty set (0.00 sec)
15:24: [sys]>
这篇关于Mysql数据库管理-阻塞lock问题分析处理 session和schema_table_locks_waits的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
