免费注册 查看新帖 |

Chinaunix

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

【已解决】各位帮忙看一下死锁问题 [复制链接]

论坛徽章:
0
发表于 2012-08-01 11:43 |显示全部楼层
本帖最后由 cenalulu 于 2012-08-01 15:29 编辑

发现一张表几乎每天都会出现死锁现象
show engine innodb status ,结果如下:
  1. | InnoDB |      |
  2. =====================================
  3. 120801 11:39:27 INNODB MONITOR OUTPUT
  4. =====================================
  5. Per second averages calculated from the last 57 seconds
  6. ----------
  7. SEMAPHORES
  8. ----------
  9. OS WAIT ARRAY INFO: reservation count 51384484, signal count 48846140
  10. Mutex spin waits 0, rounds 1018011722, OS waits 16297634
  11. RW-shared spins 50943146, OS waits 25603297; RW-excl spins 10433570, OS waits 5954627
  12. ------------------------
  13. LATEST DETECTED DEADLOCK
  14. ------------------------
  15. 120801 10:26:55
  16. *** (1) TRANSACTION:
  17. TRANSACTION 1 3307885574, ACTIVE 10 sec, process no 9567, OS thread id 139743974278928 fetching rows
  18. mysql tables in use 1, locked 1
  19. LOCK WAIT 5366 lock struct(s), heap size 620528, 36645 row lock(s)
  20. MySQL thread id 451507729, query id 6609691877 192.168.10.161 web Searching rows for update
  21. UPDATE user_notice_2 SET is_read = '1' WHERE type = '12' AND user_id = '269786'
  22. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  23. RECORD LOCKS space id 0 page no 3515164 n bits 320 index `PRIMARY` of table `xxxx`.`user_notice_2` trx id 1 3307885574 lock_mode X locks rec but not gap waiting
  24. Record lock, heap no 172 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
  25. 0: len 4; hex 8031acdc; asc  1  ;; 1: len 6; hex 0001c52a52c7; asc    *R ;; 2: len 7; hex 00003f8024055b; asc   ? $ [;; 3: len 4; hex 8000bfe2; asc     ;; 4: len 4; hex 8000f95c; asc    \;; 5: len 4; hex 80000000; asc     ;; 6: len 4; hex 80000006; asc     ;; 7: len 1; hex 81; asc  ;; 8: len 4; hex 80041dda; asc     ;; 9: len 4; hex 80000903; asc     ;; 10: len 4; hex 80000001; asc     ;; 11: len 4; hex 80000001; asc     ;; 12: len 4; hex cf6f2a42; asc  o*B;;

  26. *** (2) TRANSACTION:
  27. TRANSACTION 1 3307885255, ACTIVE 16 sec, process no 9567, OS thread id 139743724054288 fetching rows, thread declared inside InnoDB 222
  28. mysql tables in use 1, locked 1
  29. 5125 lock struct(s), heap size 587760, 33201 row lock(s), undo log entries 175
  30. MySQL thread id 451507525, query id 6609689171 192.168.1.58 web Updating
  31. UPDATE user_notice_2 SET is_read = '1' WHERE type = '6' AND user_id = '269786'
  32. *** (2) HOLDS THE LOCK(S):
  33. RECORD LOCKS space id 0 page no 3515164 n bits 320 index `PRIMARY` of table `xxxx`.`user_notice_2` trx id 1 3307885255 lock_mode X locks rec but not gap
  34. Record lock, heap no 172 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
  35. 0: len 4; hex 8031acdc; asc  1  ;; 1: len 6; hex 0001c52a52c7; asc    *R ;; 2: len 7; hex 00003f8024055b; asc   ? $ [;; 3: len 4; hex 8000bfe2; asc     ;; 4: len 4; hex 8000f95c; asc    \;; 5: len 4; hex 80000000; asc     ;; 6: len 4; hex 80000006; asc     ;; 7: len 1; hex 81; asc  ;; 8: len 4; hex 80041dda; asc     ;; 9: len 4; hex 80000903; asc     ;; 10: len 4; hex 80000001; asc     ;; 11: len 4; hex 80000001; asc     ;; 12: len 4; hex cf6f2a42; asc  o*B;;

  36. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  37. RECORD LOCKS space id 0 page no 4282304 n bits 320 index `PRIMARY` of table `xxxx`.`user_notice_2` trx id 1 3307885255 lock_mode X locks rec but not gap waiting
  38. Record lock, heap no 103 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
  39. 0: len 4; hex 8094ba21; asc    !;; 1: len 6; hex 0001c3d9f6be; asc       ;; 2: len 7; hex 80001840120110; asc    @   ;; 3: len 4; hex 80019c71; asc    q;; 4: len 4; hex 800145d5; asc   E ;; 5: len 4; hex 80000000; asc     ;; 6: len 4; hex 80000006; asc     ;; 7: len 1; hex 81; asc  ;; 8: len 4; hex 80041dda; asc     ;; 9: len 4; hex 8006c3f0; asc     ;; 10: len 4; hex 80000001; asc     ;; 11: len 4; hex 80000000; asc     ;; 12: len 4; hex d0160d7c; asc    |;;

  40. *** WE ROLL BACK TRANSACTION (2)
  41. ------------
  42. TRANSACTIONS
  43. ------------
  44. Trx id counter 1 3308366016
  45. Purge done for trx's n:o < 1 3308364543 undo n:o < 0 0
  46. History list length 36
  47. LIST OF TRANSACTIONS FOR EACH SESSION:
  48. ---TRANSACTION 0 0, not started, process no 9567, OS thread id 139743705683728
  49. MySQL thread id 451506124, query id 6612683330 localhost root
  50. show engine innodb status
  51. --------
  52. FILE I/O
  53. --------
  54. I/O thread 0 state: waiting for i/o request (insert buffer thread)
  55. I/O thread 1 state: waiting for i/o request (log thread)
  56. I/O thread 2 state: waiting for i/o request (read thread)
  57. I/O thread 3 state: waiting for i/o request (write thread)
  58. Pending normal aio reads: 0, aio writes: 0,
  59. ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
  60. Pending flushes (fsync) log: 0; buffer pool: 0
  61. 484129428 OS file reads, 569326131 OS file writes, 14401634 OS fsyncs
  62. 83.58 reads/s, 19781 avg bytes/read, 24.96 writes/s, 1.75 fsyncs/s
  63. -------------------------------------
  64. INSERT BUFFER AND ADAPTIVE HASH INDEX
  65. -------------------------------------
  66. Ibuf: size 56, free list len 7387, seg size 7444,
  67. 32140121 inserts, 32131078 merged recs, 6226493 merges
  68. Hash table size 9239933, node heap has 17416 buffer(s)
  69. 1140.79 hash searches/s, 628.04 non-hash searches/s
  70. ---
  71. LOG
  72. ---
  73. Log sequence number 215 1052551632
  74. Log flushed up to   215 1052550237
  75. Last checkpoint at  215 1046136556
  76. 0 pending log writes, 0 pending chkp writes
  77. 494759215 log i/o's done, 12.96 log i/o's/second
  78. ----------------------
  79. BUFFER POOL AND MEMORY
  80. ----------------------
  81. Total memory allocated 4665606282; in additional pool allocated 1040896
  82. Dictionary memory allocated 1243248
  83. Buffer pool size   256000
  84. Free buffers       0
  85. Database pages     238584
  86. Modified db pages  7894
  87. Pending reads 0
  88. Pending writes: LRU 0, flush list 0, single page 0
  89. Pages read 662841221, created 1168882, written 117181538
  90. 100.91 reads/s, 0.02 creates/s, 21.02 writes/s
  91. Buffer pool hit rate 991 / 1000
  92. --------------
  93. ROW OPERATIONS
  94. --------------
  95. 0 queries inside InnoDB, 0 queries in queue
  96. 1 read views open inside InnoDB
  97. Main thread process no. 9567, id 139744041867024, state: sleeping
  98. Number of rows inserted 93903841, updated 390672007, deleted 13535688, read 1271485043994
  99. 1.77 inserts/s, 36.24 updates/s, 0.00 deletes/s, 45229.31 reads/s
  100. ----------------------------
  101. END OF INNODB MONITOR OUTPUT
  102. ============================
复制代码

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 2012-08-01 13:31 |显示全部楼层
光从mysql给的信息,较难进行分析,因为环状锁之类的不会体现在innodb status中。
建议还是从程序sql的角度,人工的走着分析下可能发生死锁的地方

论坛徽章:
0
发表于 2012-08-01 14:57 |显示全部楼层
UPDATE user_notice_2 SET is_read = '1' WHERE type = '12' AND user_id = '269786'

UPDATE user_notice_2 SET is_read = '1' WHERE type = '6' AND user_id = '269786'

这两条sql,分别是前端的2台机器过来的,单独select 记录也比较多,已经让开发去解决了。

论坛徽章:
0
发表于 2012-08-01 16:29 |显示全部楼层
能说说,这两句为啥会死锁吗?

论坛徽章:
0
发表于 2012-08-01 17:22 |显示全部楼层
本帖最后由 o0o520 于 2012-08-01 17:23 编辑
jijianjie 发表于 2012-08-01 16:29
能说说,这两句为啥会死锁吗?


这两条语句应该都锁主键索引和那个非主键索引

论坛徽章:
0
发表于 2012-08-01 17:23 |显示全部楼层
这两条update语句肯定有至少两块索引块需要锁定  由于锁定的顺序不同  所以会产生死锁

论坛徽章:
0
发表于 2012-08-01 17:29 |显示全部楼层
本帖最后由 o0o520 于 2012-08-01 17:32 编辑
rucypli 发表于 2012-08-01 17:23
这两条update语句肯定有至少两块索引块需要锁定  由于锁定的顺序不同  所以会产生死锁


是这么个情况,分析得很到位

论坛徽章:
0
发表于 2012-08-01 18:12 |显示全部楼层
恩,不错。学习了。






婴童寝居网 http://www.61mami.com

论坛徽章:
0
发表于 2012-08-02 16:10 |显示全部楼层
本帖最后由 G8bao7 于 2012-08-02 16:18 编辑

回复 7# o0o520


    由于两个update的where条件相同,只是具体值不同,那么锁定的主键索引和辅助索引的区域应该不一样,即使顺序不同也不应该产生死锁
  
除非两个update锁定的索引(主键、辅助)正好相邻,可能会产生死锁
   

求正解

论坛徽章:
0
发表于 2012-08-03 09:34 |显示全部楼层
我觉得应该是间隙锁造成的,版主来给解释一下吧
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

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

清除 Cookies - ChinaUnix - Archiver - WAP - TOP