免费注册 查看新帖 |

Chinaunix

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

Mysql Deadlock求解决方法 [复制链接]

论坛徽章:
0
发表于 2013-10-20 13:27 |显示全部楼层
------------------------
LATEST DETECTED DEADLOCK
------------------------
131019 16:57:08
*** (1) TRANSACTION:
TRANSACTION 0 421919767, ACTIVE 1 sec, process no 25282, OS thread id 1079552320 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 4 row lock(s), undo log entries 2
MySQL thread id 39547, query id 120013806 192.168.128.6 hotel Updating
UPDATE BOOKING_EXAM_RECORD    SET EXAM_STATUS = '2',     UPDATE_ID = '2668',     UPDATE_TIME = NOW()    WHERE BOOKING_CD = '20131017000331'      AND exam_id_num = '2668'     AND seq = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 180830 n bits 176 index `PRIMARY` of table `hotel_db`.`booking_exam_record` trx id 0 421919767 lock_mode X locks rec but not gap waiting
Record lock, heap no 96 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030333331; asc 20131017000331;; 1: len 4; hex 80000001; asc     ;; 2: len 6; hex 00001925f472; asc    % r;; 3: len 7; hex 00000b00031002; asc        ;; 4: len 4; hex 32363638; asc 2668;; 5: len 1; hex 30; asc 0;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e30a83b; asc    O 0 ;;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32363638; asc 2668;; 12: len 8; hex 8000124f1e3f0f6a; asc    O ? j;; 13: len 4; hex 32363338; asc 2638;; 14: len 8; hex 8000124f1e30a83b; asc    O 0 ;;;

