Middelen om te UPDATEN WAAR Waarde is IN Subquery die GROEP BY heeft, dus geen probleem met rasconditie?

Misschien is het mijn naïviteit, misschien mijn paranoia, maar ik denk dat ik op zoek ben naar een oplossing voor een kwestie van een race-conditie die lijkt dat het zo gewoon is dat er een stortvloed aan oplossingen zou zijn en ik zou er nu een hebben gevonden ... maar dat is niet zo.

Het simplistische scenario is dat ik een proces heb dat verondersteld wordt alle records te pakken waar er meer dan één van een bepaald type is. Ik wil het systeem/proces (sen) thread-/multiprocessing-/reentrant-/buzzword-van-de-dag-veilig maken; als hetzelfde proces wordt gestart en een raceconditie wordt geïntroduceerd die probeert interessante rijen te pakken, zou ik willen dat er duidelijke winnaars/verliezers zijn: succes voor één, fout voor de ander; eigenlijk zou ik voor de tweede een naadloze, stille, sierlijke "mislukking" willen, in die zin dat het alleen NIET ZIE de dingen die door de eerste instantie zouden zijn gepakt.

Dus mijn dilemma.

De vraag die ik heb is als volgt:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(trans_nbr) > 1
                           LIMIT our_limit_to_have_single_process_grab
                       )
RETURNING row_id

Mijn gedachte is: ik zou denken dat er geen vergrendeling is, dus geen garantie van "staat" tussen de subquery en de uiterlijke update. Hoe kunnen we ervoor zorgen dat het DIT proces van kandidaten wordt, en dat ze ondertussen niet door een ander proces zijn gepakt?

Ik heb nagedacht over het toevoegen van een " VOOR UPDATE OP my_table "aan het einde van de subquery, maar dat zal niet werken; kan dit niet krijgen EN een "GROEP VOOR" (wat nodig is voor het berekenen van het aantal trans_nbr's). (Omdat dit ook zou afdwingen dat alle ook-rans worden geblokkeerd in afwachting van onze update, zou dit een geprefereerde oplossing zijn geweest, omdat dan een race-voorwaarde-veroorzaakte fout zou worden voorkomen [twee processen die dezelfde rij {s}] grijpen en toestaan die andere processen zijn zalig onverwachts en krijgen alleen maar rijen die niet langer degene zijn die het eerste proces hebben gekregen. Helaas)

Ik heb nagedacht over het vergrendelen van de tafel, maar (in Postgres, tenminste) worden tafelsloten alleen vrijgegeven na een COMMIT; voor testdoeleinden wil ik niet COMMIT, daarom tijdens het testen (ja, de pre-live testen in de live database NA testen op een test db) zou het niet doen om deze route te gaan. (Plus, zelfs live, dit zou een onaanvaardbare prestatieshit geven, gezien genoeg gebruikers/processen.)

Ik heb nagedacht over het maken van de update afhankelijk van wat de waarde van processing_by was voor onze subquery, maar nogmaals, dat werkt niet: als in de subquery de voorwaarde GROUP BY/HAVING zou verbreken ( zoals nu zouden subgroepen van trans_nbr/processing_by worden geteld, wat niet is wat ik zoek.

Ik verwacht een schril punt in de goede richting dat ik spot met het stellen van een dergelijke voor de hand liggende vraag, maar het is mij niet duidelijk (duidelijk; o) en ik verzeker je, ik heb dit letterlijk uren onderzocht.

Bedankt voor alle tips, laat staan ​​voor oplossingen!


UPDATE: Thanks SO MUCH Chris Travers!

That ol' line about "Forrest for the Trees" comes to mind! :>

Hier is een aangepaste versie van de query, rekening houdend met deze suggestie, en nog een "dubbele controle" toe te voegen. Deze zou DE ENE moeten zijn.

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                SELECT trans_nbr
                  FROM my_table
                 WHERE trans_nbr IN (
                           SELECT trans_nbr
                             FROM my_table
                         GROUP BY trans_nbr
                           HAVING COUNT(*) > 1 -- Thanks for the suggestion, Flimzy
                            LIMIT our_limit_to_have_single_process_grab
                                    )
                   AND processing_by IS NULL
                       /* Or some other logic that says "not currently being
                          processed".  This way, we ALSO verify we're not
                          grabbing one that might have been UPDATEd/grabbed
                          during our sub-SELECT, while it was being
                          blocked/waiting.

                          This COULD go in our UPDATE/top-level, but unnecessary
                          rows could be locked by this lower-level in that case.
                       */
            FOR UPDATE /* Will block/wait for rows this finds to be unlocked by
                          any prior transaction that had a lock on them.

                          NOTE: Which _could_ allow the prior trans to change
                                our desired rows in the mean time, thus the
                                secondary WHERE clause.
                       */
                       )
RETURNING row_id

Ik zou graag zien dat Postgres een SKIP LOCKED -achtige functie heeft. Vooral voor wachtrijen van in wezen atomaire rijen die moeten worden verwerkt zonder andere verwerking te blokkeren. Maar helaas. Misschien ooit ...? < a href = "http://www.postgresql.org/message-id/[email protected]om" rel = "nofollow noreferrer"> Of "binnenkort"? :-)

Voor nu kan iemand NOWAIT om NIET te worden geblokkeerd door een andere transactie (s), houd er echter rekening mee, het dumpt alleen maar met een fout - je zult je vraag moeten blijven proberen totdat het lukt (of opgeeft). Zonder NOWAIT blokkeert de query totdat andere transacties hun vergrendelingen vrijgeven of de time-out van de zoekopdracht wordt bereikt.


