第一種
#1.查詢是否鎖表
|
MySQL> show OPEN TABLES where In_use > 0; |
#2.查詢進(jìn)程（如果您有SUPER權(quán)限，您可以看到所有線程。否則，您只能看到您自己的線程）
|
mysql> show full processlist; |
#3.殺死進(jìn)程id（就是上面命令的id列）
|
[linux-command] kill id; |
第二種
#1.查看正在鎖的事務(wù)
|
mysql> select * from information_schema.innodb_trx; |
#2.殺死進(jìn)程id（就是上面命令的trx_mysql_thread_id列）
|
mysql> show processlist; [linux-command] kill 420821 |
#3.查找執(zhí)行事務(wù)的客戶端請(qǐng)求的SQL線程
|
mysql> select * from information_schema.processlist where id=76613; |
#4.通過SQL線程，找到應(yīng)用程序的IP地址以及端口192.168.10.1:23452
|
[linux-command].NETstat -nlatp |grep 23452 [linux-command] ps -eaf|grep 12059 |
其它關(guān)于查看死鎖的命令
#1：查看當(dāng)前的事務(wù)
|
select * from information_schema.innodb_trx; |
#2：查看當(dāng)前鎖定的事務(wù)
|
select * from information_schema.innodb_locks; |
#3：查看當(dāng)前等鎖的事務(wù)
|
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; |
