CodeSOD: Query Lockup

Another day, another time where someone from Brian's team needs to log into their MySQL database and kill a query. This particular query hangs while holding a lock, which hangs up every other query which needs to touch this table, which is a lot of them.

select count(*) INTO @fullCountFROM SALLSTDM LEFT OUTER JOIN BUYBIDMB ON (MBBUYNBR = 597436 AND MBLOTNBR = SLLOTNBR)INNER JOIN LOTFILFL FL1 ON (FL1.FLFILTYP = 'A1' AND FL1.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL2 ON (FL2.FLFILTYP = 'A1' AND FL2.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL3 ON (FL3.FLFILTYP = 'A1' AND FL3.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL4 ON (FL4.FLFILTYP = 'A1' AND FL4.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL5 ON (FL5.FLFILTYP = 'A1' AND FL5.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL6 ON (FL6.FLFILTYP = 'A1' AND FL6.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL7 ON (FL7.FLFILTYP = 'A1' AND FL7.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL8 ON (FL8.FLFILTYP = 'A1' AND FL8.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL9 ON (FL9.FLFILTYP = 'A1' AND FL9.FLLOTNBR = SALLSTDM.SLLOTNBR )INNER JOIN LOTFILFL FL10 ON (FL10.FLFILTYP = 'A1' AND FL10.FLLOTNBR = SALLSTDM.SLLOTNBR )WHERE 1=1 AND SALLSTDM.SLYRDNBR not in(450,451,452)

Now, I actually think the WTF is the code that generates this query, but Brian didn't send us that. Maybe Brian doesn't even know where that is. But for some reason, this query joins back to the LOTFILFL table ten times. It's the same condition, and it's inner joins, so this isn't going to affect the count(*) of the table.

In an ideal world, someone would track down the source of the query, drag it out behind the barn and be done with it. But we don't live in that world- instead, somebody from Brian's database team gets called outside of regular hours every time this query runs. Sometimes that's once a week. Sometimes that's three or four times a day. No one is empowered to fix it or address the root cause, so it just sits there, festering.

[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!