sql_マスタテーブルとトランザクションテーブル

09マスタテーブルとトランザクションテーブル

マスタテーブル

マスタテーブルは、データベース内で低頻度で更新される基礎的なデータを保持するテーブルです。これらのテーブルは、システム全体で参照される重要なデータを保持しており、他のテーブルとリレーションシップを形成するための基盤となります。

使用例

教科マスタテーブル（subject_master）：学校の授業となる教科の一覧
部活動マスタテーブル (club_master)：部活動の一覧
教室マスタテーブル (classroom_master)：学校内の全教室の一覧

トランザクションテーブル

トランザクションテーブルは、データベース内で頻繁に発生するイベントやアクティビティを記録するテーブルです。これらのテーブルは動的なデータを保持し、時間の経過とともに多くの新しいレコードが追加されます。

使用例

授業登録トランザクションテーブル (class_enrollments)：各学生がどの授業に登録しているか
成績トランザクションテーブル (grades)：各学生の成績一覧
出席トランザクションテーブル (attendances)：各授業における、参加学生の一覧

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

トランザクションとは

トランザクションは、データベース内で一連の操作を一つの単位として実行することを指します。トランザクションはACID特性（Atomicity, Consistency, Isolation, Durability）を持ち、これによりデータの整合性が保たれます。

トランザクションのACID特性

Atomicity（原子性）: トランザクション内のすべての操作が完了するか、全てが取り消されることを保証します。
Consistency（一貫性）: トランザクションが完了すると、データベースは一貫性のある状態になります。
Isolation（独立性）: 同時に実行されるトランザクションが互いに干渉しないようにします。
Durability（永続性）: トランザクションが完了すると、その結果は永続的に保存されます。

トランザクションの開始からコミットまでの流れ

では、実際にトランザクションの開始から完了までを行ってみましょう。

1．トランザクション開始

まず、トランザクションを開始します。

start transaction;

2．学生の登録

次に、新しい学生をstudentsテーブルに登録します。

insert into school.students
   (name, age, class)
values
   ('林田六郎', 8, 3);

3．登録できているかを確認

2で実行したSQLが反映されているかを確認します。

select * from students;

以下のように、「林田六郎」さんが追加されている状態になっているはずです。

4．変更をコミット（永続化）

ここまでの操作が成功していることが分かったので、トランザクションをコミットして変更を確定します。

commit;

トランザクション開始後の変更を取り消す（ロールバック）

何らかの理由でトランザクション内の操作が失敗した場合、ロールバックを行い、すべての変更を取り消します。

１．トランザクション開始

まず、トランザクションを開始します。

2．学生の登録

次に、新しい学生をstudentsテーブルに登録します。

insert into school.students
   (name, age, class)
values
   ('河田七郎', 9, 2);

3．登録できているかを確認

2で実行したSQLが反映されているかを確認します。

select * from students;

以下のように、「河田七郎」さんが追加されている状態になっているはずです。

4．変更の取り消し（ロールバック）

しかし、「河田七郎」さんは本来追加すべき人ではなく、誤って追加してしまったとします。

その場合は、変更の取り消しを実施します。

rollback;

5．変更が取り消されているかを確認

select * from students;

トランザクション内での変更は取り消されていることが確認できました。

トランザクションの利用シーン

実務では、以下のような場合にトランザクションを使用します。

本番環境のDBに対して、何らかのSQLを実行する際

ユーザの情報が含まれている本番環境のDBで、誤ったSQLを実行すると取り返しのつかない事態になる恐れがあります。ゆえに、トランザクションを用いて、データが期待通りに登録されているかを確認しつつ作業します。

複数のテーブルに整合性を保った状態でデータを保存したい場合

これは主にプログラムから実行することが想定されます。

例えば、学生Aとその成績を登録したい場合を考えます。学生Aを登録した後にプログラム上で何らかのエラーが発生した場合、プログラムからデータベースに対してロールバックを実行し、学生Aの登録も取り消します。これにより、データの整合性が保たれます。

トランザクション分離レベル

トランザクション分離レベルは、データベースシステムにおいて、複数のトランザクションが同時に実行される際にデータの一貫性と整合性を保つための重要な概念です。MySQLでは以下の4つの分離レベルを設定することができます。

トランザクション分離レベルに関連する現象

具体的なトランザクション分離レベルの紹介に入る前に、各分離レベルで発生しうる問題を紹介します。

ダーティリード (Dirty Read)：未コミット済みのレコードの扱い

ダーティリードとは、あるトランザクションが他のトランザクションによってまだコミットされていないデータを読み取ることを指します。これにより、未コミットの変更がそのまま読み取られるため、データの一貫性が保たれません。

例）

ファジーリード (Fuzzy Read)：更新するレコードの扱い

ファジーリードとは、同じトランザクション内で同じデータを複数回読み取ったときに、他のトランザクションによってデータが変更されるため、異なる結果が返される現象を指します。

例）

ファントムリード (Phantom Read)：追加するレコードの扱い

ファントムリードとは、あるトランザクション内で同じクエリを複数回実行したときに、他のトランザクションによってデータの挿入または削除が行われるため、異なる結果が返される現象を指します。特に、範囲クエリで顕著に現れます。

例）

MySQLのトランザクション分離レベル

各分離レベルで発生しるう問題を把握したうえで、分離レベルと上記の問題の相互関係を見てみましょう。

MySQLには4つの分離レベルがあり、任意の分離レベルを設定することができます。（デフォルトはRepeatable Read）

どのようなケースで使い分ければよいのか、いかに列挙します。

Read Uncommitted

→高速な読み取りが重要で、一貫性がさほど重要でない場合

Read Committed

→ダーティリードを防ぎつつ、適度なパフォーマンスを維持したい場合

Repeatable Read

→一貫した読み取りを確保しつつ、トランザクション間の競合をある程度許容する場合

Serializable

→完全なデータ整合性が求められるが、パフォーマンスが低下しても問題ない場合

以上の内容から、分離レベルは整合性とパフォーマンスのトレードオフだということが分かります。ゆえに、プロジェクトに参画する場合は分離レベルのチェックを行い分離レベルを意識する必要がありますし、自らのプロジェクトを立ち上げる際には、プロジェクトの要件を考慮したうえで分離レベルを制定する必要があります。