- Author: duqi@kongge.com
- Date: 2016.04.30
数据库死锁的问题，还是挺让人讨厌的。
这里提供两个解决数据库死锁的方法：
- 1）重启数据库（谁用谁知道）
- 2）杀掉抢资源的进程：
- 先查哪些进程在抢资源：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; - 杀掉它们：
Kill trx_mysql_thread_id；
- 先查哪些进程在抢资源：
-
- 其他操作：
- 查看正在锁的事务:
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
- 查看等待锁的事务:
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
1：查看当前的事务
mysql> SELECT * FROM information_schema.innodb_trx \G
2：查看当前锁定的事务
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：查看当前等锁的事务
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
下面的查询显示了谁阻塞和谁在等待，以及等待了多久：
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, TIMESTAMPDIFF( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ) wait_time, r.trx_query waiting_query, l.lock_table waiting_table_lock, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, SUBSTRING( p. HOST, 1, INSTR(p. HOST, ':') - 1 ) blocking_host, SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) blocking_port, IF (p.COMMAND = 'Sleep', p.TIME, 0) idel_in_trx, 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 INNER JOIN information_schema.INNODB_LOCKS l ON w.requested_lock_id = l.lock_id LEFT JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id ORDER BY wait_time DESC
下面查询显示了多少查询被哪些线程阻塞：
SELECT CONCAT('thread ' , b.trx_mysql_thread_id , ' from ' , p.host) as who_blocks, IF(p.command = "Sleep", p.time, 0 ) as idle_in_trx, MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW() )) as max_wait_time, COUNT(*) as num_waiters FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON b.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id GROUP BY who_blocks ORDER BY num_waiters DESC
