On 11 March 2013 13:01, Chris Curvey <ch...@chriscurvey.com> wrote: > 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. > > 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.
