MySQL死锁查询

  sre

1、查询是否锁表

show OPEN TABLES where In_use > 0;
show open tables WHERE Table LIKE '%tb_employees%' AND In_use > 0

查询到相对应的进程 === 然后 kill id

2、查询进程

show processlist

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
show status like '%lock%';
show variables like '%timeout%';
show engine innodb status\G;
/usr/local/mysql/bin/mysqladmin  -h127.0.0.1 -P3306 -uroot -p123456 "processlist" |grep Query |awk -F "|" '{print $9}'
SELECT    
        p2.`HOST` 被阻塞方host,
        p2.`USER` 被阻塞方用户,
        r.trx_id 被阻塞方事务id,    
        r.trx_mysql_thread_id 被阻塞方线程号,    
        TIMESTAMPDIFF(    
            SECOND,    
            r.trx_wait_started,    
            CURRENT_TIMESTAMP    
        ) 等待时间,    
        r.trx_query 被阻塞的查询,    
        l.lock_table 阻塞方锁住的表,  
        m.`lock_mode` 被阻塞方的锁模式,
        m.`lock_type`  "被阻塞方的锁类型(表锁还是行锁)",
        m.`lock_index` 被阻塞方锁住的索引,
        m.`lock_space` 被阻塞方锁对象的space_id,
        m.lock_page 被阻塞方事务锁定页的数量,
        m.lock_rec 被阻塞方事务锁定行的数量,
        m.lock_data  被阻塞方事务锁定记录的主键值,  
        p.`HOST` 阻塞方主机,
        p.`USER` 阻塞方用户,
        b.trx_id 阻塞方事务id,    
        b.trx_mysql_thread_id 阻塞方线程号,
        b.trx_query 阻塞方查询,
        l.`lock_mode` 阻塞方的锁模式,
        l.`lock_type` "阻塞方的锁类型(表锁还是行锁)",
        l.`lock_index` 阻塞方锁住的索引,
        l.`lock_space` 阻塞方锁对象的space_id,
        l.lock_page 阻塞方事务锁定页的数量,
        l.lock_rec 阻塞方事务锁定行的数量,
        l.lock_data 阻塞方事务锁定记录的主键值,         
      IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) 阻塞方事务空闲的时间           
    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    
    INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id`
      INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
    INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id   
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
    ORDER BY    
        等待时间 DESC;

LEAVE A COMMENT

Captcha Code