mysql事务和锁和进程查询命令

show open TABLEs

show status like ‘Table_locks%’ ;

查看锁

show status like ‘table%’;

show status like ‘innodb_row_lock%’;

show processlist

show variables like ‘%innodb_lock_wait_timeout%’;

show status like ‘%connect%’;

//查看当前连接数

show status like ‘Threads%’;

show status like ‘%lock%’

show status like ‘InnoDB_row_lock%’;

//事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

//查看 mysql 事务是否开启

SHOW VARIABLES LIKE ‘event_scheduler’;

//进程

SELECT * FROM information_schema.processlist;

//查看锁定的表

show OPEN TABLES where In_use > 0;

show engine innodb status;

//是否开启锁检测

show VARIABLES like ‘%detect%’

//查看事务级别

show variables like ‘transaction_isolation’;

show variables like ‘general_log’; --查看日志是否开启

set global general_log=ON; --开启日志

set global general_log=OFF; --关闭日志

// 查看最大连接数

show variables like ‘%max_connection%’;

开启general_log日记是比较消耗性能的，不需要的时候要关闭它

// 批量杀死事务
select concat(‘KILL ‘,id,’;’) from information_schema.processlist p inner
join information_schema.INNODB_TRX x on p.id=x.trx_mysql_thread_id where db=‘tuanjian’;

MySQL决定如何查找表中的行。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all。

KILL 710;
KILL 712;
KILL 714;