Risposta.

Bulk UPDATE è una procedura critica quando si modificano un gran numero di righe in tabelle correlate. Nella storia di SQL, l'implementazione tipica è UPDATE con una sottoquery o JOIN. Problema: qualsiasi operazione di aggiornamento di massa senza controllo dell'ordine di esecuzione blocca molte righe, provoca escalation delle lock e può portare a deadlock durante aggiornamenti multipli.

Soluzione:

Suddividi sempre l'UPDATE in piccoli batch (ad esempio, per chiave primaria o intervalli di date).
Usa approcci SET-oriented tramite JOIN, ma evita aggiornamenti di massa senza limiti.
Applica attentamente i filtri, indicizza i campi secondo le condizioni WHERE, considera l'ordine delle operazioni per tabelle correlate.

Esempio di codice (PostgreSQL):

UPDATE Orders o
SET status = 'archived'
FROM Customers c
WHERE o.customer_id = c.id
  AND c.closed = TRUE
  AND o.status != 'archived';

Oppure in batch:

WITH upd AS (
   SELECT o.id
   FROM Orders o
   JOIN Customers c ON o.customer_id = c.id
   WHERE c.closed = TRUE
   AND o.status != 'archived'
   LIMIT 10000
)
UPDATE Orders
SET status = 'archived'
WHERE id IN (SELECT id FROM upd);

Caratteristiche chiave:

Evitare di aggiornare "t tutta la tabella in una volta" — sempre batch.
Utilizzare indici sui campi aggiornati e filtrati.
Definire chiaramente le condizioni di selezione, evitando aggiornamenti di massa di righe non necessarie.

Domande trabocchetto.

Cosa succede se si avvia contemporaneamente un UPDATE su tabelle simili senza separazione degli intervalli o filtri contrari?

È probabile che si verifichi un deadlock: i processi bloccano le stesse righe, aspettandosi reciprocamente. Per evitarlo, i pacchetti non devono sovrapporsi o devono essere eseguiti rigorosamente in sequenza.

C'è differenza tra UPDATE tramite JOIN e sottoquery, se si tratta di un cambio di stato di massa?

Se ci sono indici appropriati, la differenza principale è solo nella leggibilità e a volte nelle prestazioni della specifica DBMS. JOIN è generalmente più veloce, in quanto consente all'ottimizzatore di elaborare un piano migliore.

Quando è rilevante utilizzare TRUNCATE/DELETE invece di UPDATE?

Se la logica aziendale lo consente — ad esempio, è necessario eliminare fisicamente le registrazioni archivi o azzerare una tabella, piuttosto che semplicemente cambiare il flag di stato. Ma per un aggiornamento di massa dello stato — solo UPDATE.

Errori tipici e anti-pattern

Aggiornamento di massa "senza filtro": lock, rollback, deadlock.
Mancanza di indici — scansione completa delle tabelle.
Esecuzione parallela di UPDATE senza suddivisione degli intervalli per chiavi.

Esempio dalla vita

Caso negativo

In un grande negozio online, venivano eseguiti molteplici UPDATE per cambiare lo stato degli ordini e dei clienti contemporaneamente, senza suddivisione per intervalli. Risultato: lock reciproci, è stato necessario il rollback forzato più volte e sono stati persi dati non registrati.

Vantaggi:

Tutto in una sola query.

Svantaggi:

Possibili deadlock, perdita di prestazioni, rollback di enormi masse di dati anche per un piccolo errore.

Caso positivo

Ampie selezioni sono state suddivise in batch, eseguite rigorosamente in sequenza e sono state elaborate solo le righe necessarie in base al filtro.