On Sat, Mar 9, 2013 at 4:20 PM, Paul Jungwirth <p...@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.
