Description:
Hi,
Could someone have a look at this deadlock? I do not know if it is really a bug.
Thanks for give any idea.
The deadlock from show innodb status:
------------------------
LATEST DETECTED DEADLOCK
------------------------
110302 18:05:52
*** (1) TRANSACTION:
TRANSACTION 0 8993736, ACTIVE 0 sec, process no 3173, OS thread id 1207671104 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s)
MySQL thread id 161906, query id 129228708 sha-wks-aa844.ubisoft.org 10.192.81.170 ssopcuatn update
insert into roomidmapping (lineid, rid, gid) values (3, 101, 773)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5493 n bits 200 index `PRIMARY` of table `sso_pc_uat_n`.`roomidmapping` trx id 0 8993736 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 0 8993737, ACTIVE 0 sec, process no 3173, OS thread id 1217788224 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216, 2 row lock(s)
MySQL thread id 161905, query id 129228709 sha-wks-aa844.ubisoft.org 10.192.81.170 ssopcuatn update
insert into roomidmapping (lineid, rid, gid) values (3, 101, 772)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5493 n bits 200 index `PRIMARY` of table `sso_pc_uat_n`.`roomidmapping` trx id 0 8993737 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;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5493 n bits 200 index `PRIMARY` of table `sso_pc_uat_n`.`roomidmapping` trx id 0 8993737 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
The table:
show create table roomidmapping;
-------------------------------------------------------------------------------+
| roomidmapping | CREATE TABLE `roomidmapping` (
`lineid` int(11) NOT NULL,
`rid` int(11) NOT NULL,
`gid` int(11) DEFAULT NULL,
PRIMARY KEY (`rid`,`lineid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------+---------------------------------------------------------------
The transaction level:
tx_isolation | REPEATABLE-READ
Thanks
How to repeat:
It seems only happened when lots of concurrent inserts/select on the table.
