MySQL Forums
Forum List  »  InnoDB

How to resolve deadlock caused during concurrent inserts into one table
Posted by: Borislav Andruschuk
Date: September 21, 2010 08:23AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to resolve deadlock caused during concurrent inserts into one table
16004
September 21, 2010 08:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.