Risposta.

Storia della domanda
Nelle operazioni di elaborazione della logica aziendale è spesso necessario modificare i dati in più tabelle correlate. Se qualcosa va storto (ad esempio, a causa di una violazione del vincolo), è importante annullare non solo l'ultima operazione, ma tutte le azioni nell'ambito della logica. A tal fine viene applicata la meccanica delle transazioni e la gestione degli errori.

Problema
Non tutti i DBMS garantiscono per impostazione predefinita il rollback in procedure multipasso (soprattutto se vengono utilizzati TRY/CATCH, gestori delle eccezioni). Una scrittura errata della logica transazionale porta a modifiche "a pezzi" (parte dei dati viene aggiornata, parte no), compromettendo l'integrità aziendale.

Soluzione
Per una corretta gestione si utilizzano l'apertura e la conclusione esplicita delle transazioni (BEGIN TRANSACTION, COMMIT/ROLLBACK) con la cattura degli errori. La sintassi e le possibilità variano tra i diversi DBMS.

Esempio per MS SQL Server:

CREATE PROCEDURE update_balances(@from INT, @to INT, @amount DECIMAL(10,2))
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE accounts SET balance = balance - @amount WHERE id = @from;
      UPDATE accounts SET balance = balance + @amount WHERE id = @to;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    INSERT INTO error_log(err_message, err_date) VALUES(ERROR_MESSAGE(), GETDATE());
    THROW; -- rilancia l'errore
  END CATCH
END;

Per PostgreSQL:

CREATE OR REPLACE FUNCTION update_balances(from_id INT, to_id INT, amount NUMERIC)
RETURNS void AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- Un'eccezione farà automaticamente il rollback della transazione
EXCEPTION WHEN OTHERS THEN
  INSERT INTO error_log(err_message, err_date) VALUES(SQLERRM, NOW());
  RAISE;
END;
$$ LANGUAGE plpgsql;

Caratteristiche chiave:

• Apriamo e concludiamo esplicitamente la transazione.
• Gestiamo gli errori nel blocco catch/exception e rollback delle modifiche.
• Registriamo le informazioni sugli errori per audit.

Domande insidiose.

Un errore all'interno di una procedura memorizzata può sempre fare rollback automaticamente della transazione? No! Ad esempio, in MS SQL Server tale rollback non è garantito: è necessario un ROLLBACK esplicito. In PostgreSQL, in caso di errore, l'intera transazione diventa "compromessa" e richiede conclusione.

È possibile "commettere parzialmente" all'interno di una sola procedura? È una cattiva pratica. Si consiglia di eseguire commit/rollback solo una volta alla fine dell'operazione aziendale. Alcune piattaforme consentono "savepoints", ma questa è per operazioni speciali.

Quali tipi di errori non possono essere catturati tramite TRY/CATCH/EXCEPTION in SQL? Alcuni guasti di sistema (ad esempio, la perdita della connessione al server) non entreranno nel gestore e possono rendere impossibile il rollback.

Errori tipici e anti-pattern

• Lasciare COMMIT senza gestione degli errori: registriamo in database dati "rotti" o parzialmente modificati.
• Molteplici COMMIT/ROLLBACK all'interno di una singola operazione aziendale.
• Ignorare la registrazione degli errori.

Esempio dalla vita reale

Caso negativo

La procedura memorizzata aggiornava tre tabelle e terminava con COMMIT senza catturare errori. Di conseguenza, in caso di errore nella seconda tabella, la prima era già stata modificata e ci è voluto ore per "rollback" manualmente i dati dalle copie di backup.

Vantaggi:
Semplice e "funziona", finché non si verifica un errore.

Svantaggi:
Rischio di inconsistenza; impossibile riprendersi rapidamente dagli errori.

Caso positivo

È stata implementata una gestione esplicita degli errori + rollback delle transazioni con registrazione di tutti i guasti in un log separato. Tornare a uno stato di integrità del database richiede secondi, i guasti vengono analizzati e minimizzati.

Vantaggi:
Garanzia di immutabilità dei dati anche in caso di anomalie; logica trasparente.

Svantaggi:
Leggermente più complesso per i principianti, richiede disciplina nel codice.