熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

MYSQL死鎖相關查找

2013-11-23 21:03:14  來源: MySQL 

  如果遇到死鎖了怎麼解決呢?找到原始的鎖ID然後KILL掉一直持有的那個線程就可以了 但是眾多線程可怎麼找到引起死鎖的線程ID呢? MySQL 發展到現在已經非常強大了這個問題很好解決 直接從數據字典連查找

  我們來演示下

  線程A我們用來鎖定某些記錄假設這個線程一直沒提交或者忘掉提交了 那麼就一直存在但是數據裡面顯示的只是SLEEP狀態

  mysql> set @@autocommit=;

  Query OK rows affected ( sec)

  mysql> use test;

  Reading table information for completion of table and column names

  You can turn off this feature to get a quicker startup with A

  Database changed

  mysql> show tables;

  ++

  | Tables_in_test |

  ++

  | demo_test      |

  | t             |

  ++

   rows in set ( sec)

  mysql> select * from t;

  ++++++++

  | id | fname  | lname  | birthday   | c | c | c |

  ++++++++

  | | lily | lucy | | |

  | | lily | lucy | | |

  ++++++++

   rows in set ( sec)

  mysql> update t set birthday = where id = ;

  Query OK row affected ( sec)

  Rows matched:   Changed:   Warnings:

  mysql> select connection_id()

  ++

  | connection_id() |

  ++

  |              |

  ++

   row in set ( sec)

  mysql>

  線程B 我們用來進行普通的更新但是遇到問題了此時不知道是哪個線程把這行記錄給鎖定了?

  mysql> use test;

  Reading table information for completion of table and column names

  You can turn off this feature to get a quicker startup with A

  Database changed

  mysql> select @@autocommit;

  ++

  | @@autocommit |

  ++

  |            |

  ++

   row in set ( sec)

  mysql> update t set birthday= where id = ;

  ERROR (HY Lock wait timeout exceeded; try restarting transaction

  mysql> select connection_id()

  ++

  | connection_id() |

  ++

  |              |

  ++

   row in set ( sec)

  mysql> show processlist;

  +++++++++

  | Id | User | Host      | db   | Command | Time | State | Info             |

  +++++++++

  | | root | localhost | NULL | Sleep   | |       | NULL             |

  | | root | localhost | NULL | Sleep   |  |       | NULL             |

  | | root | localhost | test | Sleep   |  |       | NULL             |

  | | root | localhost | test | Query   |    | init  | show processlist |

  | | root | localhost | NULL | Sleep   |    |       | NULL             |

  +++++++++

   rows in set ( sec)

  mysql> show engine innodb status\G

  

  TRANSACTIONS

  

  Trx id counter

  Purge done for trxs n:o < undo n:o < state: running but idle

  History list length

  LIST OF TRANSACTIONS FOR EACH SESSION:

  TRANSACTION not started

  MySQL thread id OS thread handle xfac query id localhost root init

  show engine innodb status

  TRANSACTION ACTIVE sec starting index read

  mysql tables in use locked

  LOCK WAIT lock struct(s) heap size row lock(s)

  MySQL thread id OS thread handle xfabd query id localhost root updating

  update t set birthday= where id =

   TRX HAS BEEN WAITING SEC FOR THIS LOCK TO BE GRANTED:

  RECORD LOCKS space id page no n bits index `PRIMARY` of table `test``t` trx id lock_mode X waiting

  Record lock heap no PHYSICAL RECORD: n_fields ; compact format; info bits

  : len ; hex ; asc ;;

  : len ; hex ec; asc       ;;

  : len ; hex edc; asc ~   ( ;;

  : len ; hex cc; asc lily;;

  : len ; hex c; asc lucy;;

  : len ; hex fcc; asc   W;;

  : len ; hex ; asc     ;;

  : len ; hex ; asc     ;;

  : len ; hex ; asc     ;;

  

  TRANSACTION ACTIVE sec

   lock struct(s) heap size row lock(s) undo log entries

  MySQL thread id OS thread handle xfab query id localhost root cleaning up

  Trx read view will not see trx with id >= sees <

  上面的信息很繁多也看不清楚到底哪裡是哪裡

  不過現在我們只要從數據字典裡面拿出來這部分信息就OK了

  mysql> SELECT * FROM information_schemaINNODB_TRX\G

  *************************** row ***************************

  trx_id:

  trx_state: RUNNING

  trx_started: ::

  trx_requested_lock_id: NULL

  trx_wait_started: NULL

  trx_weight:

  trx_mysql_thread_id:

  trx_query: NULL

  trx_operation_state: NULL

  trx_tables_in_use:

  trx_tables_locked:

  trx_lock_structs:

  trx_lock_memory_bytes:

  trx_rows_locked:

  trx_rows_modified:

  trx_concurrency_tickets:

  trx_isolation_level: REPEATABLE READ

  trx_unique_checks:

  trx_foreign_key_checks:

  trx_last_foreign_key_error: NULL

  trx_adaptive_hash_latched:

  trx_adaptive_hash_timeout:

  trx_is_read_only:

  trx_autocommit_non_locking:

   row in set ( sec)

  mysql>


From:http://tw.wingwit.com/Article/program/MySQL/201311/29500.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.