information_schema

mysql查询死锁语句_MYSQL查询数据库死锁和事务的语句

查看进程信息

SHOWFULLPROCESSLIST;

查询事务信息

SELECT*FROMinformation___queryLIKE'%select%';

查询表打开情况

SHOWOPENTABLESWHEREIn_use>0;

查询锁情况列表

SELECT*FROMINFORMATION__LOCKS;

查询锁等待信息,blocking_lock_id是当前事务在等待的事务

SELECT*FROMINFORMATION__LOCK_WAITSw;

查询死锁⽇志

SHOWENGINEINNODBSTATUS;

查询锁等待时间

SHOWSTATUSLIKE'%lock%';

查询源头锁

_idblocking_trx_id,

_mysql_thread_id源头锁thread_id,

SUBSTRING(,1,INSTR(,':')-1)blocking_host,

SUBSTRING(,INSTR(,':')+1)blocking_port,

IF(D='Sleep',,0)idel_in_trx,

_queryblocking_query,

_idwaiting_trx_id,

_mysql_thread_idwaiting_thread,

TIMESTAMPDIFF(SECOND,_wait_started,CURRENT_TIMESTAMP)wait_time,

_querywaiting_query,

_tablewaiting_table_lock

FROMinformation__LOCKSl

LEFTJOINinformation__LOCK_WAITSw

ted_lock_id=_id

LEFTJOINinformation__TRXb

_id=ng_trx_id

LEFTJOINinformation__TRXr

_id=ting_trx_id

LEFTJOINinformation_SLISTp

=_mysql_thread_id

/*JOIN(SELECTblocking_trx_id--查找最源头的trx_id

FROMinformation__LOCK_WAITSilw

WHEREblocking_trx_idNOTIN

(SELECTrequesting_trx_id

FROMinformation__LOCK_WAITS))c

ng_trx_id=_id*/

ORDERBYwait_timeDESC;

批量⽣产删除数据库外键的SQL

SELECTCONCAT('altertable',table_name,'DROPFOREIGNKEY',constraint_name,';')

FROMinformation__constraints

WHEREconstraint_type='FOREIGNKEY'

ANDtable_schema='dawanka';

查询数据库所有约束类型信息

SELECT*FROMinformation__column_usage;

或者

SELECT*FROMinformation__constraints;