MySQL Forums
Forum List  »  InnoDB

Deadlock on Delete and Insert
Posted by: John Larsen
Date: February 15, 2005 12:20AM

Hi

I'm having trouble with deadlocks when I delete and insert into two tables from multiple client threads (see below).

The client code first performs a delete from the charge_event table (but in the test case nothing is deleted). Then a record is inserted into the call_detail_record table. Then one or more records are inserted into the charge_event table, each of the charge_events references the just inserted call_detail_record.

The deadlock always seems to be on the charge_event table between the follow_on_index foreign key and the auto-inc table lock. However I find if I don't do the delete on the charge_event table at the start of the transaction then the deadlock doesn't occur. Can anyone explain why this might happen and whether there is a solution that doesn't involve table locks (which are impractical in this case)???

thanks!

------------------------
LATEST DETECTED DEADLOCK
------------------------
050215 15:40:07
*** (1) TRANSACTION:
TRANSACTION 0 1133651, ACTIVE 2 sec, OS thread id 98263 inserting
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 3024, undo log entries 6
MySQL thread id 98254, query id 7796940 192.168.168.200 sonar update
REPLACE INTO charge_event SET call_id='200502151539340002', endpoint='0', follow_on_index=1, active_times_id=4, destination_id=665, subscription_id=1553, accounting_entry_id=20429, amount=0.5650
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 35350 n bits 280 index `follow_on_index` of table `memory_leak_test_fix/charge_event` trx id 0 1133651 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 1; hex 31; asc 1;; 2: len 19; hex 32303034313231363130353331363031343539; asc 2004121610531601459;; 3: len 8; hex 0000000000000092; asc ;;

*** (2) TRANSACTION:
TRANSACTION 0 1133648, ACTIVE 3 sec, OS thread id 98235 setting auto-inc lock, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
16 lock struct(s), heap size 3024, undo log entries 5
MySQL thread id 98226, query id 7796980 192.168.168.200 sonar update
REPLACE INTO charge_event SET call_id='200502151539330004', endpoint='0', follow_on_index=1, active_times_id=4, destination_id=719, subscription_id=10398, accounting_entry_id=null, amount=5.0509264980
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 35350 n bits 272 index `follow_on_index` of table `memory_leak_test_fix/charge_event` trx id 0 1133648 lock_mode X locks gap before rec
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 1; hex 31; asc 1;; 2: len 19; hex 32303034313231363130353331363031343539; asc 2004121610531601459;; 3: len 8; hex 0000000000000092; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `memory_leak_test_fix/charge_event` trx id 0 1133648 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)


CREATE TABLE charge_event (
charge_event_id bigint unsigned NOT NULL auto_increment,
call_id varchar(64) NOT NULL,
endpoint varchar(128) NOT NULL,
follow_on_index int(5) unsigned default '0',
active_times_id int unsigned NOT NULL,
destination_id int unsigned NOT NULL,
subscription_id int unsigned default NULL,
accounting_entry_id bigint unsigned default NULL,
amount decimal(12,4) default '0.0000',
timestamp timestamp NOT NULL,
PRIMARY KEY (charge_event_id, follow_on_index, endpoint, call_id),
FOREIGN KEY (follow_on_index, endpoint, call_id) REFERENCES call_detail_record (follow_on_index, endpoint, call_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (active_times_id) REFERENCES active_times (active_times_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (destination_id) REFERENCES destination (destination_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (subscription_id) REFERENCES subscription (subscription_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (accounting_entry_id) REFERENCES accounting_entry (accounting_entry_id) ON DELETE RESTRICT ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1 ;

CREATE TABLE call_detail_record (
call_id varchar(64) NOT NULL,
endpoint varchar(128) NOT NULL,
follow_on_index int(5) unsigned default '0',
this_resource_group_id int unsigned NOT NULL,
other_resource_group_id int unsigned default NULL,
direction enum('in', 'out') NOT NULL,
completion_code_id tinyint unsigned NOT NULL,
PRIMARY KEY (follow_on_index, endpoint, call_id),
KEY call_id (call_id),
FOREIGN KEY (this_resource_group_id) REFERENCES resource_group (resource_group_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (other_resource_group_id) REFERENCES resource_group (resource_group_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (completion_code_id) REFERENCES completion_code (completion_code_id) ON DELETE RESTRICT ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlock on Delete and Insert
5959
February 15, 2005 12:20AM


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.