【データベース】「SQLの実践」簡単速習‼【④トランザクション・ロック/MySQL】

こんにちはヤク学長です。
データサイエンティスト兼ファーマシストで、アルゴリズムやBI開発を行っています。

本記事の目的は、「SQLの基本操作を知る」ことを目的としています。

【本記事のもくじ】

まず、「SQL」に真剣に取り組むための概要を解説します。
下記の方法で、簡単に概要を抑えることができます。

• ７．トランザクション

それでは、上から順番に見ていきます。
なお、本上記の方法を順番に抑えれば成果が出ます。

記事の内容は「転載 & 引用OK」問題ありません。

７．トランザクション

データを管理の重要性

データベース（DB）は、正確にデータを管理する必要があります。これは、データベースが様々な目的に使用されるためです。例えば、ビジネスアプリケーションにおいては、データベースが正確でなければ、誤った決定が下されたり、不正確な情報に基づいた戦略を立てることになります。また、金融業界においては、正確なデータが必要不可欠であり、誤ったデータが渡されることは、重大な結果を引き起こす可能性があります。

データベースは、データの正確性を保証するために、様々な機能や手段を備えています。例えば、データベースには、データの整合性を維持するための制約（コンストレイント）があります。これにより、データベース内のデータが予め定義されたルールに従って格納され、データの不整合や矛盾を防止することができます。また、データベースには、トランザクション処理があります。トランザクションは、複数のデータベース操作をまとめて処理することができ、その処理が完了するまで、一時的にデータを保留することができます。これにより、データの整合性を維持することができます。

以上のように、データベースは、正確にデータを管理する必要があるため、データの整合性を維持するための様々な手段を備えています。これにより、データベースを用いた様々なアプリケーションや業務において、正確な情報が提供されることが保証されます。

トランザクション

トランザクションとは、データベースにおいて、複数の処理をまとめた一連の作業の単位のことです。トランザクションには、以下のような特徴があります。

• 原子性（Atomicity）：トランザクションは、一連の処理が全て正常に完了するか、あるいは何も処理されなかった状態のどちらかになるまで、中断することができません。つまり、トランザクションに含まれるすべての処理が完了するか、もしくは何も処理されなかった状態になるまで、途中で中断することはできません。
• 一貫性（Consistency）：トランザクションの処理が完了すると、データベースは一貫性のある状態になります。つまり、トランザクションを実行する前と後で、データベースの状態に矛盾が生じることはありません。
• 分離性（Isolation）：複数のトランザクションが同時に実行された場合でも、トランザクション同士が互いに影響しないように処理されます。つまり、あるトランザクションの処理が他のトランザクションに影響を与えることはありません。
• 持続性（Durability）：トランザクションが完了した後、その処理結果は永続的にデータベースに保存されます。つまり、データベースの障害やシステムの再起動などが発生しても、トランザクションが完了した結果は保証されます。

トランザクションは、データベースの整合性を保つために重要な役割を果たしています。トランザクションを利用することで、複数の処理が同時に実行されても、データベースの整合性を保つことができます。また、トランザクションは、アプリケーションにおいて不正な操作が行われた場合や、システムの障害などが発生した場合に、データの一貫性を維持するための手段としても利用されます。

MySQLでのトランザクション

MySQLにおいて、トランザクションは、複数のSQLコマンドを一連の処理として扱う方法の1つです。トランザクションを利用することで、一連のSQLコマンドが全て正常に実行されるか、もしくは全く実行されない状態になるまで、途中で中断することができます。

MySQLにおいて、トランザクションを開始するためには、BEGINまたはSTART TRANSACTIONコマンドを使用します。トランザクションの終了は、COMMITまたはROLLBACKコマンドを使用します。COMMITコマンドは、トランザクション内で実行された全てのSQLコマンドを確定し、データベースに永続的に保存します。一方、ROLLBACKコマンドは、トランザクション内で実行された全てのSQLコマンドを取り消し、データベースをトランザクション開始前の状態に戻します。

以下は、トランザクションの例です。

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

この例では、2つのアカウントの残高を更新するトランザクションが開始されています。最初のSQLコマンドでは、アカウント1の残高から100を引きます。次のSQLコマンドでは、アカウント2の残高に100を加えます。最後に、COMMITコマンドを使用して、トランザクションを確定します。トランザクション内で実行された全てのSQLコマンドが正常に実行された場合、このトランザクションによって、アカウント1の残高が減り、アカウント2の残高が増えます。

しかし、トランザクション中にエラーが発生した場合は、ROLLBACKコマンドを使用してトランザクションを取り消し、トランザクション開始前の状態に戻します。これによって、データベースの整合性を保つことができます。

SET autocommit=0;

-- テーブル作成
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance INT
);

-- テストデータの挿入
INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 200);

-- トランザクション開始
START TRANSACTION;

-- アカウント1の残高を更新し、共有ロックを取得する
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;

-- アカウント2の残高を更新し、排他ロックを取得する
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

-- トランザクションを確定する
COMMIT;

LOCK TABLES table_name [AS alias_name] lock_type [, table_name [AS alias_name] lock_type] ...;