MySQL トランザクションとデータ整合性

はじめに

この実験 (Lab) では、MySQL のトランザクションを使用してデータの整合性を確保する方法を学びます。トランザクションは、一連の SQL 操作を単一の「すべて実行するか、すべて実行しないか」という単位にグループ化します。これは、すべてのステップが正常に完了する必要がある金融取引のようなタスクに不可欠です。

BEGIN でトランザクションを開始し、COMMIT で変更を永続化し、ROLLBACK で変更を取り消し、SAVEPOINT を使用して部分的なロールバックを実行する方法を練習します。この実験 (Lab) の終わりまでに、データベース操作を安全かつ確実に管理する方法を理解できるようになります。

データベースとテーブルのセットアップ

トランザクションを扱う前に、データベースとテーブルをセットアップする必要があります。このステップでは、bank_db という名前のデータベースと、銀行業務をシミュレートするための accounts テーブルを作成します。

まず、デスクトップからターミナルを開きます。

root ユーザーとして MySQL サーバーに接続します。この実験 (Lab) 環境では、root ユーザーにパスワードは不要です。

sudo mysql -u root

接続すると、MySQL プロンプト (mysql>) が表示され、SQL コマンドを実行できることを示します。

bank_db データベースを作成し、現在のセッションで使用するために切り替えます。IF NOT EXISTS 句は、データベースが既に存在する場合にエラーを防ぎます。

CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;

次に、accounts テーブルを作成します。このテーブルは、ID、口座番号、残高を含む口座情報を格納します。

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

これで、accounts テーブルに 2 つのサンプル口座を挿入します。これらが開始データとして使用されます。

INSERT INTO accounts (account_number, balance) VALUES
('ACC001', 1000.00),
('ACC002', 500.00);

テーブルとデータが正しく作成されたことを確認するために、次のクエリを実行します。

SELECT * FROM accounts;

挿入した 2 つの口座が表示されるはずです。出力は次のようになります。

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         | 1000.00 |
|  2 | ACC002         |  500.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

データベースとテーブルの準備ができたので、トランザクションについて学習する準備が整いました。

BEGIN と COMMIT を使用した成功トランザクションの作成

トランザクションは、単一の論理的な単位として実行される一連の操作です。変更を永続化するには、それらを COMMIT する必要があります。このステップでは、ACC001 から ACC002 への銀行送金を成功させるシミュレーションを行います。

前のステップから引き続き MySQL シェル内にいるはずです。

まず、BEGIN コマンドを使用して新しいトランザクションを開始します。後続のすべてのコマンドは、COMMIT または ROLLBACK するまで、このトランザクションの一部となります。

BEGIN;

次に、送金のための 2 つの操作を実行します。ACC001 から 100 ドルを引き落とし、ACC002 に 100 ドルを追加します。

UPDATE accounts SET balance = balance - 100.00 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100.00 WHERE account_number = 'ACC002';

この時点では、変更は現在のセッション内でのみ表示されます。口座の一時的な状態を確認できます。

SELECT * FROM accounts;

出力には更新された残高が表示されますが、まだ永続的に保存されていません。

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

両方の操作が成功したため、トランザクションをコミットすることで変更を永続化できます。

COMMIT;

COMMIT コマンドは、トランザクション中に加えられたすべての変更を保存します。これで送金は完了し、他のすべてのデータベース接続から表示できるようになります。テーブルを再度クエリすることで、これを検証できます。残高は 900 ドルと 600 ドルのままです。

ROLLBACK によるトランザクションの取り消し

トランザクション内の操作が失敗した場合や、キャンセルを決定した場合などがあります。ROLLBACK コマンドは、現在のトランザクション中に行われたすべての変更を破棄し、データベースをトランザクション開始前の状態に戻します。

新しい口座を追加するための新しいトランザクションを開始しましょう。

BEGIN;

次に、accounts テーブルに新しい口座 ACC003 を挿入します。

INSERT INTO accounts (account_number, balance) VALUES ('ACC003', 150.00);

トランザクション内の新しい口座を確認するためにテーブルをチェックします。

SELECT * FROM accounts;

3 つの口座がリストされているのが表示されます。

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
|  3 | ACC003         |  150.00 |
+----+----------------+---------+
3 rows in set (0.00 sec)

ここで、この口座は追加されるべきではなかったことに気づいたとします。コミットする代わりに、ROLLBACK を使用してトランザクション全体をキャンセルできます。

ROLLBACK;

このコマンドは INSERT 操作を取り消します。これを検証するために、再度 accounts テーブルをクエリします。

SELECT * FROM accounts;

ACC003 口座は消えており、テーブルは BEGIN を入力する前の状態に戻っています。

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

ROLLBACK は、エラーが発生した場合のデータ整合性を維持するための重要なツールです。

SAVEPOINT を使用した部分的なロールバック

長いトランザクションでは、トランザクション全体ではなく、一部の作業のみを取り消したい場合があります。SAVEPOINT を使用すると、トランザクション内にマーカーを設定し、後でそのマーカーまでロールバックできます。

新しいトランザクションを開始しましょう。

BEGIN;

まず、ACC001 の残高を更新し、50 ドル増やします。

UPDATE accounts SET balance = balance + 50.00 WHERE account_number = 'ACC001';

次に、セーブポイントを作成します。これはトランザクションの現在の状態のブックマークとして機能します。

SAVEPOINT after_update;

次に、別の操作を実行します。新しい口座 ACC004 を挿入します。

INSERT INTO accounts (account_number, balance) VALUES ('ACC004', 300.00);

ACC004 の挿入は間違いであったが、ACC001 の更新は正しかったと仮定します。セーブポイントまでロールバックすることで、INSERT ステートメントのみを取り消すことができます。

ROLLBACK TO after_update;

このコマンドは、after_update セーブポイントを作成した時点の状態にトランザクションを戻します。ACC004 の INSERT は取り消されますが、ACC001 の UPDATE は維持されます。

確認するためにテーブルをクエリします。ACC004 は消えているはずですが、ACC001 の残高は更新されているはずです。

SELECT * FROM accounts;

出力には、残高が 950.00 ドルの ACC001 が表示され、ACC004 は表示されないはずです。

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  950.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

ACC001 の更新を保持したいので、トランザクションの残りの部分をコミットできます。

COMMIT;

セーブポイントを使用して部分的なロールバックを正常に実行しました。これで MySQL シェルを終了できます。

exit;

まとめ

この実験では、データ整合性を維持するための MySQL トランザクションの基本を学びました。トランザクションの開始 (BEGIN) から、完了 (COMMIT) またはキャンセル (ROLLBACK) まで、トランザクションの完全なライフサイクルを実践しました。

銀行振込をシミュレートし、複数の操作が単一の原子的な単位として扱われることを確認しました。また、SAVEPOINT を使用して、部分的なロールバックを可能にすることで、複雑なトランザクションをより細かく制御する方法を学びました。これらのスキルは、データの整合性が最重要視される、堅牢で信頼性の高いデータベースアプリケーションを開発する上で不可欠です。