Search Postgresql Archives

Re: Avoiding a deadlock

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11 March 2013 13:01, Chris Curvey <chris@xxxxxxxxxxxxxxx> wrote:
On Sat, Mar 9, 2013 at 4:20 PM, Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> 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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux