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

はじめに

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

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

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

この最初のステップでは、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 テーブルに負の残高を防止する制約を追加します。これにより、無効なトランザクションを防止し、データの整合性を確保します。

SQLite は ALTER TABLE ADD CONSTRAINT を使用して既存のテーブルに制約を追加する機能をサポートしていないため、制約付きの新しいテーブルを作成する必要があります。まず、すべての操作が原子的に実行されるようにトランザクションを開始します。

BEGIN TRANSACTION;

同じ構造を持つ新しいテーブルを作成しますが、CHECK 制約を含めます。

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL CHECK (balance >= 0)
);

古いテーブルから新しいテーブルにすべてのデータをコピーします。

INSERT INTO users_new SELECT * FROM users;

古いテーブルを削除します。

DROP TABLE users;

新しいテーブルを元の名前に変更します。

ALTER TABLE users_new RENAME TO users;

トランザクションをコミットして変更を永続化します。

COMMIT;

これで、users テーブルには、balance 列が常に 0 以上であることを保証する CHECK 制約が追加されました。

Alice から Bob に 1000 を送金しようとすると、制約に違反します。

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

この場合、UPDATE ステートメントは CHECK 制約に違反するため、エラーが発生します。エラーメッセージは、Error: CHECK constraint failed: balance >= 0 のようなものになります。

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

ROLLBACK;

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

SELECT * FROM users;

期待される出力：

1|Alice|40.0
2|Bob|260.0

これは、ROLLBACK が失敗したトランザクションの前にデータベースがもとの状態に戻ったことを確認しています。

まとめ

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