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
--
_________________________________
Pulchritudo splendor veritatis.
