Odpowiedź.

Masowe aktualizowanie danych w kilku powiązanych tabelach to klasyczne zadanie w przemysłowym programowaniu SQL. Wraz z rozwojem aplikacji biznesowych pojawiła się potrzeba jednoczesnego aktualizowania dużych wolumenów danych, gwarantując jednocześnie ich spójność. Historycznie radzono sobie z tym za pomocą cyklicznych scenariuszy, co prowadziło do niskiej wydajności i długotrwałych blokad. Później pojawiły się zaawansowane operatory DML (takie jak MERGE), konstrukcje transakcyjne i podejścia z tabelami staging.

Problem polega na tym, że aktualizacja danych dotyczy wielu tabel z relacjami (np. zamówienia i szczegóły zamówienia), co wiąże się z ryzykiem pojawienia się "osieroconych" rekordów (orphan rows), utraty wydajności z powodu blokad oraz nieprzewidywalnego obciążenia dla DBMS.

Rozwiązanie opiera się na wykorzystaniu atomowych transakcji, operacji UPDATE/DELETE/MERGE z warunkami JOIN, a także przetwarzaniu wsadowym danych. Dobrą praktyką jest odkładanie agregowanych zmian do tymczasowych tabel staging, a następnie stosowanie ich wsadowo przez transakcję. Przykład dla SQL Server z użyciem MERGE:

BEGIN TRANSACTION;

-- Przykład masowej aktualizacji głównej i powiązanej tabeli z użyciem MERGE
MERGE INTO orders AS tgt
USING temp_order_updates AS src
  ON tgt.id = src.id
WHEN MATCHED THEN
  UPDATE SET tgt.status = src.status, tgt.updated_at = src.updated_at;

MERGE INTO order_details AS tgt
USING temp_detail_updates AS src
  ON tgt.order_id = src.order_id AND tgt.sku = src.sku
WHEN MATCHED THEN
  UPDATE SET tgt.price = src.price, tgt.qty = src.qty;

COMMIT;

Kluczowe cechy:

Izolowanie operacji w jedną transakcję: brak pośrednich niespójności.
Użycie tabel staging do przygotowania zmiennych danych.
Zastosowanie operacji wsadowych w celu zmniejszenia blokad i optymalizacji obciążenia.

Pytania pułapki.

Czy można po prostu wykonać aktualizację głównej tabeli, a potem osobno — powiązanych, bez transakcji, jeśli wymagania dotyczące prędkości są surowe?

Osobne UPDATE'y poza transakcją prowadzą do brutalnej niespójności danych w przypadku błędu na jakimkolwiek etapie — na przykład, jeśli zamówienia zostały zaktualizowane, ale szczegóły nie, logika zostaje naruszona. W nowoczesnych DBMS użycie transakcji niemal nie zwiększa kosztów przy przetwarzaniu wsadowym.

Czy wydajność spadnie, jeśli zrobimy jedno dużą aktualizację z podzapytaniem? Czy może to prowadzić do blokad?

Tak, monolityczne UPDATE na dużych tabelach! prowadzą do eskalacji blokad, lock'ów tabeli i przestojów innych użytkowników. Lepiej podzielić przetwarzanie na wsady z limitem przez WHERE ... AND rownum/id/limit.

Przykład wsadu:

UPDATE orders
SET status = 'closed'
WHERE status = 'pending' AND id BETWEEN 100000 AND 199999;

MERGE gwarantuje atomowość i właściwą kolejność przetwarzania powiązanych tabel?

Nie, MERGE działa w ramach jednej tabeli. Do zaktualizowania powiązanych tabel wymagane jest osobne MERGE lub UPDATE, i koniecznie umieszczenie obu działań w jednej transakcji.

Typowe błędy i antywzorce

Brak transakcji przy masowych zmianach, co prowadzi do niespójności danych
Duże pojedyncze UPDATE/DELETE na ogromnych zbiorach bez LIMIT/WBATCH: blokady i przestoje
Niewłaściwa kolejność operacji (na przykład, najpierw aktualizacja szczegółów, a następnie głównej tabeli)

Przykład z życia

Negatywny przypadek

Firma aktualizowała status ("Zakończone") w milionie zamówień osobnymi zapytaniami poza transakcją: główne zamówienia, a następnie — szczegóły order_details. Pod obciążeniem serwer "spadał" — w przypadku awarii szczegóły pozostawały z "otwartym" statusem. Zalety:

Łatwe do zrealizowania
Minimalny kod

Wady:

Niespójność danych i trudności w późniejszym debugowaniu
Problemy z wycofaniem

Pozytywny przypadek

Wprowadziliśmy tabele staging i grupowe przetwarzanie w ramach transakcji. Najpierw wszystkie zmiany były obliczane i umieszczane w tymczasowych tabelach, a następnie wsadowo aktualizowane obie główne tabele. W przypadku awarii — pełny rollback. Zalety:

Gwarancja spójności i integralności danych
Wygoda kontroli i rollbacku