Posted inデータベース

SQLのデータベーストランザクション管理：データの一貫性と整合性を守る

Database tutorial - IT technology blog
Database tutorial - IT technology blog

銀行振込が消えた日

数年前、小規模なECプラットフォームの決済モジュールを開発していたときのことだ。ロジックは単純に見えた。買い手の口座から残高を差し引き、売り手の口座に加算する。UPDATE文を2回実行するだけ。ところがある午後、その2つのクエリの実行中にサーバーが突然落ちた。買い手の残高は減った。売り手には一円も入らなかった。お金がどこかへ消えてしまったのだ。

このバグから、どんな教科書よりも多くのことをデータベーストランザクションについて学んだ。複数の行を操作するSQL、特に金融・在庫・ユーザーデータにまたがる処理を書くなら、トランザクションはオプションではない。絶対に必要な要件だ。

根本原因：トランザクションなしでデータが壊れる理由

問題はSQLの書き方にあるわけではない。データベースは文を1つずつ実行していく。現実の世界はその間、おとなしく待ってはくれない。

関連する2つの文の間には、さまざまな障害が起こりうる：

  • アプリケーションサーバーが処理の途中でクラッシュする
  • ネットワークタイムアウトで接続が切断される
  • 別の同時実行ユーザーが中途半端なデータを読み取る
  • 2番目の文で制約違反が起き、1番目の文がすでにコミット済みになっている

トランザクションがなければ、各SQL文は即座に自動コミットされる。実行が終わった瞬間に確定する。次の文が失敗しても「取り消し」はできない。

これまでMySQL、PostgreSQL、MongoDBをさまざまなプロジェクトで使ってきた。それぞれに強みがある。ただトランザクション保証という観点では、PostgreSQLの厳格なACID準拠に何度も救われてきた。MySQL（InnoDB）も堅実だが、トランザクションの境界を意識的に管理する必要がある。MongoDBは後からマルチドキュメントトランザクションを追加したが、コアの設計思想というよりも後付けの印象は否めない。

ACIDが本当に意味すること（教科書的な建前抜きで）

ACIDはトランザクションにおける「信頼性」の定義だ。理論の話は置いておいて、各プロパティをあなたも経験したことがあるような実際の障害にマッピングして説明する：

原子性（Atomicity）— すべて成功か、すべて失敗か

トランザクション内のすべての文が成功するか、一つも実行されないかのどちらかになる。5ステップのうち3番目で失敗したら、1番目と2番目は自動的にロールバックされる。私の決済バグは原子性の失敗だった。2つのUPDATE文が1つの原子的な単位にまとめられていなかったのだ。

一貫性（Consistency）— ルールは常に守られる

データベースはある有効な状態から別の有効な状態へ移行する。制約、外部キー、ビジネスルールはトランザクション全体を通して適用される。文ごとではない。

独立性（Isolation）— トランザクションはお互いの作業を見ない

同時実行されるトランザクションは、順番に実行されているかのように振る舞う。あるトランザクションは別のトランザクションのコミットされていない変更を読み取れない。（正確な保証は分離レベルによって異なる。後ほど詳しく説明する。）

永続性（Durability）— コミットされたデータはクラッシュを生き延びる

COMMITしたらディスクに書き込まれる。その1ミリ秒後にサーバーが落ちても、再起動時にデータはちゃんと存在している。

基本的なトランザクション構文

コアコマンドはMySQLとPostgreSQL共通だ：

-- トランザクションを開始する
BEGIN;

-- SQLの処理
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;

-- 問題なければコミット
COMMIT;

-- 何か問題が起きたらロールバック
ROLLBACK;

アプリケーションコードではプログラムで管理する。以下はpsycopg2を使ったPostgreSQLのPythonサンプルだ：

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
conn.autocommit = False  # 自動コミットを無効化する — トランザクションは手動管理

try:
    cur = conn.cursor()

    # 送金元から差し引く
    cur.execute(
        "UPDATE accounts SET balance = balance - %s WHERE user_id = %s AND balance >= %s",
        (500, 1, 500)
    )
    if cur.rowcount == 0:
        raise ValueError("残高不足またはユーザーが見つかりません")

    # 受取人に加算する
    cur.execute(
        "UPDATE accounts SET balance = balance + %s WHERE user_id = %s",
        (500, 2)
    )
    if cur.rowcount == 0:
        raise ValueError("受取人が見つかりません")

    conn.commit()
    print("振込完了")

except Exception as e:
    conn.rollback()
    print(f"振込失敗、ロールバックしました: {e}")

finally:
    conn.close()

conn.autocommit = False に注目してほしい。これは非常に重要だ。これを設定しないと、psycopg2は各文を独自の暗黙的なトランザクションでラップしてしまい、トランザクションを使う意味がなくなる。

SAVEPOINT — 部分的なロールバックが必要なとき

トランザクション全体を失わずに、一部だけをロールバックしたいことがある。SAVEPOINTを使えば、トランザクション内に名前付きのチェックポイントを設定できる：

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 1500);

SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (101, 5, 2);

-- 商品5の在庫が切れていた
ROLLBACK TO SAVEPOINT after_order;

-- 代替商品で再試行する
INSERT INTO order_items (order_id, product_id, qty) VALUES (101, 7, 2);

