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;
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`)
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.