本記事は渡るデータベース入門記事のうち、第3回「トランザクションと ACID 特性」です。
本記事は以下の書籍を参考に執筆しています。
その他のデータベースの入門記事ついては以下の記事をご確認ください。
- 【データベース入門1】データベースとは
- 【データベース入門2】SQL コマンドとは、SQL 文の一覧
- 【データベース入門3】トランザクションと ACID 特性とは ←今ここ
- 【データベース入門4】バックアップ・クラスター・レプリケーション
- 【データベース入門5】テーブル設計・正規化
- 【データベース入門6】オプティマイザー・実行計画
ACID 特性とは
ACID とは、トランザクション処理(後述)において、信頼性を保証するために求められる次の4つの性質の頭文字です。
・Atomicity(原始性)
・Consistency(一貫性)
・Isolation(分離性)
・Durability(永続性)
トランザクション処理とは、以下のように BEGIN ~ COMMIT で複数の SQLコマンド を1つに纏めた処理のことです。
以降では、RDBMS として MySQL を例に ACID 特性を説明しますが、他のデータベースでも同様の考え方です。
Atomicity (原始性)
Atomicity は、すべてのトランザクション処理の実行結果が、以下のどちらかになるという性質です。
・トランザクションに含まれるすべての操作を実行する (COMMIT)
・トランザクションに含まれるすべての操作を実行しない (ROLLBACK)
以下の検証用テーブルを利用して、Atomicity (原始性) の動作を確認してみます。
すべての操作を実行しない (ROLLBACK)
「すべての操作を実行しない(ROLLBACK)」は、途中までしか行わなかったトランザクションの操作をすべてキャンセルします。
例えば、銀行口座 A から B に 500 円を送金する場合、以下の2つの操作をトランザクション処理として行います。
- 銀行口座 A の残高から 500 円を引く
- 銀行口座 B の残高に 500 円を加える
この時、2の操作が失敗した場合は、1の操作をキャンセル (ROLLBACK) する必要があります。
動作確認
+--------+---------+ | name | balance | +--------+---------+ | Taro | 2000 | | Hanako | 1000 | +--------+---------+
トランザクション処理で Taro から Hanako の銀行口座に送金するために、まずは Taro の銀行口座から 500 円を減らします。
+--------+---------+
| name | balance |
+--------+---------+
| Taro | 1500 |
| Hanako | 1000 |
+--------+---------+
ここで、障害が発生し Hanako の銀行口座に 500 円を増やせなかった場合を想定します。
障害が発生し、どこまで処理が成功したかわからない場合は ROLLBACK を利用してすべての操作をキャンセルします。
+--------+---------+
| name | balance |
+--------+---------+
| Taro | 2000 |
| Hanako | 1000 |
+--------+---------+
ROLLBACK した結果、トランザクション処理のすべての更新操作がキャンセルされるため、ACID 特性の Atomicity(原始性) を満たしていることがわかります。
すべての操作を実行する (COMMIT)
「すべての操作を実行する(COMMIT)」は、トランザクション処理を確定するために利用します。
動作確認
+--------+---------+ | name | balance | +--------+---------+ | Taro | 2000 | | Hanako | 1000 | +--------+---------+
トランザクション処理で以下の2つの操作を行い、Taro から Hanako の銀行口座に 500 円を送金します。
- Taro の銀行口座から 500 円を減らす
- Hanako の銀行口座に 500 円を増やす
+--------+---------+ | name | balance | +--------+---------+ | Taro | 1500 | | Hanako | 1500 | +--------+---------+
上記のトランザクション処理は COMMIT して確定いるため、ROLLBACK で戻すことはできません。
+--------+---------+ | name | balance | +--------+---------+ | Taro | 1500 | | Hanako | 1500 | +--------+---------+
もし、障害が発生しても COMMIT までの操作が確実に行われたことを保証します。つまり、Atomicity(原始性) を満たします。
Consistency (一貫性)
Consistency は、トランザクション前後で制約を満たす性質です。
MySQL で利用可能な制約は以下の4つです。
- PRIMARY KEY (値が一意かつ NOT NULL)
- FOREIGN KEY (指定した他のテーブルのカラムに含まれる値のみを格納可能)
- 無効データ (INT UNSIGNED にマイナスの値を入れる、NOT NULL に NULL を入れる等を禁止)
- ENUM および SET (あらかじめ指定した値以外を格納禁止)
トランザクション処理で制約を違反した場合、違反した SQL 文に対して ROLLBACK が発生します。
通常、データ変更ステートメント (
https://dev.mysql.com/doc/refman/5.6/ja/constraint-primary-key.htmlINSERT
やUPDATE
など) がプライマリキー、一意キー、または外部キーの制約に違反すると、エラーが発生します。InnoDB
などのトランザクションストレージエンジンを使用している場合、MySQL ではステートメントが自動的にロールバックされます。
PRIMARY KEY の制約
PRIMARY KEY の制約とは、PRIMARY KEY のカラムの値が一意かつ NULL では無い制約です。
動作確認
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
PRIMARY KEY に重複した値を入れると制約の違反となります。
+------------+------+ | account_id | name | +------------+------+ | 1 | hoge | +------------+------+
直前の「INSERT INTO consistency1 VALUES(1,'hoge');」までは実行され、違反した SQL 文のみが ROLLBACK されていることがわかります。つまりトランザクションは Consistency (一貫性) を満たします。
この状態でトランザクション自体を ROLLBACK することも可能です。
Query OK, 0 rows affected
FOREIGN KEY の制約
FOREIGN KEY の制約とは、指定した他のテーブルのカラムに含まれる値のみを格納可能な制約です。
動作確認
foreign_account_id INT,
name VARCHAR(20),
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 テーブルの account_id カラムに存在しない値を外部キー foreign_account_id にINSERT するとエラーとなり、SQL 文がロールバックされます。
つまりトランザクションは Consistency (一貫性) を満たします。
+--------------------+------+ | foreign_account_id | name | +--------------------+------+ | 1 | hoge | +--------------------+------+
FOREIGN KEY の制約を満たす場合は、そのままトランザクション処理を完了できます。
無効データの制約
無効データの制約とは、許容していないデータの格納を禁止する制約です。
許容する値の指定には様々な種類がありますが、今回は UNSIGNED 属性(負の整数を禁止)で検証します。
動作確認
ERROR 1264 (22003): Out of range value for column 'price' at row 1
Empty set (0.00 sec)
制約を違反する無効データ (UNSIGNED に対する負の整数) を INSERT する SQL 文を ROLLBACK することで Consistency (一貫性) を満たしていることを確認できます。
ENUM および SET の制約
ENUM および SET の制約とは、あらかじめ指定した値以外をカラムに格納できないようにする制約です。
動作確認
ERROR 1265 (01000): Data truncated for column 'level' at row 1
level カラムの ENUM では 'advanced' を指定していないので切り捨てられます。
Empty set (0.00 sec)
ENUM の制約を違反するデータは格納されていないため、Consistency (一貫性) を満たしています。
Isolation(分離性)
Isolation は、トランザクション処理が他のトランザクション処理の影響を受けない性質です。
Isolation(分離性)は、他のトランザクション処理の影響をどの程度許容するかによって、以下の4つの分離レベルに分かれます。
- READ UNCOMMITTED(COMMIT していない結果を読み取る = 分離無し)
- READ COMMITTED(COMMIT した最新の結果を常に読み取る)
- REPEATABLE READ(読み取り対象のデータを常に読み取る)
- SERIALIZABLE(直列化可能)
なお、分離レベルは以下のグローバル変数、セッション変数で確認可能です。
+-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+------------------------+
READ UNCOMMITTED
READ UNCOMMITTED とは、トランザクション処理が全く分離していない分離レベルです。
後述するダーティーリードが発生します。
ダーティーリード
ダーティーリードとは、COMMIT していない別のトランザクション処理の結果が見えてしまう現象です。
動作確認
+------+
| id |
+------+
| 1 |
+------+
ダーティーリードが発生していることを確認できます。(1つ目の端末でトランザクション処理をまだ COMMIT していないにも関わらず、2つ目の端末で1つ目の端末のトランザクション処理の結果が反映されています)
Empty set (0.00 sec)
Empty set (0.00 sec)
1つ目の端末のトランザクションをロールバックすると、2つ目の端末でダーティーリードが消えました。つまり全くトランザクションを分離出来ていないことがわかります。
READ COMMITTED
READ COMMITTED とは、他のトランザクションが COMMIT した結果だけを見ることが可能な分離レベルです。
READ COMMITTED は、後述するファジーリード (ノンリピータブルリード) が発生します。
ファジーリード (ノンリピータブルリード)
ファジーリード (ノンリピータブルリード) とは、別のトランザクションの影響により、同じ SELECT 文を実行した場合でも同じカラムの結果が異なる現象です。
動作確認
+------+
| id |
+------+
| 1 |
+------+
Empty set (0.00 sec)
2つ目の端末では、1つ目の端末のトランザクション処理の結果がまだ反映されていないことがわかります。そのため、分離レベルが READ COMMITTED の場合はダーティーリードを防げます。
+------+
| id |
+------+
| 1 |
+------+
2つ目の端末では、ファジーリード (ノンリピータブルリード)が発生しました。
(同一トランザクションで同じ SELECT 文を実行しているにも関わらず、別のトランザクションの影響で同じカラムの結果が異なっています)
REPEATABLE READ
REPEATABLE READ とは、同じトランザクション内で SELECT を実行すると、常に同じ結果となることが保証されている分離レベルです。
REPEATABLE READ では後述するファントムリードが発生します。※1
ファントムリード
ファントムリードとは、別のトランザクション追加、削除により、同じ SELECT 文を実行した場合でもカラムの数が異なる現象です。
※1 MySQL の InnoDB の RREPEATABLE READ では、Next Key Lock という仕組みでファントムリードは発生しません。その代わりに後述するロストアップデートが発生します。
ロストアップデート
ロストアップデートとは、後から更新したトランザクションの結果に上書きされる (前のトランザクションの更新が消える) 現象です。
動作確認
+------+
| id |
+------+
| 1 |
+------+
Empty set (0.00 sec)
Empty set (0.00 sec)
結果は1回目の SELECT 文と同じなので、ファジーリードを防いでいることがわかります。
+------+
| id |
+------+
| 1 |
+------+
ロストアップデートの確認
+------+
| @x |
+------+
| 1 |
+------+
+------+
| id |
+------+
| 100 |
+------+
+------+
| id |
+------+
| 2 |
+------+
ロストアップデートが発生し、2 (= 1 * 2) となってしまいました。
つまり、2つ目の端末のトランザクションで実行した「UPDATE isolation3 SET id=100;」の結果は失われてしまいました。
ロストアップデートを回避するためには、テーブルロックを取得することです。
テーブルロックをすることで、1つ目の端末のトランザクション処理が終了するまで、2つ目の端末のトランザクション処理は待ちます。
SERIALIZABLE
SERIALIZABLE とは、直列(各トランザクション処理を順番)に実行した場合と結果が同じとなることが保証する分離レベルです。
つまり同じ場所を更新する場合は、前のトランザクション処理が終わるまで次のトランザクション処理が実行できません。
InnoDB の SERIALIZABLE では、すべての SELECT ステートメントに共有ロックを自動で付与することで SERIALIZABLE を保証します。
共有ロックとは?ロックの種類について
共有ロックとは、他のトランザクションが同じ行を操作できないようにテーブルや行をロックすることです。
ロックは、強度によって次の2種類が存在します。
参照(SELECT) | 変更(UPDATE) | ステートメント | |
共有ロック | 可能 | 不可能 | SELECT ... LOCK IN SHARE MODE |
排他ロック | 不可能 | 不可能 | SELECT ... FOR UPDATE |
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
は独自のトランザクションです。
動作確認
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
「SELECT * FROM isolation4;」の処理が完了しないことが確認できます。
これは、1つ目の端末が共有ロックを取得した上でテーブルを更新したからです。
2つ目の端末は、1つ目の端末のトランザクション処理が終了するまで待機します。
+------+
| id |
+------+
| 1 |
+------+
分離レベルのまとめ
- ダーティーリード:COMMIT していない別のトランザクション処理の結果が見える
- ファジーリード:同じ SELECT 文を実行した場合でも同じカラムの結果が異なる
- ファントムリード:同じ SELECT 文を実行した場合でもカラムの数が異なる
- ロストアップデート:後から更新したトランザクションの結果に上書きされる
分離レベル | ダーティーリード | ファジーリード | ファントムリード | ロストアップデート※1 |
---|---|---|---|---|
READ UNCOMMITTED | ある | ある | ある | ある |
READ COMMITTED | ない | ある | ある | ある |
REPEATABLE READ | ない | ない | △※2 | ある |
SERIALIZABLE | ない | ない | ない | ない |
※2 一般的には「ある」。MySQL の InnoDB の場合は Next Key Lock という仕組みにより「ない」。
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 特性」に関する説明は以上となります。
その他のデータベースの入門記事ついては以下の記事をどうぞ。
- 【データベース入門1】データベースとは
- 【データベース入門2】SQL コマンドとは、SQL 文の一覧
- 【データベース入門3】トランザクションと ACID 特性とは ←今ここ
- 【データベース入門4】バックアップ・クラスター・レプリケーション
- 【データベース入門5】テーブル設計・正規化
- 【データベース入門6】オプティマイザー・実行計画