Antwoord.

Achtergrond van de vraag
Bij de verwerking van bedrijfslogica is het vaak nodig om gegevens in verschillende gekoppelde tabellen te wijzigen. Als er iets misgaat (bijvoorbeeld door een geschonden beperking), is het belangrijk om niet alleen de laatste operatie te annuleren, maar alle handelingen binnen de logica. hiervoor worden transactiemechanismen en foutafhandeling toegepast.

Probleem
Niet alle DBMS bieden standaard rollback aan bij fouten in meerstapsprocedures (vooral als TRY/CATCH, EXCEPTION-handlers worden gebruikt). Een onjuiste implementatie van transactielogica leidt tot "deeltijd" wijzigingen (een deel van de gegevens is bijgewerkt, een deel - niet), wat de zakelijke integriteit compromitteert.

Oplossing
Voor correcte afhandeling worden expliciete opening en afsluiting van transacties gebruikt (BEGIN TRANSACTION, COMMIT/ROLLBACK) met foutopsporing. In verschillende DBMS verschillen syntaxis en mogelijkheden.

Voorbeeld voor 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; -- gooi de fout verder
  END CATCH
END;

Voor 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;
  -- Een uitzondering zal de transactie automatisch terugdraaien
EXCEPTION WHEN OTHERS THEN
  INSERT INTO error_log(err_message, err_date) VALUES(SQLERRM, NOW());
  RAISE;
END;
$$ LANGUAGE plpgsql;

Belangrijkste kenmerken:

• We openen en beëindigen de transactie expliciet.
• We behandelen fouten in de catch/exceptions-block en draaien wijzigingen terug.
• We registreren foutinformatie voor audits.

Vragen met een valstrik.

Kan een fout binnen een opgeslagen procedure altijd de transactie automatisch terugdraaien? Nee! Bijvoorbeeld in MS SQL Server is zo'n rollback niet gegarandeerd - er is een expliciete ROLLBACK vereist. In PostgreSQL wordt bij elke fout de gehele transactie "vervallen" en vereist afsluiting.

Is het mogelijk om "gedeeltelijk" te committen binnen één procedure? Slechte praktijk. Het wordt aanbevolen om commit/rollback slechts eenmaal aan het einde van de bedrijfsoperatie uit te voeren. Sommige platforms staan "savepoints" toe, maar dit is voor speciale taken.

Welke soorten fouten kunnen niet worden opgevangen via TRY/CATCH/EXCEPTION in SQL? Sommige systeemstoringen (bijvoorbeeld een verbroken verbinding met de server) komen niet in de handler terecht en kunnen rollback onmogelijk maken.

Typische fouten en anti-patronen

• Laat COMMIT zonder foutafhandeling: we registreren "gebroken" of gedeeltelijk gewijzigde gegevens in de database.
• Meerdere COMMIT/ROLLBACK binnen één bedrijfsoperatie.
• Negeren van het registreren van fouten.

Voorbeeld uit het leven

Negatieve case

De opgeslagen procedure werkte drie tabellen bij en eindigde met een COMMIT zonder foutafhandeling. Als gevolg daarvan was de eerste al gewijzigd bij een fout in de tweede tabel, en het kostte uren om de gegevens handmatig uit back-ups terug te zetten.

Voordelen:
Simpel en "werkt", zolang er geen storing optreedt.

Minpunten:
Risico van inconsistentie; het is moeilijk om snel te herstellen bij fouten.

Positieve case

We hebben expliciete foutafhandeling ingevoerd + rollback van transacties met het registreren van alle fouten in een apart log. Terugkeer naar de oorspronkelijke staat van de database duurt seconden, fouten worden geanalyseerd en geminimaliseerd.

Voordelen:
Garantie voor de onveranderlijkheid van gewassen gegevens, zelfs bij storingen; transparante logica.

Minpunten:
Iets complexer voor beginners, vereist discipline in de code.