Re: Avoiding a deadlock - Mailing list pgsql-general

From Chris Curvey
Subject Re: Avoiding a deadlock
Date
Msg-id CADfwSsCfjYD=NLKMuO=Fs5rN3MD+3q_c7hjOXkdnHDg+P-O-EQ@mail.gmail.com
Whole thread Raw
In response to Avoiding a deadlock  (Paul Jungwirth <pj@illuminatedcomputing.com>)
Responses Re: Avoiding a deadlock
List pgsql-general
On Sat, Mar 9, 2013 at 4:20 PM, Paul Jungwirth <pj@illuminatedcomputing.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. 

pgsql-general by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: Avoiding a deadlock
Next
From: Alban Hertroys
Date:
Subject: Re: Avoiding a deadlock