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

はじめに

この実験では、MySQL のトランザクションと、データの整合性を維持する上でのその重要性を探ります。一連の SQL 操作を単一の作業単位として扱い、すべてが成功するか、またはすべてが一緒に失敗するようにするためにトランザクションを使用する方法を学びます。

この実験では、BEGIN でトランザクションを開始し、データを挿入してトランザクションをコミットして変更を永続化し、失敗した更新をロールバックして変更を破棄し、トランザクション内で部分的なロールバックを行うためのセーブポイントを使用する方法をカバーしています。MySQL サーバーに接続し、データベースとテーブルを作成し、そしてこれらのトランザクション関連の操作を練習します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/update("Data Update") subgraph Lab Skills mysql/use_database -.-> lab-550918{{"MySQL トランザクションとデータ整合性"}} mysql/create_database -.-> lab-550918{{"MySQL トランザクションとデータ整合性"}} mysql/create_table -.-> lab-550918{{"MySQL トランザクションとデータ整合性"}} mysql/select -.-> lab-550918{{"MySQL トランザクションとデータ整合性"}} mysql/insert -.-> lab-550918{{"MySQL トランザクションとデータ整合性"}} mysql/update -.-> lab-550918{{"MySQL トランザクションとデータ整合性"}} end

BEGIN でトランザクションを開始する

このステップでは、BEGIN 文を使用して MySQL でトランザクションを開始する方法を学びます。トランザクションはデータベース管理における基本的な概念で、データの整合性と一貫性を保証します。トランザクションは、1 つ以上の SQL 操作のシーケンスで、単一の作業単位として扱われます。トランザクション内のすべての操作が成功するか、またはどれも成功しないかのいずれかです。

実践的な部分に入る前に、トランザクションが重要な理由を理解しましょう。あなたがある銀行口座から別の銀行口座にお金を振り込むシチュエーションを想像してみてください。これには 2 つの操作が関係します。送金者の口座から金額を引き落とすことと、受取人の口座に金額を入金することです。最初の操作が成功したが 2 番目の操作が失敗した場合（例えば、システムクラッシュによる）、お金が失われてしまいます。トランザクションは、両方の操作が一緒に成功するか、一緒に失敗するかを保証することで、このような不整合を防ぎます。

MySQL でトランザクションを開始するには、BEGIN 文（またはその別名 START TRANSACTION）を使用します。この文は新しいトランザクションの開始を示します。それ以降の SQL 文は、COMMIT で変更を永続化するか、ROLLBACK で変更を破棄するまで、このトランザクションの一部となります。

まず、MySQL サーバーに接続しましょう。ターミナルを開き、次のコマンドを実行します。

mysql -u root -p

root パスワードの入力を求められます。MySQL インストール時に設定したパスワードを入力してください。パスワードを設定していない場合は、Enter キーを押してください。

これで MySQL サーバーに接続したので、作業するデータベースとテーブルを作成しましょう。次の SQL 文を実行します。

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;
CREATE TABLE IF NOT EXISTS accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_number VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

これらのコマンドは、labex_db という名前のデータベースと、そのデータベース内に accounts という名前のテーブルを作成します。accounts テーブルには、id、account_number、balance の 3 つの列があります。

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

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

では、トランザクションを開始しましょう。次の文を実行します。

BEGIN;

このコマンドは新しいトランザクションを開始します。それ以降の SQL 文は、このトランザクションの一部となります。

トランザクションが開始されたことを確認するには、簡単な更新文を実行できます。たとえば、口座 'ACC001' から 100 を引き落としましょう。

UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';

次に、'ACC001' の残高を確認しましょう。

SELECT * FROM accounts WHERE account_number = 'ACC001';

'ACC001' の残高が 900.00 に更新されていることがわかるはずです。ただし、これらの変更はまだ永続的ではありません。現在のトランザクション内でのみ表示されます。変更を永続化するには、次のステップで学ぶ COMMIT でトランザクションをコミットする必要があります。この時点で、コミットまたはロールバックせずに MySQL 接続を閉じると、変更は失われます。

データを挿入してトランザクションをコミットする

前のステップでは、BEGIN 文を使用してトランザクションを開始する方法を学びました。今回は、トランザクション内でデータを挿入し、その後トランザクションをコミットして変更を永続化する方法を学びます。

前のステップで中断したところから続けます。MySQL サーバーに接続したままで、トランザクションが進行中であるはずです。接続していない場合は、次のコマンドを使用して MySQL サーバーに再接続します。

mysql -u root -p

パスワードの入力を求められたら入力し、次に labex_db データベースを選択します。

USE labex_db;

トランザクションが進行中でない場合は、トランザクションを開始します。

BEGIN;

では、現在のトランザクション内で accounts テーブルに新しいレコードを挿入しましょう。

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

このコマンドは、口座番号 'ACC003' と残高 200.00 の新しい口座を挿入します。

データが（トランザクション内で）挿入されたことを確認するには、次のクエリを実行します。

SELECT * FROM accounts WHERE account_number = 'ACC003';

新しく挿入されたレコードが表示されるはずです。ただし、前に述べたように、これらの変更はまだ永続的ではありません。現在のトランザクション内でのみ表示されます。

変更を永続化するには、トランザクションを COMMIT する必要があります。COMMIT 文は、トランザクション中に行われたすべての変更をデータベースに保存します。次のコマンドを実行します。

COMMIT;

このコマンドはトランザクションをコミットし、挿入されたデータを永続化します。

変更がコミットされたことを確認するには、同じクエリを再度実行します。

SELECT * FROM accounts WHERE account_number = 'ACC003';

新しく挿入されたレコードがまだ表示されるはずです。これで、MySQL サーバーから切断して再接続しても、レコードはまだ存在します。

