最近在項目開發過程中碰到了數據庫死鎖問題在解決問題的過程中筆者對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