UPDATE 2: SO, after re-re-re-reading this and thinking about it, again "Forrest for the Trees" moment. I can simply do like this:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                        -- This query MAY pull ones we don't want to mess with (already "grabbed")
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(*) > 1
                           LIMIT our_limit_to_have_single_process_grab
                             AND processing_by IS NULL -- only "ungrabbed" ones (at this point)
                       )
      AND processing_by IS NULL -- But THIS will drop out any "bogus" ones that changed between subquery and here
RETURNING row_id

COMMIT de transactie om ONZE sluizen vrij te geven, en Bob's yer oom.

SKIP LOCKED zou echter nog steeds supercool zijn.

A CAVEATE: If one was to have workers pulling a limited (like LIMIT 1) number of rows and/or items must be grabbed in a certain order (e.g.: FIFO, either ORDER BY and/or by function like Min(id)), there can be cases of starved workers: a worker waits and waits, and when the row(s) they were waiting for unblocks, turns out none of them meet its final criteria. There are a number of ways to try to get around this, like having workers jumping around via OFFSET, but most are either complex or slow. (Usually both. BONUS!)

MY functionailty expects multiple rows returned, or none is A-OK - nothing to do for now; sleep for a bit and recheck, so this isn't a problem for me. It may be for you. If so, you'll want to consider a...

NON-BLOCKING VERSION: I found a great article working with this very problem, turns out, and it introduced me to Pg's Advisory Locks. (This one was quite informative, too.)

Dus een niet-blokkerende oplossing voor mijn eigen probleem zou er als volgt uit moeten zien:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
            -- This query MAY pull ones we don't want to mess with (already "grabbed")
              SELECT trans_nbr
                FROM my_table AS inner_my_table_1
            GROUP BY trans_nbr
              HAVING Count(*) > 1
                 AND Count(*) in ( -- For MY query, since I'm grouping-by, I want "all or none" of trans_nbr rows
                       SELECT Count(*)
                         FROM my_table AS inner_my_table_2
                        WHERE inner_my_table_2.trans_nbr = inner_my_table_1.trans_nbr
                          AND pg_try_advisory_xact_lock(id) -- INT that will uniquely ID this row
                                 )
/* Note also that this will still lock all non-locked rows with this
   trans_nbr, even though we won't use them unless we can grab ALL of the
   rows with same trans_nbr... the rest of our query should be made
   quick-enough to accept this reality and not tie up the server unduly.

   See linked info for more-simple queries not doing group-by's.
*/
               LIMIT our_limit_to_have_single_process_grab
                 AND processing_by IS NULL -- only "ungrabbed" ones (at this point)
                       )
      AND processing_by IS NULL -- But THIS will drop out any "bogus" ones that changed between subquery and here
RETURNING row_id

OPMERKINGEN:

  • It's up to the applications to do/respect Advisory Locks, so this is no pancea, but nor is it a placebo. Again, SKIP LOCKED would be very handy because of this.
  • pg_try_advisory_lock, since v 8.2, does not auto-unlock, (thus) may (MUST) be explicitly unlocked
  • pg_try_advisory_xact_lock, since v 9.1, auto-unlocks at end of transaction, may NOT be explicitly unlocked
  • I HAVE NOT TESTED THIS YET! I'll edit/update when I have...
1
Minorpunt: Tenzij trans_nbr soms NULL is, zult u waarschijnlijk betere resultaten krijgen met COUNT (*) versus COUNT (trans_nbr) , omdat de eerste volgens mij beter kan worden geoptimaliseerd .
toegevoegd de auteur Flimzy, de bron
Heb je erover nagedacht om dit in twee SQL-sessies te testen?
toegevoegd de auteur Mike Sherrill 'Cat Recall&, de bron
@ MikeSherrill'Catcall ': Ja, ik had erover nagedacht. Begonnen met het opbouwen van een aantal testgegevens/query's, maar werden gebeld voor andere taken. (Ik ben dit nu aan het herzien omdat iemand zo vriendelijk was om een ​​oplossing voor te stellen. :-) 1. Logisch gezien is het probleem dat ik noem "theoretisch mogelijk", probleem-gezien of niet. 2. Zelfs met testen zijn dit soort concurrency-problemen moeilijk te repliceren. Dus, "het daadwerkelijk zien gebeuren" is misschien niet altijd mogelijk. En zonder gedetailleerde kennis van de bron ... (Ja, FLOSS, maar dat type code is moeilijk [voor mij;] om door te dringen ... vooral als ik er 24/7 niet aan werk
toegevoegd de auteur pythonlarry, de bron
@Flimzy: Ik heb je al lang geleden elders rekwisieten gegeven, maar wilde hier expliciet expliceren: BEDANKT dat je dat hebt opgemerkt! >
toegevoegd de auteur pythonlarry, de bron

1 antwoord

Wat dacht je van een extra subquery-laag voor de vergrendeling?

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this instance
    WHERE trans_nbr IN (
                    SELECT trans_nbr
                      FROM my_table
                     WHERE trans_nbr IN (
                                 SELECT trans_nbr
                                   FROM my_table
                               GROUP BY trans_nbr
                                 HAVING COUNT(trans_nbr) > 1
                                  LIMIT our_limit_to_have_single_process_grab
                                 )
                        FOR UPDATE
                       )
RETURNING row_id
1
toegevoegd