mysql锁操作&查询表、字段语句

查询事务

-- 查看当前事务列表
select * from  information_schema.innodb_trx;

-- 查看当前事务状态(当前事务执行语句)
show engine innodb status;

-- -------------------------出现死锁解决死锁的语句 ------------
-- 查看当前事务状态
show engine innodb status;

-- 得到输出结果后，执行输出结果
SELECT CONCAT("kill ",trx_mysql_thread_id,";") FROM  information_schema.innodb_trx;
Kill <thread_id>

查询锁

-- 查看当前锁的状态
select * from information_schema.innodb_locks;

-- 查询当前锁状态(是否锁表）
show open tables where in_use>0;

-- 查看当前锁的等待状态
select * from information_schema.INNODB_LOCK_WAITS;

-- 查询进程
show full processlist;

-- 查看锁的类型和状态
show status like '%lock%';
show status like 'innodb_row_lock%'

-- 查看 innodb 锁的参数
show variables like 'innodb%lock%';

查询库表、字段

-- 查询库中的表名称、表的描述信息
SELECT TABLE_NAME,table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dulles_glass_dev' order by TABLE_NAME;

-- 查询某一张表的所有字段信息
SELECT COLUMN_NAME, column_type,column_comment,column_key
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dulles_glass_dev' 
AND TABLE_NAME = 'dulles_custom_category' order by ordinal_position;