本記事では、MySQL を例に説明しますが、他の RDBMS でも考え方は同じです。
関連記事:データベースの基礎知識 | |||
---|---|---|---|
Atomicity (原子性)
すべての操作を実行しない (ROLLBACK)
すべての操作を実行する (COMMIT)
つまり、Atomicity は処理が途中で終わることを禁止する特性です。
ROLLBACK はなんらかの問題が発生した場合に、トランザクション開始前の状態に戻します。
動作確認
以下の検証用テーブルを利用して、Atomicity (原子性) の動作を確認してみます。
+--------+---------+ | name | balance | +--------+---------+ | Taro | 2000 | | Hanako | 1000 | +--------+---------+
Taro から Hanako に 500 円を送金するために、以下のトランザクション処理をします。
- Taro の銀行口座から 500 円を減らす
- Hanako の銀行口座に 500 円を増やす
+--------+---------+
| name | balance |
+--------+---------+
| Taro | 1500 |
| Hanako | 1000 |
+--------+---------+
ここで障害が発生し、「2. Hanako の銀行口座に 500 円を増やす」が出来ない場合を考えます。
ROLLBACK を利用して、トランザクション開始前の状態に戻します。
+--------+---------+
| name | balance |
+--------+---------+
| Taro | 2000 |
| Hanako | 1000 |
+--------+---------+
ROLLBACK でトランザクション開始前の状態に戻るため、Atomicity を満たします。
動作確認
Taro から Hanako に 500 円を送金するために、以下のトランザクション処理をします。
- Taro の銀行口座から 500 円を減らす
- Hanako の銀行口座に 500 円を増やす
+--------+---------+ | name | balance | +--------+---------+ | Taro | 2000 | | Hanako | 1000 | +--------+---------+
+--------+---------+ | name | balance | +--------+---------+ | Taro | 1500 | | Hanako | 1500 | +--------+---------+
上記のトランザクションは COMMIT して確定いるため、ROLLBACK で戻すことはできません。
+--------+---------+ | name | balance | +--------+---------+ | Taro | 1500 | | Hanako | 1500 | +--------+---------+
全ての変更が確定しているため、Atomicity を満たします。
Consistency (一貫性)
Consistency はアプリケーションの内容に依存するため、アプリケーション側の責任となる。
データベース側で Consistency を保つ場合は、次のような、なんらかの制約を設定します。
- PRIMARY KEY (値が一意かつ NOT NULL)
- FOREIGN KEY (指定した他のテーブルの列に含まれる値のみを格納可能)
- 無効データ (特定の値を禁止)
- ENUM および SET (指定した値のみ格納可能)
トランザクションで制約を違反した場合、違反した SQL 文に対して ROLLBACK が発生します。
通常、データ変更ステートメント (
https://dev.mysql.com/doc/refman/8.0/ja/constraint-primary-key.htmlINSERT
やUPDATE
など) がプライマリキー、一意キー、または外部キーの制約に違反すると、エラーが発生します。InnoDB
などのトランザクションストレージエンジンを使用している場合、MySQL ではステートメントが自動的にロールバックされます。
動作確認
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
PRIMARY KEY が重複した場合は ROLLBACK するため、Consistency を満たしています。
+------------+------+ | account_id | name | +------------+------+ | 1 | hoge | +------------+------+
動作確認
foreign_account_id INT,
balance INT,
FOREIGN KEY (foreign_account_id) REFERENCES consistency1(account_id));
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`consistency2`, CONSTRAINT `consistency2_ibfk_1` FOREIGN KEY (`foreign_account_id`) REFERENCES `consistency1` (`account_id`))
consistency1 に存在しない会員 ID は ROLLBACK するため、Consistency を満たします。
Empty set (0.00 sec)
今回は UNSIGNED 属性 (負の整数を禁止) で検証します。
動作確認
ERROR 1264 (22003): Out of range value for column 'balance' at row 1
無効データの挿入を禁止して、Consistency を満たしています。
Empty set (0.00 sec)
動作確認
ERROR 1265 (01000): Data truncated for column 'grade' at row 1
デフォルトの SQL モードでは、ENUM に無い値はエラーとなり、Consistency を満たします。
Empty set (0.00 sec)
Isolation (分離性)
他のトランザクション処理の影響とは、次のリード現象を指します。
リード現象 (Read Phenomena)
トランザクションを分離しない場合に、発生するリード現象は次の 4 つです。
- ダーティリード
- ファジーリード
- ファントムリード
- ロストアップデート (ANSI/ISO standard SQL 92 では未定義)
トランザクションが分離されているなら、右側は COMMIT するまで同じ値を返すべきである。
(右側は COMMIT するまで、左側のトランザクションの影響を受けないことが期待される)
分離レベル
トランザクションを並行で実行すると、他のトランザクションでリード現象が発生します。
一方で、パフォーマンスの観点から、トランザクションを並行で実行したいです。
そのため、分離レベルで、パフォーマンスと Isolation のバランスを決定します。
分離レベル | ダーティーリード | ファジーリード | ファントムリード | ロストアップデート※1 |
---|---|---|---|---|
READ UNCOMMITTED | 発生する | 発生する | 発生する | 発生する |
READ COMMITTED | 発生しない | 発生する | 発生する | 発生する |
REPEATABLE READ | 発生しない | 発生しない | △※2 | 発生する |
SERIALIZABLE | 発生しない | 発生しない | 発生しない | 発生しない |
※2 ANSI では「発生する」。MySQL の InnoDB の場合は Next Key Lock という仕組みにより「発生しない」。(参考)
なお、分離レベルは、以下のグローバル変数・セッション変数で確認可能です。
+-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+------------------------+
InnoDB の SERIALIZABLE では、すべての SELECT ステートメントに共有ロックを自動で付与することで SERIALIZABLE を保証します。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html
SERIALIZABLE
このレベルはREPEATABLE READ
と似ていますが、autocommit
が無効になっている場合、InnoDB
はすべてのプレーンSELECT
ステートメントをSELECT ... FOR SHARE
に暗黙的に変換します。autocommit
が有効な場合、SELECT
は独自のトランザクションです。
Durability(永続性)
Durability はトランザクション処理を完了(COMMIT)した時点で、その結果が失われない性質のことです。
トランザクション処理を COMMIT した結果が失われる状況は、次の2種類が考えられます。
Durability は一般的に「システム障害」を対象とすることが多いようです。
Durability can be achieved by flushing the transaction's log records to non-volatile storage before acknowledging commitment.
https://en.wikipedia.org/wiki/Durability_(database_systems)
In addition, this recovery must be permanent, meaning all transactions must be reconstructed, even if the database server crashes due to OS failure or power loss.
https://www.techopedia.com/definition/27416/durability-databases
システム障害に対して Durability を実現するには、メモリ上にあるトランザクション処理の結果を、ストレージに書き終えるまで待つことです。
ストレージ障害に対する対策は、「バックアップを取る」・「レプリケーションを設定」することです。
チェックポイント
チェックポイントとは、メモリ上で更新したデータページの内容を、ストレージに書き込むことです。
Durability とパフォーマンスは以下のようにトレードオフの関係です。
RDBMS では、次のチェックポイントまで可能な限りメモリ上でデータページを更新することで、パフォーマンスを向上させます。(後述しますが、Durability は低下します。)
用語 | 説明 |
---|---|
バッファプール | メモリ上にあるテーブル、インデックスデータのキャッシュ |
テーブルスペース | バッファプールをストレージに書き出したデータ |
ダーティページ | バッファプールからテーブルスペースにまだ書き出してないページ |
チェックポイント | バッファプール上の全てのダーティページを、テーブルスペースに書き出すこと |
なお、各用語は RDBMS の種類に応じて以下のように読み替えてください。
MySQL | PostgreSQL | Oracle |
---|---|---|
(InnoDB)バッファプール | 共有バッファ | データベースバッファキャッシュ |
テーブルスペース(.ibd,ibdata1 等) | データファイル | データファイル |
動作確認
CREATE PROCEDURE insert_loop(max int) BEGIN DECLARE id INT DEFAULT 0; REPEAT SET id = id + 1; INSERT INTO durability VALUES(id); COMMIT; UNTIL id >= max END REPEAT; END//
+--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | Innodb_buffer_pool_pages_dirty | 0 | +--------------------------------+-------+
何もトランザクション処理をしていないため、ダーティページは0です。
ここから検証を始めます。
+--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | Innodb_buffer_pool_pages_dirty | 390 | +--------------------------------+-------+
INSERT するたびに COMMIT しているにも関わらず、ダーティページが増えていることが確認できます。(つまり、バッファプールに COMMIT したダーティページを貯め、一定期間後に一気にダーティページを書き出していることがわかります。)
この方法では、チェックポイントが完了した地点での Durability を実現します。
一方で、チェックポイントが完了する前にシステム障害が発生するとダーティページが消えるため、前回のチェックポイント以降に COMMIT した結果に対する Durability は実現できません。
WAL (Write Ahead Log)
WAL とは、「バッファプール-->テーブルスペース」の書き出しよりも先に、「ログバッファ-->REDO ログ」の書き出しを行う方法です。
WAL により、COMMIT した結果に対する Durability を実現します。
用語 | 説明 |
---|---|
ログバッファ | トランザクションの操作を順番に記録したメモリ上のログ |
REDO ログ(ib_logfile) | ログバッファをストレージに書き出したログ(トランザクションログと呼ばれる) |
LSN (Log Sequence number) | Redo ログに記録される操作に対応する番号 |
チェックポイントレコード | バッファプール上のダーティページが、どこまでテーブルスペースに書き込まれたか表す番号 (どの LSN に相当する操作が完了したダーティページが、テーブルスペースに書き出されたか) |
なお、データベースの種類によって各用語は以下のように読み替えてください。
MySQL 用語 | PostgreSQL 用語 | Oracle 用語 |
---|---|---|
ログバッファ | WAL バッファ | ログバッファ |
REDO ログ(ib_logfile) | WAL ログ | トランザクションログ |
以下の MySQL, PostgreSQL の構成図より、「バッファプール」・「テーブルスペース」・「ログバッファ」・「REDO ログ」の関係を確認してください。
WAL の動作は以下のとおりです。
WAL により、以下のように COMMIT したトランザクション処理の Durability を実現します。
- COMMIT 完了前にシステム障害発生:更新に失敗した REDO ログを捨てる
- COMMIT 完了後にシステム障害発生:REDO ログを利用して、前回のチェックポイントからの差分をテーブルスペースにリカバリ (クラッシュリカバリ)
また、テーブルスペースを直接更新せずに WAL を利用して先に REDO ログを更新すると以下のようなメリットがあります。
比較対象 | WAL (REDO ログを先に更新) | テーブルスペースに直接更新 |
---|---|---|
COMMIT 中の障害 | 更新に失敗した REDO ログを捨てる | 更新に失敗したテーブルスペースを捨てられない (実際のテーブルデータだから) |
アクセス速度 | シーケンシャルアクセスなので速い (LSN や更新レコード等を追記する) | ランダムアクセスなので遅い (実際のテーブルデータにアクセスし更新する) |
書き込み回数 | 少ない (REDO ログだけ) | 多い (更新するすべてのテーブルスペース) |
動作確認
CREATE PROCEDURE loop_insert2(max int) BEGIN DECLARE id INT DEFAULT 0; REPEAT SET id = id + 1; INSERT INTO durability VALUES(id); IF(MOD(id, 10000) = 0) THEN COMMIT; END IF; UNTIL id >= max END REPEAT; END//
ここから検証を始めます。
--- LOG --- Log sequence number 153604788 Log buffer assigned up to 153604788 Log buffer completed up to 153604788 Log written up to 153604028 Log flushed up to 153604028 Added dirty pages up to 153604788 Pages flushed up to 144025476 Last checkpoint at 144025476
Last checkpoint at(チェックポイントレコード)が、Log sequence number (LSN) より遅れていることがわかります。
つまり、先に「ログバッファ--> REDO」の書き込みが行われ、後から「バッファプール-->テーブルスペース」に書き込むことがわかります。
クラッシュリカバリとは
クラッシュリカバリとは、RDBMS がクラッシュ後に再起動すると、トランザクションを COMMIT した結果までテーブルスペースを復旧することです。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-recovery.html
InnoDB
のクラッシュリカバリは、次のいくつかのステップで構成されます。
・テーブルスペースの検出
・Redo log アプリケーション
・不完全な transactions の Roll back
・Change buffer マージ
・Purge
Redo ログを利用したロールフォワードは以下のように実施されます。
クラッシュ時
このときの各記録状況は以下のとおりです。
データ名 | 記録状況 |
---|---|
ログバッファ | メモリなのでデータは吹き飛びます |
バッファプール | メモリなのでデータは吹き飛びます |
REDO ログ | ストレージ上にあるので残ります。 中身は「変更操作」と「チェックポイントレコード」です |
テーブルスペース | ストレージ上にあるので、前回のチェックポイントまでの更新内容が残ります |
クラッシュリカバリ時
関連記事
ACID 特性に関する説明は以上となります。他のデータベース入門記事は以下のとおりです。
関連記事:データベースの基礎知識 | |||
---|---|---|---|