mysql常用sql

mysql常用sql

1.查看未提交的事务

select t.trx_mysql_thread_id from information_schema.innodb_trx t

2.查询是否锁表

show OPEN TABLES where In_use > 0;

3.查看进程

show processlist

4.查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

5.查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

6.查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

7.查询锁和被锁的信息

SELECT r.`trx_id` waiting_trx_id, r.`trx_mysql_thread_id` waiting_thread, r.`trx_query` waiting_query, b.`trx_id` bolcking_trx_id, b.`trx_mysql_thread_id` blocking_thread, b.`trx_query` block_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` ;

8.查看连接数

show variables like '%max_connection%';

show status like 'Threads%';

set global max_connections=1000;

SHOW PROCESSLIST;

show status like 'Threads%';