mysql一张表卡死,mysql 表 卡死

最近在测试环境的mysql执行一个alter table 语句时一直处于阻塞状态，通过 show processlist 查看线程一直处于Waiting for table metadata lock，

show processlist

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx

KILL processid

------------SQL server

查找阻塞进程:

SELECT blocking_session_id '阻塞进程的ID', wait_duration_ms '等待时间(毫秒)', session_id '(会话ID)' FROM sys.dm_os_waiting_tasks

杀死阻塞进程:

KILL session_id

查找事务

SELECT es.session_id, es.login_name, es.host_name, est.text

, cn.last_read, cn.last_write, es.program_name

FROM sys.dm_exec_sessions es

INNER JOIN sys.dm_tran_session_transactions st --系统里还存在的事务

ON es.session_id = st.session_id

INNER JOIN sys.dm_exec_connections cn

ON es.session_id = cn.session_id

CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est

LEFT OUTER JOIN sys.dm_exec_requests er

ON st.session_id = er.session_id

AND er.session_id IS NULL