SQLite トランザクション処理 | データ整合性を学ぶ

はじめに

この実験（Lab）では、SQLite のトランザクション処理について、トランザクションを使用してデータの整合性を維持することに焦点を当てて探求します。トランザクションを開始およびコミットする方法を学び、複数の関連する操作が単一の不可分なユニットとして扱われるようにします。

この実験（Lab）では、BEGIN TRANSACTION を使用してトランザクションを開始し、データベースに変更を加え、COMMIT ステートメントを使用してそれらの変更を永続的に保存するプロセスを説明します。また、ROLLBACK を使用して変更を元に戻し、より詳細な制御のために SAVEPOINT を使用する方法も学びます。この実践的な経験を通して、SQLite におけるトランザクションが原子性（Atomicity）、一貫性（Consistency）、独立性（Isolation）、永続性（Durability）（ACID プロパティ）をどのように保証するかについての理解を深めます。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/start_trans("Begin New Transaction") sqlite/SQLiteGroup -.-> sqlite/save_trans("Commit Transaction") sqlite/SQLiteGroup -.-> sqlite/undo_trans("Rollback Transaction") subgraph Lab Skills sqlite/get_all -.-> lab-552558{{"SQLite トランザクション処理"}} sqlite/query_where -.-> lab-552558{{"SQLite トランザクション処理"}} sqlite/start_trans -.-> lab-552558{{"SQLite トランザクション処理"}} sqlite/save_trans -.-> lab-552558{{"SQLite トランザクション処理"}} sqlite/undo_trans -.-> lab-552558{{"SQLite トランザクション処理"}} end

データベースとテーブルの作成

この最初のステップでは、SQLite データベースと、ユーザーデータを格納するためのテーブルを作成します。これは、以降のステップでトランザクション処理を探求するための基盤となります。

まず、LabEx VM でターミナルを開きます。デフォルトのパスは /home/labex/project です。

次に、mydatabase.db という名前の SQLite データベースを作成しましょう。次のコマンドを実行して、データベースファイルを作成し、SQLite コマンドラインツールを開きます。

sqlite3 mydatabase.db

SQLite シェル内に入ったことを示すプロンプトが表示されます。

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

次に、基本的なユーザー情報を格納するために、users という名前のテーブルを作成します。このテーブルには、id (一意の識別子)、name、および balance の 3 つの列があります。sqlite> プロンプトで次の SQL コマンドを入力し、Enter キーを押します。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

このコマンドは、users テーブルを次のように設定します。

id は、各ユーザーの主キーとして機能する整数です。
name は、ユーザーの名前を表すテキストフィールドです。
balance は、ユーザーの口座残高を表す実数です。

次に、いくつかの初期データを users テーブルに挿入します。

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

これらのコマンドは、アリスとボブの 2 人のユーザーを、それぞれ初期残高 100.0 と 200.0 で追加します。

データが正しく追加されたことを確認するには、次のコマンドを実行して、テーブル内のすべてのレコードを表示します。

SELECT * FROM users;

期待される出力：

1|Alice|100.0
2|Bob|200.0

この出力は、各レコードの id、name、および balance を示しています。SELECT * コマンドは、指定されたテーブルからすべての列を取得します。

トランザクションの開始とコミット

このステップでは、トランザクションを開始およびコミットする方法を学びます。トランザクションは、複数の関連する操作を実行する際にデータの整合性を確保するために不可欠です。

トランザクションを開始するには、BEGIN TRANSACTION コマンドを使用します。

BEGIN TRANSACTION;

このコマンドは、SQLite に変更の追跡を開始するように指示しますが、トランザクションを明示的にコミットするまで、データベースに永続的に適用しません。

次に、アリスからボブに 50.0 を送金しましょう。

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

これらのコマンドは、トランザクション内でアリスとボブの残高を更新します。アリスの残高は 50.0 減少し、ボブの残高は 50.0 増加します。

変更を保存するには、COMMIT コマンドを使用します。

COMMIT;

このコマンドはトランザクションをコミットし、変更を永続的なものにします。

テーブルを再度クエリして、変更を確認します。

SELECT * FROM users;

