MySQL Forums
Forum List  »  General

Re: Help for fixing update failure due to lock in a outer join table
Posted by: Peter Brawley
Date: June 02, 2020 10:15AM

update table_a as wk
left join table_b as b1 on b1.cd1 = in_cd1 and b1.cd2 = in_cd2 and b1.div = '1'
left join table_b as b2 on b2.cd1 = in_cd1 and b2.cd2 = in_cd2 and b2.div = '2'
set wk.name1 = , wk.name2 =
where = in_id;

First, in busy nontrivial systems with multiple users, deadlocks are pretty much inevitable, so at the very least, transactions need to be written such that they can be restarted when deadlocks happen.

And, the smaller the transaction, the less likely a deadlock is. How many rows does this update query typically target? Did you try splitting this into 2 update statements?

Likewise the more optimised the SQL, the less likely is a deadlock ... have you rn Explain on this update?

Then, why Left Joins? They don't optimise well, and non-matches will write nulls. Can't the relational setup be tightened enough to support Inner Joins?

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.