アシャからベンに 300 ルピーを移動するには、1 つのアカウントから減算し、もう 1 つのアカウントに追加するという 2 つの更新が必要です。それらの間でシステムが故障すると、お金は消えてしまいます。 SQL トランザクション は、両方のステップを 1 つの単位として扱うことで、これを不可能にするために存在します。

このガイドでは、小規模なアカウント テーブルでのコミット、ロールバック、失敗について説明します。これらの例は SQLite で実行し、表示された残高はその実行から得られたものです。基本的なコマンドはどのデータベースでも似ていますが、ロックと分離の動作は大きく異なるため、独自のデータベースのドキュメントを確認する場所を示します。

トランザクションとは何ですか?

トランザクションは、データベースが単一の論理操作として扱う 1 つ以上の SQL ステートメントのシーケンスです。これが私たちのテーブルです:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

CHECK 制約は、残高が決してゼロを下回らないというルールであり、後で使用します。トランザクションを開始する構文はさまざまであることに注意してください。SQLite と PostgreSQL は BEGIN を受け入れますが、MySQL には START TRANSACTION があり、SQL Server には BEGIN TRANSACTION が使用されます。

明示的なトランザクションの外では、ほとんどのデータベースは autocommit を使用し、各ステートメントは終了するとすぐに保存されます。 Oracle は注目に値する例外です。Oracle のセッションは、コミットするまでトランザクション内に留まるからです。

COMMIT: 永続化します

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

COMMIT が実行されるまで、変更は暫定的なものです。正常に返されると、データベースはそれらが保存されることを保証します。両方のアカウントの合計は 1200 に留まり、これが演習の要点です。

ロールバック: すべてをキャンセルします

ROLLBACK はトランザクション開始以降のすべてを元に戻します。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

接続が切断されたり、コミット前にサーバーがクラッシュした場合にもロールバックが自動的に行われるため、中途半端なトランザクションは存続しません。

ステートメントが失敗したとき

ここではより現実的なケースを示します。 Asha は 5000 を送金しようとしましたが、アカウントにはその送金がありません。 Ben への貸方が最初に実行され、次に借方が CHECK ルールに違反します。

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

この取引がなければ、ベンは 5000 ドルを保管していたでしょうし、帳簿は間違っていたでしょう。重要な点が 1 つあることに注意してください。失敗したステートメントは必ずしもトランザクション自体をキャンセルするとは限りません。たとえば、SQLite と MySQL では、以前のステートメントが保留状態のままになる可能性があるため、コードでロールバックを発行する必要があります。 PostgreSQL は代わりにトランザクション全体を中止としてマークし、ロールバックするまでそれ以降のステートメントを拒否します。

アプリケーション コードでは、トランザクションとエラー処理を常に組み合わせてください。これは Python の考え方であり、Python 例外処理 のガイドで構文が説明されています。

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

セーブポイント

セーブポイントはトランザクション内の位置をマークするため、作業の一部のみを元に戻すことができます。

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

999 ボーナスは取り消されましたが、残りのトランザクションは継続してコミットされました。これは、長いプロセスのオプションのステップに役立ちます。

ACID プロパティの説明

一貫性はよく誤解されています。データベースは、制約や外部キーなど、宣言したルールのみを強制します。コードが間違った金額を送金することを防ぐことはできません。耐久性は通常、コミットが確認される前に変更がディスクに安全に記録される先行書き込みログによって実現されます。

同時トランザクションでは何が起こるでしょうか?

1 つの SQLite ファイルに対して 2 つの接続を開きました。最初のトランザクションはトランザクションを開始し、まだコミットせずに行を 1 から 2 に更新しました。 2 番目の接続で確認された内容は次のとおりです。

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite では、一度に 1 つのライターしか許可されません。これは、多くのトランザクションが同時に異なる行を書き込むことができるサーバー データベースよりも単純です。ただし、原則は同じです。通常の設定では、他のセッションはコミットされていない変更を認識すべきではなく、競合する書き込みは待機する必要があります。私たちのテストでは、2 番目の接続を待機せずにすぐに放棄するように設定したため、エラーはすぐに表示されました。

異常を読み取る

トランザクションが重複すると、特定の問題が発生する可能性があります。 SQL 標準では、トランザクションが観察する可能性のあるものに関して次のように定義されています。

もう 1 つの問題は、アップデートが失われたです。 2 つのセッションで残高 100 が読み取られ、それぞれが 10 を加算し、それぞれが 110 を書き込みました。答えが 120 になるはずのときに、1 つの更新が事実上消えてしまいました。

SQL分離レベル

分離レベルを使用すると、厳密性と引き換えに同時実行性を実現できます。 SQL 標準で各レベルで許可される内容は次のとおりです。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

正確な構文と動作はデータベースによって異なります。デフォルトも異なります。PostgreSQL と SQL Server はコミットされた読み取りを使用しますが、MySQL の InnoDB は反復可能な読み取りを使用します。データベースによっては、標準で要求されているよりも強力な保証を提供したり、あるレベルを別のレベルとして扱ったりすることがあります。たとえば、PostgreSQL では、コミットされていない読み取りはコミットされた読み取りと同じように動作します。特定の動作に依存する前に、必ずデータベースのドキュメントを読んでください。

デッドロック

デッドロックは、2 つのトランザクションがそれぞれ、もう一方のトランザクションが必要とするロックを保持している場合に発生します。トランザクション A は行 1 をロックし、行 2 を必要としますが、トランザクション B は行 2 をロックし、行 1 を必要とします。どちらも続行できません。

PostgreSQL、MySQL、SQL Server などのデータベースはこれを検出し、エラーで 1 つのトランザクションをキャンセルします。これは通常の動作であり、アプリケーションはこれに備えて準備する必要があります。デッドロックを減らすには、常に一貫した順序で行を更新します (最小のアカウント ID から順に更新するなど)。トランザクションを短くし、犠牲者として選択されたときに再試行します。

ベストプラクティス

• トランザクションは短くしてください。長いトランザクションはロックを保持し、他のユーザーをブロックします。
• トランザクション内でユーザーや遅い API を決して待たないでください。この作業は開始前または開始後に実行してください。
• 常に失敗を処理します。 すべてのパスはコミットまたはロールバックで終了する必要があります。
• 制約を追加します。 データベースに強制する実際のルールを与えます。優れたテーブルデザインも役立ちます。
• 適切なインデックスを使用します。検索が高速になると、ロックが保持される時間が短くなります。
• 再試行には注意してください。 デッドロック時に再試行しますが、操作が 2 回適用されないように注意してください。
• 意図的に同時実行性をテストします。これらのバグの多くは負荷がかかっている場合にのみ現れるため、基本的な統合テストだけではバグを明らかにできない可能性があります。

まとめ

トランザクションは、いくつかの危険なステップを 1 つの安全な操作に変換します。ACID は、これを信頼できるものにする保証について説明します。ビジネス アクションが複数の行またはテーブルに触れるときは、それが途中で停止したらどうなるかを自問してください。答えが気になる場合は、トランザクションに含めてください。練習するには、ローカル データベースで 2 つのセッションを開き、自分で異常を生成してみます。

よくある質問

学び続ける