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 bar_id 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 (bar_id) to no avail.
Is there something better I can do here? I’d at least like to understand what the problem is. Thanks!
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!