ITPub博客

首页 > Linux操作系统 > Linux操作系统 > innodb_lock_monitor解决mysql死锁

innodb_lock_monitor解决mysql死锁

原创 Linux操作系统 作者:aaqwsh 时间:2011-04-15 17:47:38 0 删除 编辑
开发说,mysql出现Translating SQLException with SQL state '41000', error code '1205', message [Lock wait timeout exceeded; try restarting transaction];
通过innodb_lock_monitor看到有如下语句一直在运行,同时有lock。
insert into xxxx( ...)  select .... from xxxx 关联很多表很多复杂聚合,
 
处理:

修改一下插入逻辑,先把记录查询出来,然后在分步提交insert

 
结果,问题解决了,但是还是有疑问,得继续研究。
 
Translating SQLException with SQL state '41000', error code '1205', message [Lock wait timeout exceeded; try restarting transaction];

Trx read view will not see trx with id >= 0 5185563, sees < 0 5185563
TABLE LOCK table `xxxx`.`xxxx` trx id 0 5185562 lock mode IX
RECORD LOCKS space id 0 page no 480 n bits 1368 index `idx_xxxxxx_ID` of table `xxxx`.`xxxx` trx id 0 5185562 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 317 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 808cca; asc    ;; 1: len 3; hex 85334e; asc  3N;;
 0: len 3; hex 808ccd; asc    ;; 1: len 3; hex 871dea; asc    ;;
Record lock, heap no 1287 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 808cce; asc    ;; 1: len 3; hex 871deb; asc    ;;
Record lock, heap no 1288 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 808ccb; asc    ;; 1: len 3; hex 878666; asc   f;;
Record lock, heap no 1289 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 808cce; asc    ;; 1: len 3; hex 8ac665; asc   e;;
Record lock, heap no 1300 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 808cca; asc    ;; 1: len 3; hex 87351a; asc  5 ;;
Record lock, heap no 1301 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 808ccb; asc    ;; 1: len 3; hex 87351b; asc  5 ;;
RECORD LOCKS space id 0 page no 1573 n bits 184 index `PRIMARY` of table `xxxx`.`zszzz` trx id 0 5185562 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 853349; asc  3I;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b03830; asc      80;; 3: len 3; hex 808ccb; asc    ;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 6; hex e9a9ace58faf; asc       ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 332e32; asc 3.2;; 11: len 1; hex 33; asc 3;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 8000000c; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 332e3030; asc 3.00;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 85334a; asc  3J;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b0383c; asc      8<;; 3: len 3; hex 808d5a; asc   Z;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 9; hex e8afbae8a5bfe4ba9a; asc          ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 302e30; asc 0.0;; 11: len 1; hex 37; asc 7;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 80000000; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 302e3135; asc 0.15;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 85334b; asc  3K;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b03848; asc      8H;; 3: len 3; hex 808d39; asc   9;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 6; hex e79c9fe5bda9; asc       ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 302e30; asc 0.0;; 11: len 1; hex 36; asc 6;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 80000000; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 302e3630; asc 0.60;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 85334c; asc  3L;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b03854; asc      8T;; 3: len 3; hex 808d33; asc   3;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 6; hex e993b6e8a385; asc       ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 302e30; asc 0.0;; 11: len 1; hex 37; asc 7;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 80000000; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 302e3235; asc 0.25;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 853353; asc  3S;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b038a8; asc      8 ;; 3: len 3; hex 808d55; asc   U;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 9; hex e699aee4b990e5a3ab; asc          ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 312e30; asc 1.0;; 11: len 1; hex 37; asc 7;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 80000001; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 302e3135; asc 0.15;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 853354; asc  3T;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b038b4; asc      8 ;; 3: len 3; hex 808d4f; asc   O;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 6; hex e69687e4bbaa; asc       ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 302e30; asc 0.0;; 11: len 1; hex 37; asc 7;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 80000000; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 302e3135; asc 0.15;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
Record lock, heap no 14 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 853355; asc  3U;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b038c0; asc      8 ;; 3: len 3; hex 808d3a; asc   :;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 6; hex e6b0b8e58f91; asc       ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 322e30; asc 2.0;; 11: len 1; hex 37; asc 7;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 80000001; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 302e3135; asc 0.15;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
Record lock, heap no 15 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 3; hex 853356; asc  3V;; 1: len 6; hex 00000005c3d7; asc       ;; 2: len 7; hex 80000005b038cc; asc      8 ;; 3: len 3; hex 808d62; asc   b;; 4: len 3; hex 800001; asc    ;; 5: len 3; hex 800001; asc    ;; 6: SQL NULL; 7: len 9; hex e58fafe5be97e4bc98; asc          ;; 8: len 3; hex 302e30; asc 0.0;; 9: len 3; hex 302e30; asc 0.0;; 10: len 3; hex 302e30; asc 0.0;; 11: len 1; hex 37; asc 7;; 12: len 4; hex 80000000; asc     ;; 13: len 4; hex 80000000; asc     ;; 14: len 4; hex 80000000; asc     ;; 15: len 4; hex 302e3030; asc 0.00;; 16: len 3; hex 302e30; asc 0.0;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: len 4; hex 302e3030; asc 0.00;; 22: len 4; hex 302e3135; asc 0.15;; 23: len 5; hex 302e303025; asc 0.00%;; 24: len 4; hex 80000000; asc     ;; 25: len 4; hex 80000000; asc     ;; 26: len 4; hex 80000000; asc     ;; 27: len 4; hex 80000000; asc     ;; 28: len 3; hex 302e30; asc 0.0;; 29: len 8; hex 8000124a40837176; asc    J@ qv;; 30: len 8; hex 8000124a40900e24; asc    J@  $;;
 
 
 
 

开启:

 

mysql> use marketing

Database changed

mysql>

 

mysql> create table innodb_lock_monitor(x int) engine=innodb;

Query OK, 0 rows affected (0.08 sec)

 

mysql> exit

Bye

 

 

关闭:

 

mysql> use marketing

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> drop table innodb_lock_monitor;

Query OK, 0 rows affected (0.02 sec)

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-692630/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    262215