登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

SeaRiver Blog

实力才是你一生最好的依靠!

 
 
 

日志

 
 

一个MySQL死锁问题的解决  

2008-06-11 01:20:40|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

最近在项目开发过程中,碰到了数据库的死锁问题,在解决问题的过程中,加深了对MySQL InnoDB引擎锁机制的理解。 
 
我们使用Show innodb status检查引擎状态时,发现了死锁问题: 
 
*** (1) TRANSACTION: 
TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read 
mysql tables in use 1, locked 1 
LOCK WAIT 3 lock struct(s), heap size 320 
MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update 
update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL 30 minute) 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap waiting 
Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 
0: len 8; hex 800000000097629c; asc      b ;; 1: len 6; hex 00002866eaee; asc  (f  ;; 2: len 7; hex 00000d40040110; asc    @  ;; 3: len 8; hex 80000000000050b2; asc      P ;; 4: len 8; hex 800000000000502a; asc      P*;; 5: len 8; hex 8000000000005426; asc      T&;; 6: len 8; hex 800012412c66d29c; asc    A,f  ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc        +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc      N$;; 
 
*** (2) TRANSACTION: 
TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499 
mysql tables in use 1, locked 1 
3 lock struct(s), heap size 320, undo log entries 1 
MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating 
update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180) 
*** (2) HOLDS THE LOCK(S): 
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap 
Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 
0: len 8; hex 800000000097629c; asc      b ;; 1: len 6; hex 00002866eaee; asc  (f  ;; 2: len 7; hex 00000d40040110; asc    @  ;; 3: len 8; hex 80000000000050b2; asc      P ;; 4: len 8; hex 800000000000502a; asc      P*;; 5: len 8; hex 8000000000005426; asc      T&;; 6: len 8; hex 800012412c66d29c; asc    A,f  ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc        +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc      N$;; 
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting 
Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 
0: len 8; hex 8000000000000425; asc        %;; 1: len 8; hex 800012412c66d29c; asc    A,f  ;; 2: len 8; hex 800000000097629c; asc      b ;; 
 
*** WE ROLL BACK TRANSACTION (1) 
 
该死锁问题涉及TSK_TASK表,该表用于保存系统监测任务,相关字段及索引如下: 
 
 
ID:主键; 
 
MON_TIME:监测时间; 
 
STATUS_ID:任务状态; 
 
索引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。 
 
 
经分析,涉及的两条语句应该不会涉及相同的TSK_TASK记录,那为什么会造成死锁呢? 
 
查询MySQL官网文档,发现这跟MySQL的索引机制有关。MySQL的InnoDB引擎是行级锁,我原来的理解是直接对记录进行锁定,实际上不是这样的,其要点如下: 
 
 
不是对记录进行锁定,而是对索引进行锁定; 
 
在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking; 
如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000会锁定所有主键大于等于1000的所有记录,在该语句完成之前,你就不能对主键等于10000的记录进行操作; 
 
当非簇索引(non-cluster index)记录被锁定时,相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作。 
 
再分析一下发生问题的两条SQL语句,就不难找到问题所在了: 
 
当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL 30 minute)”执行时,MySQL会使用KEY_TSKTASK_MONTIME2索引,因此首先锁定相关的索引记录,因为KEY_TSKTASK_MONTIME2是非簇索引,为执行该语句,MySQL还会锁定簇索引(主键索引)。 
 
假设“update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)”几乎同时执行时,本语句首先锁定簇索引(主键),由于需要更新STATUS_ID的值,所以还需要锁定KEY_TSKTASK_MONTIME2的某些索引记录。 
 
这样第一条语句锁定了KEY_TSKTASK_MONTIME2的记录,等待主键索引,而第二条语句则锁定了主键索引记录,而等待KEY_TSKTASK_MONTIME2的记录,这样死锁就产生了。 
 
我们通过拆分第一条语句解决了死锁问题:即先查出符合条件的ID:select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute);然后再更新状态:update TSK_TASK set STATUS_ID=1064 where ID in (….) 
 
这样就不会产生索引的竞争问题,死锁问题就解决了。
 
  评论这张
 
阅读(3398)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018