期待される出力：

1|Alice|50.0
2|Bob|250.0

この出力は、トランザクションが正常にコミットされ、アリスとボブの残高がそれに応じて更新されたことを確認します。

トランザクションのロールバック

このステップでは、ROLLBACK コマンドを使用して、トランザクション中に行われた変更を元に戻す方法を学びます。これは、エラーを処理し、データの整合性を確保するために不可欠です。

まず、新しいトランザクションを開始します。

BEGIN TRANSACTION;

次に、アリスからボブに 100.0 を送金しようとしますが、今回はエラー状態をシミュレートします。

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

アリスは 50.0 しか持っていないため、最初の更新により残高がマイナスになります。SQLite はデフォルトで制約を強制しませんが、この操作後にエラーが発生することをシミュレートします。

変更を元に戻すには、ROLLBACK コマンドを使用します。

ROLLBACK;

このコマンドはトランザクションをロールバックし、BEGIN TRANSACTION ステートメント以降に行われた変更をすべて元に戻します。

users テーブルをクエリして、変更がロールバックされたことを確認します。

SELECT * FROM users;

期待される出力：

1|Alice|50.0
2|Bob|250.0

この出力は、トランザクションが正常にロールバックされ、アリスとボブの残高が変更されていないことを確認します。

セーブポイントの実装

このステップでは、トランザクション内でセーブポイントを使用する方法を学びます。セーブポイントを使用すると、トランザクション全体をロールバックせずに、ロールバックできるトランザクション内の中間点を作成できます。

まず、新しいトランザクションを開始します。

BEGIN TRANSACTION;

savepoint1 という名前のセーブポイントを作成します。

SAVEPOINT savepoint1;

次に、アリスからボブに 20.0 を送金しましょう。

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

savepoint2 という名前の別のセーブポイントを作成します。

SAVEPOINT savepoint2;

初期残高 300.0 で 'Charlie' という名前の新しいユーザーを追加しましょう。

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

ここで、チャーリーの追加は間違いだったと判断したとしましょう。savepoint1 にロールバックすると、INSERT ステートメントとアリスとボブ間の 20.0 の送金が元に戻ります。

ROLLBACK TO SAVEPOINT savepoint1;

savepoint1 へのロールバック後の変更を確認します。

SELECT * FROM users;

期待される出力：

1|Alice|50.0
2|Bob|250.0

チャーリーがテーブルに存在せず、アリスとボブの残高が savepoint1 より前の値に戻っているはずです。

次に、アリスからボブに 10.0 を送金しましょう。

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

最後に、トランザクションをコミットします。

COMMIT;

負の残高を防ぐ制約の追加

このステップでは、負の残高を防ぐために users テーブルに制約を追加します。これにより、無効なトランザクションを防ぎ、データの整合性を確保できます。

次の制約を users テーブルに追加します。

ALTER TABLE users ADD CONSTRAINT positive_balance CHECK (balance >= 0);

このコマンドは、positive_balance という名前の CHECK 制約を users テーブルに追加します。この制約により、balance 列は常に 0 以上である必要があります。

次に、アリスからボブに 1000 を送金しようとすると、制約に違反します。

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';
UPDATE users SET balance = balance + 1000 WHERE name = 'Bob';

今回は、UPDATE ステートメントは positive_balance 制約に違反するため、エラーをスローします。Error: CHECK constraint failed: positive_balance のようなエラーメッセージが表示されます。

トランザクションをロールバックします。

ROLLBACK;

変更がロールバックされたことを確認します。

SELECT * FROM users;

期待される出力：

1|Alice|40.0
2|Bob|260.0

これにより、ROLLBACK が失敗したトランザクションの前の元の状態にデータベースを正常に戻したことが確認されます。

まとめ

この実験（Lab）では、SQLite におけるトランザクション処理の基礎を学びました。トランザクションの開始とコミット、変更のロールバック、よりきめ細かい制御のためのセーブポイントの実装、およびデータの整合性を確保するための制約の追加について説明しました。これらのスキルは、堅牢で信頼性の高いデータベースアプリケーションを構築するために不可欠です。