| ACID 特性 | 説明 |
|---|---|
| Atomicity (原子性) | トランザクションの処理を分割しない (全て実行する/しない) |
| Consistency (一貫性) | トランザクション前後で制約を満たす |
| Isolation (分離性) | 他のトランザクションの影響を受けない |
| Durability (永続性) | トランザクションを COMMIT した結果が失われない |
本記事では、MySQL を例に説明をします。
| 関連記事：データベースの基礎知識編 | |||||
|---|---|---|---|---|---|
| 学習ロードマップ | |||||
|---|---|---|---|---|---|
Atomicity (原子性)
つまり、Atomicity は処理が途中で終わることを禁止する特性です。
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 の定義は曖昧ですが、本記事はデータベース制約について記載します。
(過去のトランザクションの結果を保証する方法は、Durability の記事で説明します。)
なお、制約内容はアプリの内容に依存するため、基本的にアプリ側の責任となります。
Consistency is one of the four guarantees that define ACID transactions; however, significant ambiguity exists about the nature of this guarantee. It is defined variously as:
https://en.wikipedia.org/wiki/Consistency_(database_systems)
- The guarantee that database constraints are not violated, particularly once a transaction commits.[2][3][4][5][6]
- The guarantee that any transactions started in the future necessarily see the effects of other transactions committed in the past.[7][8]
データベース側で 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 (分離性)
一般的なリード現象は次の 3 つです。
トランザクションが分離されているなら、右側は COMMIT するまで同じ値を返すべきである。
(右側は COMMIT するまで、左側のトランザクションの影響を受けないことが期待される)
リード現象の対策
リード現象を防ぐために、RDBMS には「MVCC」と「ロック」の２つの機能があります。
MVCC は、行に以下の３つのフィールドを追加することで実現します。
| フィールド | 説明 |
|---|---|
| DB_TRX_ID | 行を挿入/更新(削除)したトランザクションの識別子 |
| DB_ROLL_PTR | Undo レコードへのポインタ |
| DB_ROW_ID | 行の識別子 |
MVCC では、過去のスナップショット (変更前の行) を読み取ることで、リード現象を防ぎます。
先にロックが取得されている場合は、後続はロックの解除を待つ必要があります。
ロックの範囲
ロックの範囲 (LOCK_TYPE) には、行ロックと、テーブルロックが存在します。
ロックの種類
ロックの種類 (LOCK_MODE) には、次の2種類が存在します。
| ロックの種類 | 該当する SQL | 説明 |
|---|---|---|
| 共有ロック (S) | SELECT ... FOR SHARE | 読み取りのため |
| 排他ロック (X) | SELECT ... FOR UPDATE/INSERT/UPDATE/DELETE | 読み書きのため |
ロックの取得条件
すでに誰かがロックを取得している場合、後から取得するロックには、次の制限があります。
つまり、誰かが読み取り中のレコードは書き込めませんし、誰かが書き込み中のレコードは読み書きできません。(ロックが解放されるまで待つ必要があります)
これにより、リード現象を防ぎます。
ロック読み取りと一貫性読み取り
ロックの解除待ちが発生するため、ロックはなるべくしたくないです。
そのため、ロックを取得しない一貫性読み取り (SELECT 文) が存在します。
| 読み取りの種類 | SQL | MVCC 読み取り先 | ロック |
|---|---|---|---|
| 一貫性読み取り (Consistent Read) | SELECT | スナップショット | ロックしない |
| ロック読み取り (Locking Read) | SELECT ... FOR SHARE | 現在のテーブル | 共有ロック |
| SELECT ... FOR UPDATE | 現在のテーブル | 排他ロック |
一貫性読み取りでは、ロックの代わりに MVCC のスナップショットでリード現象を防ぎます。
分離レベル
トランザクションを並行で実行すると、他のトランザクションでリード現象が発生します。
一方で、パフォーマンスの観点から、トランザクションは可能な限り並行で実行したいです。
そのため、分離レベルを用いて、パフォーマンスと Isolation のバランスを決定します。
| 分離レベル | ダーティーリード | ノンリピータブルリード | ファントムリード |
|---|---|---|---|
| READ UNCOMMITTED | 発生する | 発生する | 発生する |
| READ COMMITTED | 発生しない | 発生する | 発生する |
| REPEATABLE READ | 発生しない | 発生しない | △※1 |
| SERIALIZABLE | 発生しない | 発生しない | 発生しない |
MySQL (InnoDB) の場合は、一貫性読み取りとロック読み取りの１種類だけだと「発生しない」。組み合わせた場合は「発生する」
一貫性読み取りは MVCC, ロック読み取りはギャップロック/ネクストキーロックが防ぐ
なお、分離レベルは以下のグローバル変数・セッション変数で確認可能です。
+-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+------------------------+
SERIALIZABLE は、全てのリード現象を防ぎます。
MySQL (InnoDB) の SERIALIZABLE では、すべての SELECT 文で共有ロックを取得 (SELECT ... FOR SHARE に変換) することで 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は独自のトランザクションです。
REPEATABLE READ は、ANSI の定義上ファントムリードが発生します。
しかし、MySQL (InnoDB) の場合、「一貫性読み取り」と「ロック読み取り」が混在しない場合は、ファントムリードが発生しません。(混在する場合は発生します)
| 読み取りの種類 | SQL | ファントムリードを防ぐ方法 |
|---|---|---|
| 一貫性読み取り (Consistent Read) | SELECT | 最初に SELECT を実行した時の MVCC のスナップショット |
| ロック読み取り (Locking Read) | SELECT ... FOR SHARE | 共有ロック (ギャップロック/ネクストキーロック) |
| SELECT ... FOR UPDATE | 排他ロック (ギャップロック/ネクストキーロック) |
READ COMMITTED では、ファントムリードとノンリピータブルリードが発生します。
※MySQL の READ COMMITTED では、以下の理由でファントムリードが発生
・一貫性読み取りでは、常に最新のスナップショットを取得するため
・ロック読み取りでは、ギャップロックを行わないため
ファントムリードの動作
+------+
| id |
+------+
| 1 |
+------+
Empty set (0.00 sec)
+------+
| id |
+------+
| 1 |
+------+
ノンリピータブルリードの動作
+------+
| id |
+------+
| 2 |
+------+
+------+ | id | +------+ | 1 | +------+
+------+
| id |
+------+
| 2 |
+------+
READ UNCOMMITTED では、ファントムリードとノンリピータブルリードに加えて、ダーティリードが発生します。
ダーティリードの動作
+------+ | id | +------+ | 1 | +------+
+------+
| id |
+------+
| 1 |
+------+
Durability (永続性)
トランザクション処理を COMMIT した結果が失われる状況は、主に次の２種類が考えられます。
| 障害の種類 | 説明 | 対策 |
|---|---|---|
| システム障害 | OS や電源の障害等で、メモリ上のデータが消失 | ロールフォワード - WAL (Redo ログ) - チェックポイント ロールバック - ロールバックセグメント ダブルライトバッファ |
| メディア障害 | ストレージの破損等で、メディア上のデータが消失 | バックアップ Point-in-Time リカバリ レプリケーション |
なお、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
システム障害への対策
システム障害への対策で基本的な考え方は、ストレージにデータを保存することです。
(電源が落ちるとメモリ上のデータは消えますが、ストレージ上のデータは残るため)
しかし、パフォーマンスの観点から、高速に読み書き可能なメモリにデータを置きたいです。
このデータの保存とパフォーマンスをうまくやりとりするのが RDBMS の役割です。
メモリとストレージが持つデータ
RDBMS におけるメモリとストレージで保持するデータは、以下のとおりです。
| 用語 | 説明 |
|---|---|
| バッファプール ※1 | テーブルとインデックスなどを保持するメモリ上のキャッシュ |
| データファイル | テーブルとインデックスなどを保持するストレージ上のファイル |
| ログバッファ ※2 | トランザクションの API コールを記録したメモリ上のバッファ |
| Redo ログ ※3 | トランザクションの API コールを記録したストレージ上のファイル |
| フラッシュ | メモリからストレージに書き込むこと |
| ページ | ・メモリを一定サイズに分割した単位 ・メモリ⇆ストレージ間で、一度に転送できるデータ量 ・複数の行データを格納可能 ・LRU アルゴリズムで使用頻度の低いページは削除 |
| ダーティページ | 更新したが、まだデータファイルに書き込まれていないページ |
※2 PostgreSQL ではWAL バッファ、Oracle ではログバッファと呼ぶ
※3 PostgreSQL WAL ログ、Oracle ではトランザクションログと呼ぶ。MySQL では ib_logfileN が実態。
MySQL (innodb) の対応するアーキテクチャ
PostgreSQL の対応するアーキテクチャ
上記を踏まえて、システム障害から復旧するクラッシュリカバリについて解説します。
クラッシュリカバリ
クラッシュリカバリは、システム障害から復旧する方法で、主に次の要素で構成されます。
- Redo フェーズ (ロールフォワード)
- Undo フェーズ (ロールフォワード)
- ロールバックセグメント
- Change buffer マージ ※本記事では未解説
- Purge 処理 ※本記事では未解説
また、クラッシュリカバリは、ダブルライトバッファで書き込み途中のクラッシュも検知します。
事前にロギングを行う仕組みは全てWALと呼ばれる。MySQLで言えばInnoDBにおいてRedoログとして利用されるログもWALであるし、MySQL Clusterのディスクテーブル利用時のUndoログだってWALの一種である。
https://nippondanji.blogspot.com/2009/03/wal.html
なお、Redo ログへの書き込みが完了した時点で、COMMIT が成功した通知をクライアントに返します。(データファイルには、まだ書き込みをしてません。バイナリログも COMMIT で更新)
WAL によるシステム障害への対策
WAL でデータファイルより先にログへ書き込むことにより、システム障害を次のように防ぎます。
| システム障害のタイミング | 対応 |
|---|---|
| Redo ログに書き込み中 | Redo の内容を捨てる データファイルにまだ書き込んでないので問題なし |
| データファイル書き込み中 | Redo ログから COMMIT した内容をデータファイルに復元 (ロールフォワードと言います) |
WAL によるパフォーマンスの向上
WAL は、システム障害への対策だけではなく、COMMIT 時のパフォーマンスが良くなります。
(最後、データファイルにフラッシュする際のコストは別)
| 種類 | Redo ログ | データファイル |
|---|---|---|
| アクセス | シーケンシャルアクセス (トランザクション操作を追記するだけ) | ランダムアクセスが多い (実データを更新するため) |
| 書き込み箇所 | 少ない (ログファイルのみ) | 多い (変更した全てのデータファイル) |
| 速度 | 早い | 遅い |
これにより、メモリからストレージにページが書き出され、永続化します。
COMMIT は Redo ログに書き込まれるだけで、チェックポイント処理 (データファイルへのフラッシュ) は別で行います
この時、Redo ログに LSN (ログシーケンス番号) を書き込みます。
上記の場合、LSN=1 までの変更がデータファイルに書き込み完了していることがわかります。
ここでシステム障害が発生した場合、Redo ログの LSN:2 をデータファイルに適用します。
動作確認
CREATE PROCEDURE insert_loop(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
以下の内容が確認できます。
- LSN (Log sequence number) の値
- ログバッファは最新情報
- Redo ログの書き込みは、ログバッファより少し遅れている (COMMIT まで書き込まない)
- バッファプールにあるダーティページは最新の LSN
- Redo ログに先、データファイルに後に書き込んでいる (WAL)
- チェックポイントレコードは、データファイルにフラッシュした最新の LSN
なお、ダーティページの数は以下のコマンドで確認できます。
+--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | Innodb_buffer_pool_pages_dirty | 452 | +--------------------------------+-------+
ダーティページは、チェックポイントで定期的にデータファイルへフラッシュされます。
※チェックポイントを実施するタイミングはこちら
COMMIT を行う前にシステム障害が発生すると、データファイルから COMMIT 前のデータをロールバックする (更新前のデータに戻す) 必要があります。
ロールバックを実現するために、ロールバックセグメントに更新前のデータを持ちます。
(なお、更新前のデータは MVCC でも利用します)
※ダブルライトバッファはストレージにある小さなファイル (2MB 程度) です。
ダブルライトバッファに書き込む理由はデータファイルのデータを保護するためです。
データファイルの書き込み途中でシステム障害が発生すると、Redo ログでも復旧できません。
(壊れたデータに、Redo ログのトランザクション操作をしてもデータが復元できない)
そこで、ダブルライトバッファを使って、以下のようにデータファイルを保護します。
| システム障害のタイミング | 対処 |
|---|---|
| ダブルライトバッファの書き込み中 | ダブルライトバッファを捨てるだけ (データファイルには、まだ書き込んでないので問題なし) |
| データファイルへの書き込み中 | ダブルライトバッファからデータファイルを復元 |
関連記事
| 学習ロードマップ | |||||
|---|---|---|---|---|---|
| 関連記事：データベースの基礎知識編 | |||||
|---|---|---|---|---|---|
| 関連記事：データベース設計 | |||||
|---|---|---|---|---|---|
参考記事