また、accounts テーブルからすべてのレコードを選択することで、変更を確認することもできます。

SELECT * FROM accounts;

'ACC001'、'ACC002'、'ACC003' の 3 つの口座とそれぞれの残高が表示されるはずです。'ACC001' の残高は 900.00 であるはずです（前のステップで更新文を実行し、ロールバックしていない場合）。

要するに、このステップでは、トランザクション内でデータを挿入し、その後トランザクションをコミットして変更を永続化する方法を示しました。COMMIT 文は、トランザクション内のすべての操作がデータベースに適用されることを保証するために重要です。

失敗した更新をロールバックする

このステップでは、トランザクション中に行われた変更を取り消すために、トランザクションをロールバックする方法を学びます。これは、エラーが発生した場合や、トランザクション内で行った変更が望ましくないと判断した場合に特に有用です。

前のステップから続けます。MySQL サーバーに接続したままであるはずです。接続していない場合は、次のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら入力し、次に labex_db データベースを選択します。

USE labex_db;

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

BEGIN;

失敗した更新をシミュレートしましょう。'ACC001' から 'ACC004' に 500 を振り込もうとしたが、'ACC004' が存在しないとします。まず、'ACC001' から 500 を引き落としてみましょう。

UPDATE accounts SET balance = balance - 500 WHERE account_number = 'ACC001';

次に、'ACC004' に 500 を追加してみましょう。

UPDATE accounts SET balance = balance + 500 WHERE account_number = 'ACC004';

この更新は、'ACC004' が存在しないため、おそらくどの行にも影響を与えません。影響を受けた行数を確認することで、これを検証できます。

SELECT ROW_COUNT();

結果は 0 であるはずで、これは更新された行がないことを示しています。

2 番目の更新が失敗したため（または、更新を妨げる制約がある場合には失敗するでしょう）、最初の更新も取り消したいと思います。これを行うには、トランザクションを ROLLBACK できます。ROLLBACK 文は、トランザクション中に行われたすべての変更を取り消し、トランザクション開始前のデータベースの状態に戻します。

次のコマンドを実行します。

ROLLBACK;

このコマンドはトランザクションをロールバックし、'ACC001' からの 500 の引き落としを取り消します。

ロールバックが成功したことを確認するには、'ACC001' の残高を確認します。

SELECT * FROM accounts WHERE account_number = 'ACC001';

'ACC001' の残高は元の値に戻っているはずです（最初から始めた場合は 1000、ステップ 1 で最初の更新のみをコミットした場合は 900）。

また、'ACC003' がまだ存在し、残高が 200.00 であることを確認することもできます（ステップ 2 を完了した場合）。

要するに、このステップでは、ROLLBACK 文を使用してトランザクションをロールバックする方法を示しました。これは、エラーを処理し、トランザクション内の操作が失敗した場合にデータの整合性を保証するために不可欠です。

セーブポイントを使用した部分的なロールバック

このステップでは、トランザクション内でセーブポイント (Savepoint) を使用して部分的なロールバックを行う方法を学びます。セーブポイントはトランザクション内のマーカーで、そのポイントまでロールバックすることができます。これにより、トランザクション全体ではなく、トランザクション中に行われた一部の変更のみを取り消すことができます。

前のステップから続けます。MySQL サーバーに接続したままであるはずです。接続していない場合は、次のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら入力し、次に labex_db データベースを選択します。

USE labex_db;

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

BEGIN;

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

SAVEPOINT savepoint1;

このコマンドは savepoint1 という名前のセーブポイントを作成します。このセーブポイント以降に行われた変更は、このポイントまでロールバックすることができます。

では、'ACC001' の残高を更新しましょう。

UPDATE accounts SET balance = balance - 200 WHERE account_number = 'ACC001';

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

SAVEPOINT savepoint2;

では、accounts テーブルに新しいレコードを挿入しましょう。

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

ここで、'ACC004' の挿入を取り消したいが、'ACC001' の更新は維持したいと決めたとします。savepoint2 までロールバックすることができます。

ROLLBACK TO savepoint2;

このコマンドは、トランザクションを savepoint2 が作成されたときの状態にロールバックし、実質的に 'ACC004' の挿入を取り消します。

savepoint2 までのロールバックが成功したことを確認するには、'ACC004' が存在するかどうかを確認します。

SELECT * FROM accounts WHERE account_number = 'ACC004';

このクエリは何も行を返さないはずで、これは 'ACC004' が削除されたことを示しています。

では、'ACC001' の残高を確認しましょう。

SELECT * FROM accounts WHERE account_number = 'ACC001';

'ACC001' の残高は更新されているはずです（200 減少しています）。

残りの変更（'ACC001' の更新）に満足したら、トランザクションをコミットすることができます。

COMMIT;

このコマンドはトランザクションをコミットし、'ACC001' の更新を永続化します。

要するに、このステップでは、トランザクション内でセーブポイントを使用して部分的なロールバックを行う方法を示しました。これにより、トランザクション管理においてより細かい制御が可能になり、特定の変更を取り消しながら他の変更を維持することができます。

まとめ

この実験 (Lab) では、MySQL トランザクションの基本と、データ整合性を維持する上でのその重要性を学びました。この実験では、まず BEGIN 文（または START TRANSACTION）を使用してトランザクションを開始する方法を示しました。次に、labex_db という名前のデータベースと、その中に accounts テーブルを作成し、その後のトランザクション操作の準備を行いました。

最初のステップでは、環境のセットアップとトランザクションを開始するための基本的な構文の理解に焦点を当て、後のステップで COMMIT および ROLLBACK 操作を探索してトランザクション内のデータ変更を管理するための基礎を築きました。