Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, when the table with the INSERT trigger receives a lot of inserts in a short period of time, it deadlocks.
All tables involved are using InnoDB. I used show engine status and got this report for the deadlock:
2015-01-26 19:52:53 7f7c581c6700
*** (1) TRANSACTION:
TRANSACTION 570934008, ACTIVE 0 sec inserting
mysql tables in use 9, locked 9
LOCK WAIT 14 lock struct(s), heap size 2936, 8 row lock(s), undo log entries 2
MySQL thread id 7906450, OS thread handle 0x7f7c5840f700, query id 483363132 ip-172-31-19-95.ec2.internal 172.31.19.95 root Sending data
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 477 page no 499185 n bits 1000 index `prf_concat_profiles_original_id_index` of table `cerebro`.`prf_concat_profiles` trx id 570934008 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 570934009, ACTIVE 0 sec inserting
mysql tables in use 9, locked 9
14 lock struct(s), heap size 2936, 8 row lock(s), undo log entries 2
MySQL thread id 7906451, OS thread handle 0x7f7c581c6700, query id 483363135 [rem] [rem] [rem] Sending data
INSERT INTO prf_concat_profiles
SELECT DISTINCT
prf_profiles.id,
prf_profiles.id,
prf_profiles.updated,
prf_names.title,
prf_names.`first`,
prf_names.middle,
prf_names.`last`,
prf_names.suffix,
prf_single_attributes.voter_id,
prf_phone_numbers.number,
prf_email_addresses.email,
prf_names.src_sources_id as name_src,
prf_single_attributes.src_sources_id as attr_src,
prf_phone_numbers.src_sources_id as phone_src,
prf_email_addresses.src_sources_id as email_src
FROM (
prf_profiles
LEFT JOIN prf_names ON prf_prof
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 477 page no 499185 n bits 1000 index `prf_concat_profiles_original_id_index` of table `cerebro`.`prf_concat_profiles` trx id 570934009 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 477 page no 499185 n bits 1000 index `prf_concat_profiles_original_id_index` of table `cerebro`.`prf_concat_profiles` trx id 570934009 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
The thing that seems off is that it seems like transaction (2) is waiting for a lock it's already holding, but maybe I'm misreading the log.
Below is the INSERT trigger:
BEGIN
DELETE FROM prf_concat_profiles WHERE original_id = NEW.id;
INSERT INTO prf_concat_profiles
SELECT DISTINCT
prf_profiles.id,
prf_profiles.id,
prf_profiles.updated,
prf_names.title,
prf_names.`first`,
prf_names.middle,
prf_names.`last`,
prf_names.suffix,
prf_single_attributes.voter_id,
prf_phone_numbers.number,
prf_email_addresses.email,
prf_names.src_sources_id as name_src,
prf_single_attributes.src_sources_id as attr_src,
prf_phone_numbers.src_sources_id as phone_src,
prf_email_addresses.src_sources_id as email_src
FROM (
prf_profiles
LEFT JOIN prf_names ON prf_profiles.id = prf_names.prf_profiles_id
LEFT JOIN prf_single_attributes ON prf_profiles.id = prf_single_attributes.prf_profiles_id
LEFT JOIN prf_phone_numbers ON prf_profiles.id = prf_phone_numbers.prf_profiles_id
LEFT JOIN prf_email_addresses ON prf_profiles.id = prf_email_addresses.prf_profiles_id
)
WHERE prf_profiles.id = NEW.id;
UPDATE
prf_concat_profiles
INNER JOIN prf_profile_matches ON id = prf_profiles_id_2
SET id = prf_profiles_id_1
WHERE prf_profiles_id_2 = NEW.id;
END
Any help is much appreciated and anything I can do to provide more information, I'll gladly follow up.
after insert on prf_profiles
. I also assume that prf_profiles.id is unique. Then why do you select from prf_profiles in the trigger?NEW
somehow?FROM ( SELECT NEW.id as ID ) as X LEFT JOIN prf_names ON X.id = prf_names.prf_profiles_id ...
In the select clause you can use NEW.updated or include that attribute in the derived table, and use that.