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

巧用MySQL InnoDB引擎鎖機制解決死鎖問題[1]

2013-11-23 21:06:48  來源: MySQL 

  最近在項目開發過程中碰到了數據庫死鎖問題在解決問題的過程中筆者對MySQL InnoDB引擎鎖機制的理解逐步加深

  案例如下

  在使用Show innodb status檢查引擎狀態時發現了死鎖問題

  *** () TRANSACTION:
  TRANSACTION ACTIVE sec process no OS thread id starting index read
  mysql tables in use locked
  LOCK WAIT lock struct(s) heap size
  MySQL thread id query id dcnet dcnet Searching rows for update
  update TSK_TASK set STATUS_ID=UPDATE_TIME=now () where STATUS_ID= and MON_TIME*** () WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id page no n bits index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id lock_mode X locks rec but not gap waiting
  Record lock heap no PHYSICAL RECORD: n_fields ; compact format; info bits
  : len ; hex c; asc b ;; : len ; hex eaee; asc (f ;; : len ; hex d; asc @ ;; : len ; hex b; asc P ;; : len ; hex a; asc P*;; : len ; hex ; asc T&;; : len ; hex cdc; asc Af ;; : len ; hex cfefdf ee; asc xxxcom/;; : len ; hex b; asc +;; : len ; hex bfab; asc GK +;; : len ; hex e; asc N$;;
  *** () TRANSACTION:
  TRANSACTION ACTIVE sec process no OS thread id updating or deleting thread declared inside InnoDB
  mysql tables in use locked
   lock struct(s) heap size undo log entries
  MySQL thread id query id dcnet dcnet Updating
  update TSK_TASK set STATUS_ID=UPDATE_TIME=now () where ID in ()
  *** () HOLDS THE LOCK(S):
  RECORD LOCKS space id page no n bits index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id lock_mode X locks rec but not gap
  Record lock heap no PHYSICAL RECORD: n_fields ; compact format; info bits
  : len ; hex c; asc b ;; : len ; hex eaee; asc (f ;; : len ; hex d; asc @ ;; : len ; hex b; asc P ;; : len ; hex a; asc P*;; : len ; hex ; asc T&;; : len ; hex cdc; asc Af ;; : len ; hex cfefdf ee; asc uploadfirecom/handphp;; : len ; hex b; asc +;; : len ; hex bfab; asc GK +;; : len ; hex e; asc N$;;
  *** () WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id page no n bits index `KEY_TSKTASK_MONTIME` of table `dcnet_db/TSK_TASK` trx id lock_mode X locks rec but not gap waiting
  Record lock heap no PHYSICAL RECORD: n_fields ; compact format; info bits
  : len ; hex ; asc %;; : len ; hex cdc; asc Af ;; : len ; hex c; asc b ;;
  *** WE ROLL BACK TRANSACTION ()

  此死鎖問題涉及TSK_TASK表該表用於保存系統監測任務以下是相關字段及索引

  ID主鍵
  MON_TIME監測時間
  STATUS_ID任務狀態

[]  []  


From:http://tw.wingwit.com/Article/program/MySQL/201311/29600.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.