mysql关闭数据库命令

今天主要分享下关于mysql事务的相关sql,抽空做一下总结，整理如下：

查询正在执行的事务(kill事务的线程ID（trx_mysql_thread_id）)ｓｅｌｅｃｔ * FROM information_schema.INNODB_TRX;

查看正在锁的事务

ｓｅｌｅｃｔ * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务ｓｅｌｅｃｔ * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;查看事务等待状况ｓｅｌｅｃｔ r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

查看更具体的事务等待状况ｓｅｌｅｃｔ b.trx_state, e.state, e.time, d.state as block_state, d.time as block_time, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id, a.blocking_lock_id, c.trx_query as block_trx_query, c.trx_mysql_thread_id as block_trx_mysql_tread_idfrom information_schema.innodb_lock_waits aleft join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_idleft join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_idleft join information_schema.processlist d on c.trx_mysql_thread_id = d.idleft join information_schema.processlist e on b.trx_mysql_thread_id = e.idorder by a.requesting_trx_id;

查看未关闭的事务ｓｅｌｅｃｔ a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.id, b. user, b. host, b.db, b.command, b.time, b.state, b.info from information_schema.innodb_trx a left join information_schema.processlist b on a.trx_mysql_thread_id = b.id where b.command = ‘sleep‘; 未关闭事务信息selectt1.trx_id,t1.trx_started,t1.trx_mysql_thread_id,t3.event_id,t3.end_event_id,t3.sql_text,ｃｏｎｃａｔ(‘mysql --login-path=3306 -e ‘‘kill ‘,t1.trx_mysql_thread_id,‘‘‘‘)from information_schema.innodb_trx t1 left join `performance_schema`.threads t2on t1.trx_mysql_thread_id=t2.processlist_idleft join `performance_schema`.events_statements_history t3on t2.thread_id=t3.thread_idwhere t1.trx_started < date_sub(now(), interval 1 minute)and t1.trx_operation_state is nulland t1.trx_query is nullorder by event_id desc;查看某段时间以来未关闭事务ｓｅｌｅｃｔ trx_id, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_started < date_sub( now( ), INTERVAL 1 MINUTE ) AND trx_operation_state IS NULL AND trx_query IS NULL;

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~