Bug #58892 deadlock
Submitted: 13 Dec 2010 6:45 Modified: 13 Dec 2010 7:52
Reporter: yang wang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5,1,47-x86-64 OS:Linux
Assigned to: CPU Architecture:Any
Tags: deadlock

[13 Dec 2010 6:45] yang wang 
Description:
------------------------
LATEST DETECTED DEADLOCK
------------------------
101210 14:57:39
*** (1) TRANSACTION:
TRANSACTION 0 389711100, ACTIVE 0 sec, process no 12239, OS thread id 1127369056 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216, 3 row lock(s), undo log entries 2
MySQL thread id 50758, query id 848522554 localhost 127.0.0.1 root Updating
update PLAYER_G set GENERAL_EXP=15007, UPDATE_TIME='2010-12-10 14:57:07' where ID=34958
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 116 page no 305 n bits 264 index `PRIMARY` of table `neice_6`.`player_genera
l` trx id 0 389711100 lock_mode X locks rec but not gap waiting
Record lock, heap no 197 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 8000888e; asc     ;; 1: len 6; hex 0000173a84fd; asc    :  ;; 2: len 7; hex 0000000880011
0; asc        ;; 3: len 4; hex 80000038; asc    8;; 4: len 4; hex 80005bbb; asc   [ ;; 5: len 4; hex 800
0003d; asc    =;; 6: len 4; hex 8000001c; asc     ;; 7: len 4; hex 8000002f; asc    /;; 8: len 4; hex 80
00000e; asc     ;; 9: len 4; hex 80000b3c; asc    <;; 10: len 4; hex 80003a9f; asc   : ;; 11: len 8; hex
 800012482d7cde6f; asc    H-| o;; 12: len 8; hex 800012482d7d83ab; asc    H-}  ;; 13: len 4; hex 800001e
0; asc     ;; 14: len 4; hex 80000000; asc     ;; 15: SQL NULL; 16: len 4; hex 80000001; asc     ;; 17:
len 4; hex 80000000; asc     ;; 18: len 4; hex 8000000c; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 0 389711101, ACTIVE 0 sec, process no 12239, OS thread id 1155668320 starting index read, thread declared inside InnoDB
500
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1216, 5 row lock(s), undo log entries 4
MySQL thread id 50759, query id 848522558 localhost 127.0.0.1 root Updating
update PLAYER_G set GENERAL_EXP=6623, UPDATE_TIME='2010-12-10 14:57:14' where ID=174348
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 116 page no 305 n bits 264 index `PRIMARY` of table `neice_6`.`PLAYER_G` trx id 0 389711101 lock_m
ode X locks rec but not gap
Record lock, heap no 197 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 8000888e; asc     ;; 1: len 6; hex 0000173a84fd; asc    :  ;; 2: len 7; hex 00000008800110; asc        ;; 3: len 4; h
ex 80000038; asc    8;; 4: len 4; hex 80005bbb; asc   [ ;; 5: len 4; hex 8000003d; asc    =;; 6: len 4; hex 8000001c; asc     ;; 7:
len 4; hex 8000002f; asc    /;; 8: len 4; hex 8000000e; asc     ;; 9: len 4; hex 80000b3c; asc    <;; 10: len 4; hex 80003a9f; asc
 : ;; 11: len 8; hex 800012482d7cde6f; asc    H-| o;; 12: len 8; hex 800012482d7d83ab; asc    H-}  ;; 13: len 4; hex 800001e0; asc
   ;; 14: len 4; hex 80000000; asc     ;; 15: SQL NULL; 16: len 4; hex 80000001; asc     ;; 17: len 4; hex 80000000; asc     ;; 18:
len 4; hex 8000000c; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 116 page no 395 n bits 272 index `PRIMARY` of table `neice_6`.`PLAYER_G` trx id 0 389711101 lock_m
ode X locks rec but not gap waiting
Record lock, heap no 147 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 8002a90c; asc     ;; 1: len 6; hex 0000173a84fc; asc    :  ;; 2: len 7; hex 00000000371183; asc     7  ;; 3: len 4; h
ex 800000d7; asc     ;; 4: len 4; hex 80005bbb; asc   [ ;; 5: len 4; hex 8000003a; asc    :;; 6: len 4; hex 80000006; asc     ;; 7:
len 4; hex 80000005; asc     ;; 8: len 4; hex 80000003; asc     ;; 9: len 4; hex 80000d5e; asc    ^;; 10: len 4; hex 8000399a; asc
 9 ;; 11: len 8; hex 800012482d7cde76; asc    H-| v;; 12: len 8; hex 800012482d7d83b2; asc    H-}  ;; 13: len 4; hex 800001e0; asc
   ;; 14: len 4; hex 80000000; asc     ;; 15: SQL NULL; 16: len 4; hex 80000001; asc     ;; 17: len 4; hex 80000000; asc     ;; 18:
len 4; hex 80000008; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

mysql> show create table PLAYER_G\G
*************************** 1. row ***************************
       Table: PLAYER_GENERAL
Create Table: CREATE TABLE `player_general` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `general_id` int(11) DEFAULT NULL,
  `player_id` int(11) DEFAULT NULL,
  `general_level` int(3) DEFAULT '1',
  `leader` int(4) DEFAULT '0',
  `forces` int(4) DEFAULT '0',
  `intelligence` int(4) DEFAULT '0',
  `solider_num` int(11) DEFAULT '0',
  `general_exp` int(11) DEFAULT '0',
  `start_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `train_times` int(8) DEFAULT '0',
  `refresh` int(1) DEFAULT '0',
  `train_model` int(1) DEFAULT NULL,
  `train_flag` int(1) DEFAULT '0',
  `online` int(1) DEFAULT '1',
  `troop_grow_lv` int(5) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_id,general_id,refresh` (`player_id`,`general_id`,`refresh`)
) ENGINE=InnoDB AUTO_INCREMENT=699326 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT
1 row in set (0.00 sec)

How to repeat:
no
[13 Dec 2010 7:52] MySQL Verification Team 
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.