Odpowiedź.

Historia pytania
W operacjach przetwarzania logiki biznesowej często konieczne jest zmienianie danych w kilku powiązanych tabelach. Jeśli coś zakończyło się niepowodzeniem (na przykład z powodu naruszenia ograniczenia), ważne jest, aby cofnąć nie tylko ostatnią operację, ale wszystkie działania w ramach logiki. W tym celu stosuje się mechanikę transakcji oraz obsługę błędów.

Problem
Nie wszystkie DBMS domyślnie zapewniają cofnięcie przy błędzie w procedurach wieloetapowych (szczególnie gdy używane są TRY/CATCH, EXCEPTION-handlery). Niewłaściwe napisanie logiki transakcyjnej prowadzi do „częściowych” zmian (część danych została zaktualizowana, część - nie), co kompromituje integralność biznesową.

Rozwiązanie
Do poprawnej obsługi stosuje się wyraźne otwieranie i kończenie transakcji (BEGIN TRANSACTION, COMMIT/ROLLBACK) z uchwyceniem błędów. W różnych DBMS składnia i możliwości różnią się.

Przykład dla 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; -- przekazujemy błąd dalej
  END CATCH
END;

Dla 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;
  -- Wyjątek automatycznie cofnę transakcję
EXCEPTION WHEN OTHERS THEN
  INSERT INTO error_log(err_message, err_date) VALUES(SQLERRM, NOW());
  RAISE;
END;
$$ LANGUAGE plpgsql;

Kluczowe cechy:

• Otwieramy i wyraźnie kończymy transakcję.
• Obsługujemy błędy w bloku catch/exception i cofamy zmiany.
• Rejestrujemy informacje o błędach do audytu.

Pytania z pułapką.

Czy błąd wewnątrz procedury składowanej zawsze automatycznie cofa transakcję? Nie! Na przykład, w MS SQL Server takie cofnięcie nie jest gwarantowane - wymagana jest jawna komenda ROLLBACK. W PostgreSQL przy każdym błędzie cała transakcja staje się "zepsuta" i wymaga zakończenia.

Czy można "częściowo" zatwierdzić w jednej procedurze? Zła praktyka. Zaleca się wykonywanie commit/rollback tylko raz na końcu operacji biznesowej. Część platform pozwala na „savepoints”, ale to dla specjalnych zadań.

Jakie typy błędów nie można uchwycić przez TRY/CATCH/EXCEPTION w SQL? Niektóre awarie systemowe (na przykład przerwanie połączenia z serwerem) nie trafią do handlera i mogą uniemożliwić cofnięcie.

Typowe błędy i antywzorce

• Zostawianie COMMIT bez obsługi błędów: zapisujemy w bazie „uszkodzone” lub częściowo zmienione dane
• Wielokrotne COMMIT/ROLLBACK w ramach jednej operacji biznesowej
• Ignorowanie rejestracji błędów

Przykład z życia

Negatywny przypadek

Procedura składowana aktualizowała trzy tabele i kończyła się COMMIT bez uchwycenia błędów. W rezultacie przy błędzie w drugiej tabeli pierwsza już została zmieniona, co wymagało godzinowego „cofnięcia” danych ręcznie z kopii zapasowych.

Zalety:
Prosto i „działa”, dopóki nie wystąpi awaria.

Wady:
Ryzyko niespójności; niemożność szybkiego przywrócenia w przypadku błędów.

Pozytywny przypadek

Wdrożono wyraźną obsługę błędów + rollback transakcji z rejestracją wszystkich awarii w osobnym logu. Powrót do integralnego stanu bazy zajmuje sekundy, awarie są analizowane i minimalizowane.

Zalety:
Gwarancja niezmienności danych nawet w przypadku awarii; przejrzysta logika.

Wady:
Nieco trudniej dla początkujących, wymaga dyscypliny w kodzie.