操作步骤:
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)
- 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)
执行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