巧用MySQL InnoDB引擎锁机制解决死锁问题[1]

最近在项目开发过程中碰到了数据库死锁问题在解决问题的过程中笔者对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任务状态

[] []