Mysql – Deadlock caused by INSERT trigger – thread looking for lock it already has

deadlockMySQLmysql-5.6trigger

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.

Best Answer

I guess it's a combination of MySQL not being very smart and your less-than-optimal application design.

You have two concurrent transactions trying to access the same block:

*** (1) TRANSACTION:
TRANSACTION 570934008, ACTIVE 0 sec inserting
...
*** (2) TRANSACTION:
TRANSACTION 570934009, ACTIVE 0 sec inserting

Your trigger makes them both attempting to insert into prf_concat_profiles for which they lock index pages:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 477 page no 499185 n bits 1000 index 
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 477 page no 499185 n bits 1000 index

Transaction 2 wants to lock another index entry on the same page

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 477 page no 499185 n bits 1000 index

I guess what happens next is MySQL sees transaction 1 also accessing the same page, so it detects the deadlock between 1 and 2, not 2 and 2.

Related Question