0

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.

  • 1
    As I understand your question you have a trigger like: 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? – Lennart Jan 26 '15 at 20:32
  • Your assumptions are correct. The table is repeated in the trigger so that we can join on it. Can I use NEW somehow? – George Yates Jan 26 '15 at 20:41
  • One idea is to: 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. – Lennart Jan 26 '15 at 20:46
  • The above suggestion may or may not reduce lock contention, but it might be worth a shot. – Lennart Jan 26 '15 at 20:48
  • Did you ever get this resolved ??? – RolandoMySQLDBA Mar 30 '15 at 14:31
1

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.

  • How would you recommend updating my application design to prevent this problem? – George Yates Jan 26 '15 at 20:48
  • It's hard to say without knowing details of the application, but ultimately you'll have to make sure different threads don't compete for the update of the same index (and may be data) pages. Some of the options I can think of: partition the table prf_concat_profiles on profile_id and use local indexes; randomize profile_id values so that they land in different index pages; instead of a trigger run a single asynchronous process that updates prf_concat_profiles if you can afford somewhat stale data there. – mustaccio Jan 26 '15 at 21:04
  • The prf_concat_profiles table is only ever written to by the trigger. Otherwise it's just read from. Does this change anything? If you note, in the trigger it doesn't create a single transaction for an insert and an update statement. Could that be the problem? – George Yates Jan 26 '15 at 21:17
  • 1
    Table is written by the trigger, which is triggered by an insert. If two sessions insert concurrently, you have a problem. All statements in a trigger run in the same transaction as the statement that triggers it. – mustaccio Jan 26 '15 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.