> 2) All transactions modify table rows in the same order, e.g. ascending "id".
> With the big update you can do that by putting an "ORDER BY
tg2.id" into
> the subquery, and with the "little transactions" you'll have to make sure
> that rows are updated in ascending "id" order.
I agree this would fix the deadlock. It also seems like the least disruptive way of fixing the problem.
Out of curiosity: any reason the ORDER BY should be in the subquery? It seems like it ought to be in the UPDATE (if that's allowed).
Thanks,
Paul