Search Postgresql Archives

Re: Avoiding a deadlock

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

 



Paul Jungwirth 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?

The problem is that both updates affect the same rows.
It does not matter if they update different columns, since in any
case a new row version is created (read about PostgreSQL's MVCC
implementation in the documentation).

I can only think of two ways to avoid this deadlock:

1) Each of the "little transactions" modifies no more than one row of the table.

2) All transactions modify table rows in the same order, e.g. ascending "id".
   With the big update you can do that by putting an "ORDER BY tg2.id" into
   the subquery, and with the "little transactions" you'll have to make sure
   that rows are updated in ascending "id" order.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[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