はじめに
私は、いつの日かMySQLのようなデータベースをゼロから自作したいと思っています。
データベースといえば、ただデータを保存するだけでなく、複数のユーザーが同時にアクセスしても壊れない堅牢さや、障害が起きてもデータが失われない信頼性が求められます。
今回は、MySQLにおけるトランザクションについて、処理の流れやメモリ上のデータを中心に知識を深めていきたいと思います。
トランザクションとは
トランザクションとは、複数のSQL操作をひとまとまりとして扱う仕組みです。
「全部成功（COMMIT）」か「全部なかったことにする（ROLLBACK）」かの二択で処理されます。
例えば、友達に1万円を送金するとき、裏側では2つの処理が走っています。
1. 自分の口座から1万円を引く
2. 友達の口座に1万円を足す
1の処理が成功して2の処理が失敗した場合、データの不整合が起きます。
トランザクションを使うと、2の処理が失敗した場合に、送金処理自体をなかったことにできます。
また、トランザクションは、ACID特性を保証している必要があります。
システムを使う上では当たり前ですが、この仕組みを1から作るのは結構大変ですね。
| 特性 | 説明 |
|---|---|
| Atomicity（原子性） | 全部成功 または 全部失敗。中途半端な状態にならない |
| Consistency（一貫性） | 処理前後でデータの整合性が保たれる |
| Isolation（独立性） | 他のトランザクションの途中状態が見えない |
| Durability（永続性） | COMMITしたデータは障害が起きても消えない |
PHPで単純なトランザクション処理を実装すると以下のような感じになります。
<?php
$dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4';
$pdo = new PDO($dsn, 'username', 'password');
try {
$pdo->beginTransaction(); // トランザクション開始
// 自分の口座から引く
$stmt = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE user_id = :id');
$stmt->execute([':amount' => 10000, ':id' => 1]);
// 友達の口座に足す
$stmt = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE user_id = :id');
$stmt->execute([':amount' => 10000, ':id' => 2]);
$pdo->commit(); // 両方成功したら確定
echo '送金完了';
} catch (Exception $e) {
$pdo->rollBack(); // エラーが起きたら全部戻す
echo '送金失敗: ' . $e->getMessage();
}
トランザクションデータはどこに保存されるのか
MySQLのInnoDBストレージエンジンでは、トランザクションのデータは、メモリ → ディスク（ログ）→ ディスク（実データ） という順序で保存されます。
① 実行中（メモリ）
BEGIN してから COMMIT / ROLLBACK するまでの間、変更は全部メモリ上に留まります。
| 保存場所 | 役割 |
|---|---|
| Buffer Pool | 読み込んだデータページ（※）をメモリ上にキャッシュし、変更があればディスクへの書き戻し前の状態のまま保持する |
| Undo Log | 変更前のデータを保存。(ROLLBACK時にここから元に戻す) |
| Redo Log Buffer | 操作ログをメモリ上に蓄積 |
※ データページとはディスク上のデータを固定サイズ（InnoDBでは16KB）のかたまりに分割した単位です。
② COMMIT時（ディスクへ書き込み）
実際の書き込みはこの順序で行われます。
- Redo Log（準備）
- Binary Log
- Redo Log（完了を記録）
- Tablespace（非同期で遅延書き込み）
| 保存場所 | ファイル | 役割 |
|---|---|---|
| Redo Log |
ib_logfile0 / ib_logfile1 （※） |
変更内容を実データより先にログへ記録する。 |
| Binary Log |
binlog.000001 等 |
レプリケーション・監査用。Redo Logとの整合性を保つため、準備 → 完了の2段階（2フェーズコミット）で書き込まれる |
| Tablespace | テーブル名.ibd |
実データの最終保存先。COMMITの時点では書き込まれず、Redo Logへの書き込み後に非同期でデータが反映される |
※ MySQL 8.4以降、Redo Logのファイル名の命名規則が ib_logfile0 から #ib_redo0 形式に変更されています。
MySQLを管理している人は見たことあるファイルなのではないでしょうか？私は、XAMPPを使っていたので記憶に残っています。
③ 障害発生時（クラッシュリカバリ）
MySQLが再起動すると、以下を自動で実行します。
- Redo Log を読み「コミット済みだが未反映」の操作を再実行
- Undo Log を読み「未コミットだった操作」を巻き戻し
- Tablespace が整合性のある状態に回復
メモリ上のデータを確認してみる
なんとなくですが、処理の流れが理解できたので実際にメモリ上の状態を確認してみます。
Buffer Pool の確認
Buffer Pool の使用状況は SHOW STATUS で確認できます。
SHOW STATUS LIKE 'Innodb_buffer_pool%';
主要な項目を抜粋すると、以下のような結果が得られます。
| 変数名 | 説明 |
|---|---|
Innodb_buffer_pool_pages_total |
Buffer Pool の総ページ数 |
Innodb_buffer_pool_pages_data |
データが入っているページ数 |
Innodb_buffer_pool_pages_dirty |
変更済みだがディスク未書き込みのページ数 |
Innodb_buffer_pool_pages_free |
空きページ数 |
Innodb_buffer_pool_read_requests |
読み込みリクエスト数 |
Innodb_buffer_pool_reads |
ディスクから実際に読んだ回数 |
Undo Log の確認
Undo Logの状態は SHOW ENGINE INNODB STATUS の出力か、information_schema テーブルから確認できます。
-- 現在アクティブなトランザクションとUndo Logのサイズを確認
SELECT
trx_id,
trx_state,
trx_started,
trx_rows_modified,
trx_rows_locked
FROM information_schema.INNODB_TRX;
| カラム名 | 説明 |
|---|---|
trx_id |
トランザクションID |
trx_state |
RUNNING / LOCK WAIT など現在の状態 |
trx_started |
トランザクション開始時刻 |
trx_rows_modified |
変更した行数（Undo Log の量に比例） |
trx_rows_locked |
ロックしている行数 |
長時間 RUNNING 状態のトランザクションがあると、そのぶん Undo Log が蓄積され続け、メモリの肥大化につながります。大量のデータをトランザクションで扱う場合は注意が必要です。
Redo Log Buffer の確認
Redo Log Buffer の状態は以下のクエリで確認できます。
SHOW STATUS LIKE 'Innodb_log%';
| 変数名 | 説明 |
|---|---|
Innodb_log_writes |
Redo Log への書き込み回数 |
Innodb_log_write_requests |
書き込みリクエスト数 |
Innodb_log_waits |
バッファが満杯でディスク書き込みを待った回数 |
おまけ
今回は真面目な内容になりすぎたので、余談をひとつ。
ポケモンの金銀バージョンではセーブ中に電源を切るとポケモンが増殖するというバグがありました。
内容は、
- ポケモンボックスに預ける
- ボックスを切り替えて自動セーブ中に電源を切る
- 再開すると、手持ちとボックスに同じポケモンが存在する状態になる
これは手持ちとボックスのセーブが別タイミングで行われる設計だったため、その途中で電源を切ると中途半端な状態が残ってしまったようです。これはまさに原子性（Atomicity） が保証されていない状態です。手持ちとボックスをまとめてひとつのトランザクションとして扱う設計であれば、このバグは防げたかもしれませんね。
しかし、ゲームボーイのROMは最大8MB程度、RAMはわずか8KBしかありません。トランザクションを実現するにはUndoログ用のバッファが必要になるため、当時のハードウェアでの実装は難しかったのかもしれませんね。
まとめ
今回は、MySQLのトランザクションの仕組みについて深掘りしました。しかし、トランザクションは奥が深く、今回はまだ序章にすぎません。
- セーブポイント
- トランザクションごとの保持（独立性）
- ログファイルのデータ構造
- 行ロック
など、トランザクションだけでも、まだまだ頭が痛くなることだらけです。
自作データベースへの道は遠いですね...
気が向いたら第二弾も書こうと思います。
ここまで読んでいただきありがとうございました！