Mysql 死結問題解決思路（一）【備忘】

如果出現死結，可以用SHOW INNODB STATUS命令來確定最後一個死結產生的原因。返回結果中包括死結相關事務的詳細資料，如引發死結的SQL語句，事務已經獲得的鎖，正在等待什麼鎖，以及被復原的事務等。據此可以分析死結產生的原因和改進措施。下面是一段SHOW INNODB STATUS輸出的範例：

mysql> show innodb status /G ……. ------------------------ LATEST DETECTED DEADLOCK ------------------------ 070710 14:05:16 *** (1) TRANSACTION: TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS thread id 1197328736 inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1216 MySQL thread id 7521657, query id 673468054 localhost root update insert into country (country_id,country) values(110,'Test') ……… *** (2) TRANSACTION: TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS thread id 1164048736 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1216, undo log entries 1 MySQL thread id 7521664, query id 673468058 localhost root statistics select first_name,last_name from actor where actor_id = 1 for update *** (2) HOLDS THE LOCK(S): ……… *** (2) WAITING FOR THIS LOCK TO BE GRANTED: ……… *** WE ROLL BACK TRANSACTION (1) ……