Re: Avoiding a deadlock

On 11 March 2013 13:01, Chris Curvey <chris(at)chriscurvey(dot)com> wrote:

> 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.
>
>
All the suggestions thus far only reduce the window in which a dead lock
can occur.

If you really need to prevent that, you can split off the columns for one
of the two types of updates into a separate table with a foreign key to the
original table.
That way your updates happen in different tables and there's no chance on a
deadlock between the two types of queries.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.