Strange deadlock
Posted by:
Percy Xu
Date: January 10, 2013 07:28PM
When I check the innodb status. I found the following deadlock.
------------------------
LATEST DETECTED DEADLOCK
------------------------
130110 16:06:51
*** (1) TRANSACTION:
TRANSACTION A55BAD3, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 31134773, OS thread handle 0x2e17c, query id 570526948 localhost 127.0.0.1 root update
insert into bet_statistic_1_20130110086(sub_type_id,number,amount) values(6,''X85'',5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 114742 n bits 304 index `PRIMARY` of table `7_stars_speed_3`.`bet_statistic_1_20130110086` trx id A55BAD3 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 29 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 1; hex 07; asc ;;
1: len 3; hex 303233; asc 023;;
2: len 6; hex 00000a55b3b5; asc U ;;
3: len 7; hex 630001c013141d; asc c ;;
4: len 8; hex 0000000000001040; asc @;;
*** (2) TRANSACTION:
TRANSACTION A55BAD4, ACTIVE 0 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 31124213, OS thread handle 0x336b0, query id 570526949 localhost 127.0.0.1 root update
insert into bet_statistic_1_20130110086(sub_type_id,number,amount) values(4,''96X'',1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 114742 n bits 304 index `PRIMARY` of table `7_stars_speed_3`.`bet_statistic_1_20130110086` trx id A55BAD4 lock_mode X locks gap before rec
Record lock, heap no 29 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 1; hex 07; asc ;;
1: len 3; hex 303233; asc 023;;
2: len 6; hex 00000a55b3b5; asc U ;;
3: len 7; hex 630001c013141d; asc c ;;
4: len 8; hex 0000000000001040; asc @;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 114742 n bits 304 index `PRIMARY` of table `7_stars_speed_3`.`bet_statistic_1_20130110086` trx id A55BAD4 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 29 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 1; hex 07; asc ;;
1: len 3; hex 303233; asc 023;;
2: len 6; hex 00000a55b3b5; asc U ;;
3: len 7; hex 630001c013141d; asc c ;;
4: len 8; hex 0000000000001040; asc @;;
*** WE ROLL BACK TRANSACTION (2)
I said strange because the 2 statements are inserting with differents primary key values. But they seems to waiting for a same lock. What's wrong?