mysql多个事务更新同一条记录产生死锁
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql多个事务更新同一条记录产生死锁，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7306字，纯文字阅读大概需要11分钟。
内容图文
mysql 以下是用show innodb status 查看的死锁信息，都是通过主键索引userId去更新记录，没有其他索引的影响，不应该会产生死锁啊，请大神帮忙分析下原因。
表索引如下
(....
PRIMARY KEY (userId),
UNIQUE KEY userId_UNIQUE (userId),
UNIQUE KEY userName_UNIQUE USING BTREE (userName)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
LATEST DETECTED DEADLOCK
140626 11:12:09
*** (1) TRANSACTION:
TRANSACTION 0 168550, ACTIVE 13 sec, process no 21006, OS thread id 139721036994304 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 2
MySQL thread id 168698, query id 1710850 localhost 127.0.0.1 root Updating
update users set imei='A00000455A4CFE',last_address='上海市闸北区秣陵路303号',lat=31.254289,lon=121.46208,last_login='2014-06-26 11:11:56',userStatus=11,deviceId='2B54F8EDD4A2DC4150BC5D8A4E0FB340',platform='android',updated='2014-06-26 11:11:56' where userId=15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168550 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;
*** (2) TRANSACTION:
TRANSACTION 0 168551, ACTIVE 1 sec, process no 21006, OS thread id 139720929810176 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 2
MySQL thread id 168699, query id 1710851 localhost 127.0.0.1 root Updating
update users set imei='A00000455A4CFE',last_address='上海市闸北区秣陵路303号',lat=31.254289,lon=121.46208,last_login='2014-06-26 11:12:08',userStatus=11,deviceId='2B54F8EDD4A2DC4150BC5D8A4E0FB340',platform='android',updated='2014-06-26 11:12:08' where userId=15
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168551 lock mode S locks rec but not gap
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168551 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;
*** WE ROLL BACK TRANSACTION (2)
内容总结
以上是互联网集市为您收集整理的mysql多个事务更新同一条记录产生死锁全部内容，希望文章能够帮你解决mysql多个事务更新同一条记录产生死锁所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。