MySQL锁表及死锁解决办法

MySQL在遇到锁表及死锁的时候可以通过以下命令进行查找问题原因和相关解决办法：

• 查看锁表情况及等待锁SQL语句

SELECT    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 \G;

• MySQL死锁解决办法

死锁分析：

查看锁表线程

show processlist；

查看当前已经打开的表

show status like '%opened_tables%';

查看当前具体锁表情况

show open tables where in_use>0;

innodb存储引擎锁表查找方法

检查引擎状态 ,可以看到哪些语句产生死锁：

show engine innodb status\G;

查看information_schema架构下的innodb_locks、innodb_trx、innodb_lock_waits等表

#查看innodb锁表情况
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
select * from information_schema.innodb_lock_waits;