*** (2) TRANSACTION:
TRANSACTION 0 421919732, ACTIVE 4 sec, process no 25282, OS thread id 1103190336 fetching rows, thread declared inside InnoDB 2
mysql tables in use 1, locked 1
9940 lock struct(s), heap size 997360, 1078517 row lock(s), undo log entries 4
MySQL thread id 39616, query id 120013783 192.168.128.6 hotel Updating
UPDATE booking_info      SET operate_id_num = '2653' ,          update_id = '2653' ,          update_time = SYSDATE()    WHERE booking_cd IN (20131018001247,20131018001396,20131011000675)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 180830 n bits 176 index `PRIMARY` of table `hotel_db`.`booking_exam_record` trx id 0 421919732 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030323930; asc 20131017000290;; 1: len 4; hex 80000000; asc     ;; 2: len 6; hex 0000190b8a58; asc      X;; 3: len 7; hex 00000640150c30; asc    @  0;; 4: len 4; hex 32353530; asc 2550;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f99; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32353530; asc 2550;; 12: len 8; hex 8000124f1e2f2aa1; asc    O /* ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f99; asc    O .  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030323932; asc 20131017000292;; 1: len 4; hex 80000000; asc     ;; 2: len 6; hex 00001907684b; asc     hK;; 3: len 7; hex 0000108002153e; asc       >;; 4: len 4; hex 32363434; asc 2644;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f98; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32363434; asc 2644;; 12: len 8; hex 8000124f1e2eb484; asc    O .  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f98; asc    O .  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030323932; asc 20131017000292;; 1: len 4; hex 80000001; asc     ;; 2: len 6; hex 00001907684b; asc     hK;; 3: len 7; hex 80001080140136; asc       6;; 4: len 4; hex 32363434; asc 2644;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f98; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32363434; asc 2644;; 12: len 8; hex 8000124f1e2eb484; asc    O .  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f98; asc    O .  ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030323933; asc 20131017000293;; 1: len 4; hex 80000000; asc     ;; 2: len 6; hex 0000190ee4cb; asc       ;; 3: len 7; hex 000006401c0fd0; asc    @   ;; 4: len 4; hex 32373034; asc 2704;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f98; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32373034; asc 2704;; 12: len 8; hex 8000124f1e2f7faf; asc    O /  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f98; asc    O .  ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030323933; asc 20131017000293;; 1: len 4; hex 80000001; asc     ;; 2: len 6; hex 0000190ee4cb; asc       ;; 3: len 7; hex 80000640050137; asc    @  7;; 4: len 4; hex 32373034; asc 2704;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f98; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32373034; asc 2704;; 12: len 8; hex 8000124f1e2f7faf; asc    O /  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f98; asc    O .  ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030323936; asc 20131017000296;; 1: len 4; hex 80000000; asc     ;; 2: len 6; hex 00001910fdce; asc       ;; 3: len 7; hex 00000640080ebc; asc    @   ;; 4: len 4; hex 32353530; asc 2550;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f99; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32353530; asc 2550;; 12: len 8; hex 8000124f1e2fbbe8; asc    O /  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f99; asc    O .  ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030323936; asc 20131017000296;; 1: len 4; hex 80000001; asc     ;; 2: len 6; hex 00001910fdce; asc       ;; 3: len 7; hex 80000640040135; asc    @  5;; 4: len 4; hex 32353530; asc 2550;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f99; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32353530; asc 2550;; 12: len 8; hex 8000124f1e2fbbe8; asc    O /  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f99; asc    O .  ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030333031; asc 20131017000301;; 1: len 4; hex 80000000; asc     ;; 2: len 6; hex 00001910ae50; asc      P;; 3: len 7; hex 00000640060a34; asc    @  4;; 4: len 4; hex 32353530; asc 2550;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f9a; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32353530; asc 2550;; 12: len 8; hex 8000124f1e2fb7e6; asc    O /  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f9a; asc    O .  ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030333031; asc 20131017000301;; 1: len 4; hex 80000001; asc     ;; 2: len 6; hex 00001910ae50; asc      P;; 3: len 7; hex 80000640040139; asc    @  9;; 4: len 4; hex 32353530; asc 2550;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f9a; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32353530; asc 2550;; 12: len 8; hex 8000124f1e2fb7e6; asc    O /  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f9a; asc    O .  ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030333032; asc 20131017000302;; 1: len 4; hex 80000000; asc     ;; 2: len 6; hex 0000190d6f54; asc     oT;; 3: len 7; hex 00000640090a53; asc    @  S;; 4: len 4; hex 32363131; asc 2611;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f99; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32363131; asc 2611;; 12: len 8; hex 8000124f1e2f697b; asc    O /i{;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f99; asc    O .  ;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030333032; asc 20131017000302;; 1: len 4; hex 80000001; asc     ;; 2: len 6; hex 0000190d6f54; asc     oT;; 3: len 7; hex 8000064003015c; asc    @  \;; 4: len 4; hex 32363131; asc 2611;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f99; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32363131; asc 2611;; 12: len 8; hex 8000124f1e2f697b; asc    O /i{;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f99; asc    O .  ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 14; hex 3230313331303137303030333036; asc 20131017000306;; 1: len 4; hex 80000000; asc     ;; 2: len 6; hex 00001908817e; asc      ~;; 3: len 7; hex 0000108031035a; asc     1 Z;; 4: len 4; hex 32363434; asc 2644;; 5: len 1; hex 32; asc 2;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 8; hex 8000124f1e2e7f99; asc    O .  ;; 9: len 1; hex 30; asc 0;; 10: SQL NULL; 11: len 4; hex 32363434; asc 2644;; 12: len 8; hex 8000124f1e2ed88d; asc    O .  ;; 13: len 10; hex 64697370617463686572; asc dispatcher;; 14: len 8; hex 8000124f1e2e7f99; asc    O .  ;;

如何处理?
这是show engine innodb status看到的信息,当有死锁产生时,查看不到正确的innodb  select insert update delete状态了,如何解决?清空这些死锁信息(除重启外)

论坛徽章:
1
未羊
日期:2013-10-18 11:47:51
发表于 2013-10-20 15:38 |显示全部楼层
发现死锁时,InnoDB会立马回滚事务,你查不到是正常的,
我想请问,您的环境并发度很高吗?

论坛徽章:
0
发表于 2013-10-20 17:00 |显示全部楼层
回复 2# 幕南风


    嗯,读很高的。

论坛徽章:
1
未羊
日期:2013-10-18 11:47:51
发表于 2013-10-20 17:08 |显示全部楼层
jason_teach 发表于 2013-10-20 17:00
回复 2# 幕南风


您这边是Record lock,属于InnoDB行级锁
不妨看下:
show status like 'innodb_row_lock_%'

论坛徽章:
0
发表于 2013-10-20 19:45 |显示全部楼层
回复 4# 幕南风


    mysql> show status like 'innodb_row_lock_%';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_row_lock_current_waits | 0       |
| Innodb_row_lock_time          | 7376959 |
| Innodb_row_lock_time_avg      | 23951   |
| Innodb_row_lock_time_max      | 101822  |
| Innodb_row_lock_waits         | 308     |
+-------------------------------+---------+
5 rows in set (0.00 sec)


或许我应该调整这个值了。
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

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

清除 Cookies - ChinaUnix - Archiver - WAP - TOP