1

I have these jobs that run in parallel that call a series of queries on my DB. They're batched so each job is associated with a group of "bar" objects. So in the example below, temp1 is just a temporary table like:

CREATE TEMPORARY TABLE temp1 (id int(11) NOT NULL, PRIMARY KEY (id))

The problem I'm having is queries like this are deadlocking:

explain update
    foo
join
    temp1 on foo.bar_id = temp1.id
set
    foo.a = null;

Gives the output (please excuse the formatting):

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   UPDATE  foo NULL    ALL foo_60254a58    NULL    NULL    NULL    222939  100.00  Using where
1   SIMPLE  temp1   NULL    eq_ref  PRIMARY PRIMARY 4   db.foo.bar_id   1   100.00  Using index

So as I understand it, this will lock every row while updating because of that second line. As a sort of band aid, if I put the IDs to update in a temp table everything works fine and the deadlocks stop:

create temporary table temp2 as
(
    select
        foo.id
    from
        foo join temp1 on foo.bar_id = temp1.id
);

explain update
    foo
join
    temp2 on foo.id = temp2.id
set
    foo.a = null;

Gives:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE temp2    NULL    ALL NULL    NULL    NULL    NULL    1135    100.00  NULL
1   UPDATE  foo NULL    eq_ref  PRIMARY PRIMARY 4   db.temp2.id 1   100.00  NULL

So not only does this fix my problem in practice, that result also looks a lot better.

This is where my MySQL knowledge fails me. I don't understand why the first query is doing that full scan. I thought it was because it's not using an index but I tried stuff like force index for join (foo_60254a58) to no avail.

Is there something better I can do here? I'd at least like to understand what the problem is. Thanks!

Update: foo has 222,939 rows (seen in EXPLAIN output above). Temp table has 5000 rows. bar has ~483,000 rows.

CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bar_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `foo_60254a58` (`bar_id`), CONSTRAINT `foo_60254a58` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`) );

UPDATE 2:

So I was able to fix this by doing two things. First changing the first query to:

explain update
    temp1
straight_join
    foo on foo.bar_id = temp1.id
set
    foo.a = null;

And second, I had to recreate the bar_id index. For some reason it was stuck at cardinality 1 and wasn't doing anything. This part I do not understand.

  • How many rows in each table? And please provide SHOW CREATE TABLE foo. – Rick James Dec 9 '17 at 6:26
  • Updated with that info at the bottom. – user910210 Dec 10 '17 at 18:15
  • Suggest filing a bug report with bugs.mysql.com – Rick James Dec 10 '17 at 18:35
  • I have not been able to reproduce your case, but note that there is a difference between starting with temp1 vs temp2. With temp1, one will use a secondary index on foo. With temp2, the primary key can be used. Hence, the cost is not the same. You can force the join order you want with "... from temp1 straight_join foo on ..." – oysteing Dec 11 '17 at 9:22
  • Another question: Does the same happen if temp1 is an ordinary table and not a temporary table? I think there is differences in how InnoDB maintains statistics in those two cases. – oysteing Dec 11 '17 at 9:23

Your Answer

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

Browse other questions tagged or ask your own question.