第一種
#1.查詢是否鎖表
|
MySQL> show OPEN TABLES where In_use > 0; |
#2.查詢進程（如果您有SUPER權限，您可以看到所有線程。否則，您只能看到您自己的線程）
|
mysql> show full processlist; |
#3.殺死進程id（就是上面命令的id列）
|
[linux-command] kill id; |
第二種
#1.查看正在鎖的事務
|
mysql> select * from information_schema.innodb_trx; |
#2.殺死進程id（就是上面命令的trx_mysql_thread_id列）
|
mysql> show processlist; [linux-command] kill 420821 |
#3.查找執行事務的客戶端請求的SQL線程
|
mysql> select * from information_schema.processlist where id=76613; |
#4.通過SQL線程，找到應用程序的IP地址以及端口192.168.10.1:23452
|
[linux-command].NETstat -nlatp |grep 23452 [linux-command] ps -eaf|grep 12059 |
其它關于查看死鎖的命令
#1：查看當前的事務
|
select * from information_schema.innodb_trx; |
#2：查看當前鎖定的事務
|
select * from information_schema.innodb_locks; |
#3：查看當前等鎖的事務
|
select * from information_schema.innodb_lock_waits; |
#4：查看鎖表情況
|
select r.trx_id as waiting_trx_id, r.trx_mysql_thread_id as waiting_thread, r.trx_query as waiting_query, b.trx_id as blocking_trx_id, b.trx_mysql_thread_id as blocking_thread, b.trx_query as blocking_query, (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(b.trx_started)) as block_time 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; |
