免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 3920 | 回复: 8

innodb死锁问题 [复制链接]

论坛徽章:
0
发表于 2010-04-09 14:26 |显示全部楼层
本帖最后由 justin033 于 2010-04-10 12:47 编辑

RT,死锁的信息如下:
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 100409 10:19:46
  5. *** (1) TRANSACTION:
  6. TRANSACTION 28B491, ACTIVE 0 sec, process no 12065, OS thread id 1101347136 fetching rows
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 39 lock struct(s), heap size 6752, 79 row lock(s)
  9. MySQL thread id 1079022, query id 13143355 10.10.10.151 yilin init
  10. update email_package set claimed_process = '1010101519330', start_time = '2010-04-09 10:24:24.0', task_status_id = 1   where service_id = 'Carrier' and (task_status_id = 0 or (task_status_id = 1 and start_time <= '2010-04-09 09:24:24.0'))  and (schedule_time is null or schedule_time <= '2010-04-09 10:24:24.0')  and domain_name not in ('126.com','qq.com')  and (claimed_process is null or claimed_process = '1010101519330') order by immediately desc, mixed_priority asc, priority asc limit 1
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B491 lock_mode X waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 28B490, ACTIVE 0 sec, process no 12065, OS thread id 1101879616 inserting, thread declared inside InnoDB 500
  15. mysql tables in use 1, locked 1
  16. 3 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 5
  17. MySQL thread id 1079021, query id 13143358 10.10.10.141 yilin update
  18. insert into email_package (task_id,task_name,task_type_id,service_id, client_id,user_id,object_id,group_id,task_status_id,immediately,schedule_time, start_time,end_time,create_time,order_number,priority,restart_times,weight,parameters,language,domain_name,claimed_process) values ('8bkqnje6','send msg 1043.3226-gmail.com',2,'Carrier',1043,144,3226,null,0,'N','2010-04-09 10:24:25.0','1970-01-01 08:00:00.0','1970-01-01 08:00:00.0','2010-04-09 10:24:25.0',0,31,0,11,null,'en','gmail.com',null)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B490 lock_mode X locks rec but not gap
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B490 lock_mode X locks gap before rec insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (2)
复制代码
表的索引结构如下:
  1. "email_package","0","PRIMARY","1","task_id","A","95",NULL,NULL,"","BTREE",""
  2. "email_package","1","ix_ep_01","1","task_status_id","A","7",NULL,NULL,"","BTREE",""
复制代码
  1. update email_package set claimed_process = '1010101519330', start_time = '2010-04-09 10:24:24.0', task_status_id = 1   where service_id = 'Carrier' and (task_status_id = 0 or (task_status_id = 1 and start_time <= '2010-04-09 09:24:24.0'))  and (schedule_time is null or schedule_time <= '2010-04-09 10:24:24.0')  and domain_name not in ('126.com','qq.com')  and (claimed_process is null or claimed_process = '1010101519330') order by immediately desc, mixed_priority asc, priority asc limit 1
复制代码


  1. insert into email_package (task_id,task_name,task_type_id,service_id, client_id,user_id,object_id,group_id,task_status_id,immediately,schedule_time, start_time,end_time,create_time,order_number,priority,restart_times,weight,parameters,language,domain_name,claimed_process) values ('8bkqnje6','send msg 1043.3226-gmail.com',2,'Carrier',1043,144,3226,null,0,'N','2010-04-09 10:24:25.0','1970-01-01 08:00:00.0','1970-01-01 08:00:00.0','2010-04-09 10:24:25.0',0,31,0,11,null,'en','gmail.com',null)
复制代码




由于这个表email_package的insert、update都挺经常。上述红色的两条sql,和开发人员交流了说业务逻辑是那么复杂的,暂时没法改。现在就是经常出现死锁。有什么好的建议,

论坛徽章:
0
发表于 2010-04-09 15:13 |显示全部楼层
首先,要根据update那句的条件优化索引。
其次,把update改成两句,先select出来主键ID,再按主键ID更新。

论坛徽章:
0
发表于 2010-04-09 15:28 |显示全部楼层
目前不能更改update语句,呵呵,还有其他好办法么?

论坛徽章:
0
发表于 2010-04-09 17:57 |显示全部楼层
本帖最后由 justin033 于 2010-04-09 18:10 编辑

占位。。。

论坛徽章:
0
发表于 2010-04-10 09:42 |显示全部楼层
  1. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

  2. RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B491 lock_mode X waiting

复制代码


  1. *** (2) HOLDS THE LOCK(S):

  2. RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B490 lock_mode X locks rec but not gap
复制代码

两个玩意都争用这个索引上的锁了

论坛徽章:
0
发表于 2010-04-10 09:44 |显示全部楼层
说明死锁是由这个索引造成的,你这个查询写的索引好差啊。。。。我汗了

论坛徽章:
0
发表于 2010-04-10 11:51 |显示全部楼层
正解。学习了。。。

论坛徽章:
0
发表于 2010-04-12 12:32 |显示全部楼层
正解。学习了。。。
justin033 发表于 2010-04-10 11:51



    这种SQL可以说是表在设计时造成的。
    优化这种SQL有两个方法:
    一、改表结构,走第三范式设计。
    二、用更强的机器的机器。

论坛徽章:
0
发表于 2010-04-12 14:21 |显示全部楼层
提示: 作者被禁止或删除 内容自动屏蔽
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP