はじめに
この実験では、データ整合性を確保する上で重要な PostgreSQL のトランザクション管理について探求します。一連の操作を単一の作業単位として扱う、トランザクションの開始とコミットの方法を学びます。また、失敗したトランザクションのロールバック、分離レベルの設定、同時更新によるロックのシミュレーションについても学びます。
トランザクションの開始とコミット
このステップでは、PostgreSQL でトランザクションを開始し、コミットする方法を学びます。トランザクションは、一連の操作を単一の作業単位として扱うことでデータ整合性を確保します。トランザクション内のいずれかの操作が失敗した場合、トランザクション全体がロールバックされ、部分的な更新を防ぎ、整合性を維持します。
最初に、postgres ユーザーとして PostgreSQL データベースに接続します。ターミナルを開き、以下のコマンドを使用します。
sudo -u postgres psql
postgres=# プロンプトが表示されるはずです。
次に、トランザクションのデモを行うために、accounts というテーブルを作成します。
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2)
);
accounts テーブルに初期データを入力します。
INSERT INTO accounts (name, balance) VALUES ('Alice', 100.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 50.00);
次に、BEGIN コマンドを使用してトランザクションを開始します。
BEGIN;
トランザクション内で、Alice の口座から Bob の口座に 20 ドルを転送します。
UPDATE accounts SET balance = balance - 20.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 20.00 WHERE name = 'Bob';
これらの変更を永続化するには、COMMIT コマンドを使用してトランザクションをコミットします。
COMMIT;
トランザクションが成功したことを確認するために、accounts テーブルをクエリします。
SELECT * FROM accounts;
Alice の残高が 20 ドル減少し、Bob の残高が 20 ドル増加していることが確認できるはずです。
最後に、psql シェルを終了します。
\q
失敗したトランザクションのロールバック
このステップでは、PostgreSQL で失敗したトランザクションをロールバックする方法を学びます。データベース操作の一連の流れでエラーが発生した場合、トランザクションをロールバックすることは、データベースを整合的な状態に保つために非常に重要です。
前のステップから PostgreSQL データベースに接続している必要があります。接続していない場合は、以下のコマンドを使用して再接続してください。
sudo -u postgres psql
新しいトランザクションを開始します。
BEGIN;
このトランザクション内で、意図的に失敗する操作を試みます。重複するプライマリキーを挿入してみましょう。まず、次の利用可能な id 値を見つけます。
SELECT MAX(id) FROM accounts;
結果が 2 であると仮定します。次に、既に存在する id = 1 の新しいアカウントを挿入しようとします。
INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);
このコマンドは、ERROR: duplicate key value violates unique constraint "accounts_pkey" エラーを引き起こします。
トランザクション内でエラーが発生したので、行われた変更を破棄するためにトランザクションをロールバックします。ROLLBACK コマンドを使用します。
ROLLBACK;
ROLLBACK が成功したことを確認するために、accounts テーブルをクエリします。
SELECT * FROM accounts;
テーブルには、ステップ 1 の終了時点での Alice と Bob のアカウントとその残高のみが含まれていることが確認できるはずです。失敗した INSERT 操作は正常にロールバックされました。
最後に、psql シェルを終了します。
\q
分離レベルの設定とテスト
このステップでは、PostgreSQL のトランザクション隔離レベルについて、それらをどのように設定し、テストするかを学びます。隔離レベルは、同時実行中のトランザクションが互いに隔離される度合いを制御します。より高い隔離レベルはデータ破損に対する保護を強化しますが、同時実行性を低下させる可能性があります。
前のステップから PostgreSQL データベースに接続している必要があります。接続していない場合は、以下のコマンドを使用して再接続してください。
sudo -u postgres psql
別々のターミナルウィンドウを 2 つ開きます。各ターミナルで、postgres ユーザーとして PostgreSQL データベースに接続します。2 つの postgres=# プロンプトが表示されるはずです。
ターミナル 1:
sudo -u postgres psql
ターミナル 2:
sudo -u postgres psql
ターミナル 1 で、隔離レベルを READ COMMITTED に設定します（これはデフォルトですが、デモのために明示的に設定します）。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
次に、トランザクションを開始します。
BEGIN;
ターミナル 1 で、Alice の残高を読み取ります。
SELECT balance FROM accounts WHERE name = 'Alice';
残高をメモします。次に、ターミナル 2 でトランザクションを開始し、Alice の残高を更新します。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;
ターミナル 1 で、Alice の残高をもう一度読み取ります。
SELECT balance FROM accounts WHERE name = 'Alice';
隔離レベルが READ COMMITTED なので、ターミナル 2 によってコミットされた更新後の残高 (90.00) が表示されます。
次に、REPEATABLE READ 隔離レベルをテストします。ターミナル 1 で、現在のトランザクションをロールバックし、隔離レベルを REPEATABLE READ に設定します。
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
ターミナル 1 で、Alice の残高をもう一度読み取ります。
SELECT balance FROM accounts WHERE name = 'Alice';
残高をメモします。次に、ターミナル 2 でトランザクションを開始し、Alice の残高を再び更新します。
BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;
ターミナル 1 で、Alice の残高をもう一度読み取ります。
SELECT balance FROM accounts WHERE name = 'Alice';
隔離レベルが REPEATABLE READ なので、ターミナル 2 が新しい値をコミットしたとしても、トランザクション開始時の元の残高が表示されます。
最後に、ターミナル 1 でトランザクションをコミットします。
COMMIT;
これで、ターミナル 1 で Alice の残高をもう一度読み取ると、最新のコミットされた値 (100.00) が表示されます。
両方の psql シェルを終了します。
\q
同時更新によるロックのシミュレーション
このステップでは、PostgreSQL で同時実行中の更新によるロックをシミュレートします。ロックは、同時実行中のトランザクションが互いに干渉するのを防ぎ、データの整合性を確保するために使用されるメカニズムです。
前のステップから PostgreSQL データベースに接続している必要があります。接続していない場合は、以下のコマンドを使用して再接続してください。
sudo -u postgres psql
別々のターミナルウィンドウを 2 つ開きます。各ターミナルで、postgres ユーザーとして PostgreSQL データベースに接続します。2 つの postgres=# プロンプトが表示されるはずです。
ターミナル 1:
sudo -u postgres psql
ターミナル 2:
sudo -u postgres psql
ターミナル 1 で、トランザクションを開始し、Alice の残高を更新します。重要なのは、SELECT ... FOR UPDATE を使用して行をロックすることです。
BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
このコマンドは、Alice の残高を取得し、その行をロックします。これにより、このトランザクションがコミットまたはロールバックされるまで、他のトランザクションがその行を変更することができなくなります。
ターミナル 2 で、トランザクションを開始し、Alice の残高を更新しようとします。
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';
ターミナル 2 のこのコマンドは、停止しているように見えます。これは、ターミナル 1 が保持しているロックが解放されるのを待っているためです。
次に、ターミナル 1 でトランザクションをコミットします。
COMMIT;
ターミナル 1 でトランザクションをコミットすると、ターミナル 2 の UPDATE コマンドが実行されます。
ターミナル 2 で、トランザクションをコミットします。
COMMIT;
これで、どちらのターミナルでも、accounts テーブルをクエリして変更を確認します。
SELECT * FROM accounts;
Alice の残高は、ターミナル 1 がロックを解放した後に、ターミナル 2 のトランザクションによって更新されていることが確認できるはずです。
最後に、両方の psql シェルを終了します。
\q
この例は、SELECT ... FOR UPDATE を使用してロックをシミュレートし、同時実行中の更新が互いに干渉するのを防ぐ方法を示しています。ロックがない場合、両方のトランザクションは同じ初期残高を読み取り、その値に基づいて更新を適用する可能性があり、更新の消失につながる可能性があります。
まとめ
この実験では、PostgreSQL でトランザクションを管理する方法を学びました。まず、psql を使用して PostgreSQL データベースに接続し、初期データを含むサンプルの accounts テーブルを作成しました。
次に、トランザクションの使用法を具体的に示しました。BEGIN コマンドを使用してトランザクションを開始し、複数のデータベース操作（Alice と Bob の残高の更新）を実行し、COMMIT コマンドを使用してトランザクションをコミットすることで変更を永続化しました。これにより、一連の操作を単一の原子的な単位として扱う、トランザクションの基本的な原則が示されました。また、トランザクションのロールバック方法と隔離レベルの設定方法も学びました。最後に、同時実行中の更新によるロックをシミュレートし、データ破損を防ぐ方法を理解しました。
