SQL: 構築段階で誤りを含みやすいのか？

A送金（AliceがBobに10ドル送る）という典型的な例は、一見単純に見えます。Aliceの残高を確認し、十分な資金があることを確認し、彼女の口座から金額を差し引き、Bobの口座に加算します。ほとんどのプログラミング言語では、このロジックは直感的です。しかし、リレーショナルデータベースの世界では、この「妥当な」コードは、しばしば並行性のバグの地雷原となります。

開発者がSQLを、並行性下で共有状態を管理するシステムとしてではなく、単なるスクリプト言語として扱うとき、主に3つの落とし穴に遭遇します。それは、原子性の欠如、Time-of-Check to Time-of-Use (TOCTOU) バグ、そしてデッドロックです。

並行性失敗の解剖学

送金の基本的なT-SQL実装を考えてみましょう。

DECLARE @balance INT;

SET @balance = (  
    SELECT balance  
    FROM accounts
    WHERE owner = 'alice'
);

IF @balance >= 10  
BEGIN  
    UPDATE accounts  
    SET balance = balance - 10  
    WHERE owner = 'alice';  
  
    UPDATE accounts  
    SET balance = balance + 10  
    WHERE owner = 'bob';  
END

1. 原子性のギャップ

もしシステムがクラッシュしたり、最初の UPDATE の後、2番目の UPDATE の前にプロシージャが中断された場合、お金はBobに届くことなくAliceの口座から消えてしまいます。両方の更新が実行されるか、あるいはどちらも実行されないことを保証するには、操作をトランザクション（BEGIN TRANSACTION と COMMIT TRANSACTION）で囲む必要があります。

2. TOCTOU レースコンディション

トランザクションを使用しても、Time-of-Check to Time-of-Use (TOCTOU) バグは残ります。もしAliceが同時に2つの送金（T1とT2）を開始した場合、どちらのトランザクションも引き出しを行う前に彼女の残高を読み取ってしまう可能性があります。両方のトランザクションが十分な残高を確認し、両方が引き出しを実行するため、Aliceの口座は最終的に残高不足（オーバードラフト）になります。

これを修正するには、開発者はロックヒント（T-SQL の UPDLOCK や他のダイアレクトの SELECT FOR UPDATE など）を使用して、読み取った瞬間にその行をロックし、現在のトランザクションが完了するまで他のトランザクションがその行を変更できないようにする必要があります。

3. デッドロックの罠

ロックを導入すると、新しい問題が発生します。デッドロックです。もしAliceがBobに送金し、同時にBobがAliceに送金する場合、T1はAliceの口座をロックしてBobの口座を待ち、T2はBobの口座をロックしてAliceの口座を待ちます。どちらも先に進むことができません。

著者は、すべてのロックを事前に取得することを提案していますが、より堅牢な業界標準は、一貫性のある決定論的な順序（例：口座IDでソートする）でロックを取得することです。

SQL は「構築段階で誤りを含みやすい」のか？

提示されている核心的な議論は、SQLの設計が、誤ったコードを書きやすくしてしまっているという点です。著者は、医療の投与量管理のような、正確性が極的に重要となるシステムにおいては、Rust のような「恐れのない並行性（fearless concurrency）」モデルが必要であると示唆しています。そこでは、正しい挙動がデフォルトであり、「unsafe」な操作は明示的になります。

しかし、この主張は、経験豊富なデータベースエンジニアの間で大きな議論を呼び起こしました。多くの人々は、これらの問題はSQLの構築上の欠陥ではなく、むしろ「SQL 101」の概念であると主張しています。

反論

コミュニティからは、これらの「欠陥」に関していくつかの重要なポイントが挙げられました。

  • 分離レベル: 記述された問題の多くは、弱いデフォルトの分離レベル（READ COMMITTED など）によるものです。真の SNAPSHOT 分離または SERIALIZABLE レベルを使用すれば、競合をサイレントな失敗ではなくリトライに変換することで、多くのレースコンディションを排除できます。

  • エンジン・インテリジェンス: 現代のデータベースエンジン（MySQL, SQL Server）は、デッドロックの受動的な観察者ではありません。彼らはデッドロック・サイクルを能動的に検出し、ロールバックさせるための「犠牲者」となるトランザクションを選択し、特定の特定のエラー（例：SQL Server の Error 1205）を返し、アプリケーションがリトライを試みることを期待しています。

  • 慣用的なパターン: 経験豊富な開発者は、「確認してから実行する」というパターンを完全に避けます。代わりに、条件付き更新を使用します。

    UPDATE accounts SET balance = balance - 10 
WHERE owner = 'alice' AND balance >= 10;

    これは、エンジン・レベルで確認と実行を単一の原子的な操作に結合します。

アーキテクチャの代替案

T-SQL の構文を修正することを超えて、この議論では、金融データのための2つの優れたアーキテクチャ・パターンが強調されました。

台帳パターン (Append-Only)

レースコンディションが発生しやすい可変状態である balance カラムを更新するのではなく、多くの人は追記型の台帳（append-only ledger）を提案しています。このモデルでは、残高を UPDATE するのではなく、トランザクションの行（例：Alice: -10, Bob: +10）を INSERT するだけです。現在の残高は、すべての台帳エントリの合計として計算されます。

アプリケーション・レイヤーの並行性制御

一部の開発者は、並行性制御をアプリケーション・レイヤーに移動させるか、あるいは分散ロック・メカニズムを使用して、一度に一つのプロセスだけが特定の資源を書き換えることができるようにし、データベースの内部的なロック機構への依存を減らしています。

結論

SQLが「構築段階で誤りを含みやすい」のか、あるいは単にACID特性の規律ある理解が必要なだけなのかは別として、教訓は変わりません。並行システムにおいて、「妥当に見えるコード」と「正しいコード」の間の距離は非常に大きいです。重要なシステムにおいては、デフォルト設定に頼ることは滅多に十分ではありません。明示的なロック戦略、厳格な分離レベル、または不変の台帳アーキテクチャが不可欠です。データの整合性を維持するためには、それらが不可欠です。

Sources