Re: Avoiding a deadlock - Mailing list pgsql-general

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