COMMIT;

SAVEPOINTはバッチ処理で特に役立つ。500行を挿入していて347行目に不正なレコードがあった場合、そのバッチ全体を中断したくはない。不正な行の直前に設定したセーブポイントまでロールバックして、ログに記録し、処理を続ければいい。

分離レベル：トレードオフを選ぶ

分離レベルは多くの開発者が意外に思うポイントだ。標準的な4つのレベルがあり、それぞれデータの正確性と同時実行のスループットをトレードオフする：

READ UNCOMMITTED

他のセッションのコミットされていない変更を読み取れる。これを「ダーティリード」という。なぜ危険かというと：スレッドAが口座残高を$500から$0に更新したがまだコミットしていない。スレッドBが$0を読み取り、引き出しをブロックする。その後スレッドAがロールバックした。スレッドBは実際には存在しなかったデータを元に、正当な引き出しをブロックしてしまった。最速のレベルだが、ベンチマーク以外で使ったことはない。

READ COMMITTED（PostgreSQLのデフォルト）

コミット済みのデータのみを読み取る。ダーティリードは防げる。ただし「非反復読み取り」は発生しうる。同一トランザクション内で同じ行を2回読み取ったとき、その間に別のトランザクションがコミットしていた場合、異なる結果が返ってくることがある。

REPEATABLE READ（MySQL InnoDBのデフォルト）

読み取った行はトランザクション終了まで保護される。非反復読み取りを防げる。ほとんどの金融処理では、これが適切なデフォルト設定だ。

SERIALIZABLE

完全な分離。トランザクションが1つずつ順番に実行されたかのように振る舞う。ファントムリードを含むあらゆる異常を防ぐ。重要な財務照合や監査処理に使うべきだ。高い同時実行時にはスループットが目に見えて低下するので、グローバルには適用しないこと。

-- 現在のトランザクションの分離レベルを設定する（PostgreSQL）
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- またはセッション単位で設定する（MySQL）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

よくあるトランザクションの落とし穴

長時間実行されるトランザクション

オープンなトランザクションはロックを保持し続ける。1分あたり500リクエストを処理している忙しいPostgreSQLインスタンスで、トランザクションが30秒間開きっぱなしになると、ロック待ちのキューが50接続以上に膨れ上がる可能性がある。その間ずっとwrite-aheadログも肥大化し続ける。他のクエリも待ち続ける。

トランザクションは短く保つこと。開始は遅らせ、コミットは早めに。重い計算はトランザクションの境界の外に出す：

# 悪い例：トランザクション内でAPI呼び出し
conn.autocommit = False
cur.execute("UPDATE orders SET status = 'processing' WHERE id = %s", (order_id,))
response = requests.post(payment_gateway_url, data=payload)  # 3秒以上かかる可能性がある！
cur.execute("UPDATE orders SET status = 'paid' WHERE id = %s", (order_id,))
conn.commit()

# 良い例：トランザクションはDBの処理だけに絞る
response = requests.post(payment_gateway_url, data=payload)  # トランザクションの外で実行

conn.autocommit = False
if response.status_code == 200:
    cur.execute("UPDATE orders SET status = 'paid' WHERE id = %s", (order_id,))
    conn.commit()
else:
    cur.execute("UPDATE orders SET status = 'failed' WHERE id = %s", (order_id,))
    conn.commit()

アプリケーションコードでエラーハンドリングを忘れる

コードが例外をスローしてROLLBACKを呼ばなかった場合、接続が閉じるまでトランザクションは開いたままになる。コネクションプールでは、その接続が次のリクエストに壊れた状態で再利用されてしまう。トランザクションのロジックは必ずtry/except/finallyで囲むか、コンテキストマネージャーを使うこと：

# コンテキストマネージャーを使う方法（psycopg2）
with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE ...")
        cur.execute("INSERT ...")
    # conn.__exit__ は成功時にcommit()、例外時にrollback()を呼ぶ

本番環境で通用するトランザクション設計の原則

トランザクション要件のある本番システムをいくつか出荷してきた経験から、ルールを5つに絞った：

  1. 1つの論理操作 = 1つのトランザクション。「資金振替」は2つの別々のコミットではなく、1つのトランザクションだ。
  2. 短く、集中させる。50件のINSERTをまとめるのはいい。しかし利便性のために無関係な操作を同じトランザクションに混ぜてはいけない。
  3. リスクに合った分離レベルを選ぶ。通常のCRUD処理はREAD COMMITTEDで十分。集計や財務の合計値にはREPEATABLE READ以上が必要だ。
  4. コードで明示的にロールバックを処理する。接続のクリーンアップに任せてはいけない。
  5. 失敗ケースをテストする。開発環境でトランザクションの途中でクラッシュをシミュレートする。3文のうち2番目の後でプロセスを強制終了する。何が起きるか確認していなければ、トランザクションが正しく機能しているとは言えない。

キャリア初期に踏んだ決済バグは、数時間の手動データ修正と、非常に焦った顧客サポートへのメールで済んだ。もっとひどいことになっていたかもしれない。トランザクションは省略してはいけない唯一のSQL機能だ。最適化ではなく、正確性の保証なのだから。

Share:
Tags: