Re: Help for fixing update failure due to lock in a outer join table

[Peter Brawley]

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 = b1.name , wk.name2 = b2.name
where wk.id = 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?