有事务阻塞住了,怎么了解来龙去脉合理解决

今天遇到一个测试环境的问题,开发者说表锁住了怎么弄。
于是我做了如下查询
mysql> select *from information_schema.processlist where info<>''\G
*************************** 1. row ***************************
ID: 2641
USER: root
HOST: ……
DB: gxyj_hreo
COMMAND: Query
TIME: 668
STATE: Waiting for table metadata lock
INFO: drop table `gxyj_hreo`.`center_cat_attr`
*************************** 2. row ***************************
ID: 2662
USER: root
HOST: localhost
DB: NULL
COMMAND: Query
TIME: 0
STATE: executing
INFO: select *from information_schema.processlist where info<>''
2 rows in set (0.00 sec)
从processlist看出2641进程被阻塞,想删除表,在等待元数据所。
 
 
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 149692115
trx_state: RUNNING
trx_started: 2018-08-09 13:49:28
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2465
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 422185962079184
trx_state: RUNNING
trx_started: 2018-08-09 13:51:36
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 2397
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql>
从innodb事务信息中看到MySQL进程2397,2465 一样运行了一个多小时,目前  trx_query: NULL  没有操作
 
    ID  USER    HOST                DB         COMMAND    TIME  STATE                            INFO                                                        
------ ------ ------------------ --------- ------- ------ ------------------------------- ------------------------------------------------------------
……
2397 root …… gxyj_hreo Sleep 4774 (NULL)
2465 root …… gxyj_hreo Sleep 5559 (NULL)
2641 root 10.1.40.5:59138 gxyj_hreo Query 1046 Waiting for table metadata lock drop table `gxyj_hreo`.`center_cat_attr`
(NULL)
从processlist 里找到2397,2465 两个进程以及sleep,因此把这两个事务解释,等到消除。
kill 2397;
kill 2465;
 
 
其实每次遇到开发和测试说表锁了,库锁了,心里都没什么底,不过刚听了事务那节课,我觉得这次查看还是有依据,应该是靠谱的吧?
 
 
已邀请:

要回复问题请先登录注册