回答。

問題の歴史
ビジネスロジックの処理では、しばしば関連する複数のテーブルのデータを変更する必要があります。何かがうまくいかなかった場合（例えば、制約が violatedした場合）、最後の操作だけでなく、ロジックに基づくすべてのアクションを取り消すことが重要です。これを実現するために、トランザクションメカニズムとエラー処理が使用されます。

問題
すべてのDBMSが、特にTRY/CATCHやEXCEPTIONハンドラが使用される場合に、エラーが発生した場合に複数ステップの手続きでロールバックを保証するわけではありません。トランザクションロジックを誤って書くと、「部分的な」変更（データの一部が更新され、一部は更新されない）を引き起こし、ビジネスの整合性を損ないます。

解決策
適切な処理には、エラーをキャッチするための明示的なトランザクションの開始と終了（BEGIN TRANSACTION, COMMIT/ROLLBACK）が必要です。異なるDBMSでは、構文と機能が異なります。

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; -- エラーをさらにthrowします
  END CATCH
END;

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;
  -- 例外が発生すると、トランザクションは自動的にロールバックされます
EXCEPTION WHEN OTHERS THEN
  INSERT INTO error_log(err_message, err_date) VALUES(SQLERRM, NOW());
  RAISE;
END;
$$ LANGUAGE plpgsql;

主な特徴:

トランザクションを明示的に開始し、終了します。
catch/exceptionブロックでエラー処理を行い、変更をロールバックします。
監査用にエラー情報を記録します。

騙しの質問。

ストアドプロシージャ内のエラーが常にトランザクションを自動的にロールバックできますか？ いいえ！例えば、MS SQL Serverではそのロールバックは保証されていません - 明示的なROLLBACKが必要です。PostgreSQLでは、エラーが発生するとトランザクション全体が「破損」し、終了を要求します。

一つのプロシージャ内で「部分的に」コミットできますか？ 悪い実践です。ビジネス操作の最後でのみcommit/rollbackを実行することをお勧めします。一部のプラットフォームでは「savepoints」が可能ですが、これは特別なタスクのためです。

SQLでTRY/CATCH/EXCEPTIONを介して捕らえることができないエラーのタイプは何ですか？ 一部のシステム障害（例えば、サーバーとの接続が切断されるなど）はハンドラにキャッチされず、ロールバックが不可能になります。

一般的なエラーとアンチパターン

エラー処理なしでCOMMITを残す：データベースに「壊れた」または部分的に更新されたデータを記録します
一つのビジネス操作内での複数のCOMMIT/ROLLBACK
エラー記録を無視する

実生活の例

ネガティブケース

ストアドプロシージャは3つのテーブルを更新し、エラーキャッチなしでCOMMITで終了しました。そのため、2番目のテーブルでエラーが発生した場合、最初のテーブルはすでに変更されており、手動でバックアップから「ロールバック」するのに数時間かかりました。

利点:
単純で「動作します」、障害が発生するまで。

欠点:
不整合のリスク；エラー時に迅速に回復できない。

ポジティブケース

エラー処理を明示的に実装し、すべての障害をログに記録するトランザクションのロールバックを導入しました。データベースの整合性を保つための復元には数秒しかかからず、障害が分析され、最小化されます。

利点:
障害が発生しても、クリーンなデータの不変性が保証されている；明確なロジック。

欠点:
初心者には少し難しく、コードの規律が必要です。

SQLのストアドプロシージャでビジネスロジックの整合性を確保するために、エラー処理とトランザクションをどのように実装しますか？異なるDBMSにおけるアプローチの違いは何ですか？

回答。

騙しの質問。

一般的なエラーとアンチパターン

実生活の例

ネガティブケース

ポジティブケース