How to resolve deadlock caused during concurrent inserts into one table

[Borislav Andruschuk]

Hello, guys,

Could you please help me? I'm getting the error mentioned below during two simultaneous transactions very often. Does this deadlock relate to attempt for X lock from two concurrent threads in InnoDB. May be you can give me an advice how I can solve this problem. I do not want to synchronize database access in this place to avoid reducing of concurrency in my application. How can I perform concurrent inserts into one table?

100921 16:15:46
*** (1) TRANSACTION:
TRANSACTION 0 226576089, ACTIVE 1 sec, process no 25454, OS thread id 140552698898192 inserting
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 4 row lock(s), undo log entries 1
MySQL thread id 9097, query id 370707544 inc 192.168.1.121 slava update
insert into PROCESS_MESSAGE_HEADERS (ID, HEADER_NAME, HEADER_VALUE) values (1015723, 'ProcessSignal', '*.start')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 822853 page no 20 n bits 288 index `PRIMARY` of table `dev`.`PROCESS_MESSAGE_HEADERS` trx id 0 226576089 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 226576090, ACTIVE 1 sec, process no 25454, OS thread id 140552331536144 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1216, 4 row lock(s), undo log entries 1
MySQL thread id 9098, query id 370707545 inc 192.168.1.121 slava update
insert into PROCESS_MESSAGE_HEADERS (ID, HEADER_NAME, HEADER_VALUE) values (1015722, 'ProcessSignal', '*.start')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 822853 page no 20 n bits 288 index `PRIMARY` of table `dev`.`PROCESS_MESSAGE_HEADERS` trx id 0 226576090 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 822853 page no 20 n bits 288 index `PRIMARY` of table `dev`.`PROCESS_MESSAGE_HEADERS` trx id 0 226576090 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)

Table description is:

CREATE TABLE `PROCESS_MESSAGE_HEADERS` (
`ID` bigint(20) NOT NULL,
`HEADER_NAME` varchar(32) NOT NULL,
`HEADER_VALUE` varchar(256) DEFAULT NULL,
PRIMARY KEY (`ID`,`HEADER_NAME`),
CONSTRAINT `FK_HEADERS_PROCESS_MESSAGE` FOREIGN KEY (`ID`) REFERENCES `PROCESS_MESSAGE` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8



Edited 1 time(s). Last edit at 09/22/2010 08:50AM by Borislav Andruschuk.