Antwoord.

Geschiedenis van de vraag:

Bulk bijwerken van gegevens is vereist bij overgangen, migraties en het corrigeren van bedrijfslogica. Een typisch voorbeeld: de status van tientallen miljoenen rijen in een actieve tabel wijzigen zonder de service te stoppen, met behoud van beschikbaarheid en prestaties.

Probleem:

Een gewone UPDATE zonder beperking duurt lang, kan leiden tot escalatie van blokkeringen, blokkeert de tabel en leidt tot een collectieve rollback bij een fout. Er is een aanpak nodig die de impact op gebruikers minimaliseert en transactionele integriteit waarborgt.

Oplossing:

Deel de operatie op in batches met behulp van WHERE en LIMIT/TOP.
Gebruik vensterfuncties, tijdelijke tabellen, tijdelijke markeringen.
Soms - tijdelijk indices verwijderen, punten van opslag (SAVEPOINT) vastleggen, een lagere isolatieniveau gebruiken.

Codevoorbeeld:

-- Voorbeeld van batch-update van 10.000 rijen
WHILE 1 = 1
BEGIN
  UPDATE TOP (10000) mytable
  SET status = 'archived', updated = GETDATE()
  WHERE status = 'active';
  IF @@ROWCOUNT = 0 BREAK;
END

Belangrijke kenmerken:

Batchverwerking vermindert de duur van het vasthouden van blokkeringen
Atomiciteit is gegarandeerd alleen binnen elke mini-transactie
Sommige databasesystemen hebben speciale bulk-operators die de prestaties bij grote hoeveelheden versnellen

Vragen met een hint.

Is het mogelijk om een bulk UPDATE in één transactie uit te voeren zonder de tabel te blokkeren?

Over het algemeen niet. Een grote transactie blokkeert de tabel/pagina's en verhoogt het risico op blokkeringen en time-outs. Het is beter om in batches te werken.

Heeft de aanwezigheid van indices invloed op de snelheid van bulk updates?

Ja. Elke update van geïndexeerde velden vereist herstructurering van de index voor elke rij. Soms is het zinvol om tijdelijk indices te verwijderen, maar dit vereist een grondige analyse.

Worden alle rijen atomair bijgewerkt bij batch-updates?

Nee, atomiciteit is alleen gegarandeerd binnen één batch (rijlimiet/transactie). Als de batch wordt onderbroken, kunnen sommige rijen worden bijgewerkt en andere niet. Voor echte atomiciteit is alleen een volledige UPDATE in één transactie veilig, wat gevaarlijk is bij grote hoeveelheden.

Typische fouten en anti-patronen

Een eenmalige UPDATE zonder limiet, wat leidt tot escalatie van blokkeringen
Geen rekening houden met indices - het verwachten van hoge prestaties op geïndexeerde kolommen
Geen gebruik maken van punten van opslag (SAVEPOINT)

Voorbeeld uit het leven

Negatief geval

Een technische ingenieur besloot 10 miljoen rijen met één query in de productie-database bij te werken: UPDATE mytable SET status = 'archived'. De site "bevroor", de rollback duurde tientallen minuten, de prestaties leden.

Voordelen:

Eenvoud van de opdracht, minimaal aantal regels Nadelen:
Freeze/blokkering van de productie-service
Mogelijke grote rollback van de transactie bij een fout

Positief geval

De query is opgebroken in batches van 10.000 rijen met korte transacties, de update vindt plaats tijdens werkuren zonder stilstand.