Re: Avoiding a deadlock

On Sat, Mar 9, 2013 at 4:20 PM, Paul Jungwirth
<pj(at)illuminatedcomputing(dot)com>wrote:

> I have a long-running multi-row UPDATE that is deadlocking with a
> single-row UPDATE:
>
> 2013-03-09 11:07:51 CST ERROR: deadlock detected
> 2013-03-09 11:07:51 CST DETAIL: Process 18851 waits for ShareLock on
> transaction 10307138; blocked by process 24203.
> Process 24203 waits for ShareLock on transaction 10306996; blocked
> by process 18851.
> Process 18851: UPDATE taggings tg
> SET score_tier = COALESCE(x.perc, 0)
> FROM (SELECT tg2.id,
> percent_rank() OVER (PARTITION BY
> tg2.tag_id ORDER BY tg2.score ASC) AS perc
> FROM taggings tg2, tags t
> WHERE tg2.score IS NOT NULL
> AND tg2.tag_id = t.id
> AND t.tier >= 2) AS x
> WHERE tg.id = x.id
> AND tg.score IS NOT NULL
> ;
> Process 24203: UPDATE "taggings" SET "score" = 2 WHERE
> "taggings"."id" = 29105523
>
> Note that these two queries are actually updating different columns,
> albeit apparently in the same row.
>
> Is there anything I can do to avoid a deadlock here? The big query does
> nothing else in its transaction; the little query's transaction might
> update several rows from `taggings`, which I guess is the real reason for
> the deadlock.
>
> I'd be pretty satisfied with approximate values for the big query. As you
> can see, it is just taking the `score` of each `tagging` and computing the
> percentage of times it beats other taggings of the same tag. Is there
> something I can do with transaction isolation levels here? I don't care if
> the big query operates on slightly-out-of-date values. Since each query
> updates different columns, I think there should be no issue with them
> overwriting each other, right?
>
> Thanks,
> Paul
>
>
it *might* help to do the calculation work (all those nested SELECTs) and
store the results in a temporary table, then do the update as a second,
simpler join to the temp table.