【入門】トランザクションと ACID 特性とは

トランザクショントランザクションとは、複数の処理を１まとめに処理することです。

ACID 特性ACID 特性とは、トランザクションが持つ次の４つの特性です。
・Atomicity (原子性)
・Consistency (一貫性)
・Isolation (分離性)
・Durability (永続性)

本記事では、MySQL を例に説明しますが、他の RDBMS でも考え方は同じです。

関連記事：データベースの基礎知識
データベースとは	SQL コマンド	ACID	レプリケーション
正規化	オプティマイザー

おうちで学べるデータベースのきほん

翔泳社

Amazonの商品レビュー・口コミを見る

SQL 第2版: ゼロからはじめるデータベース操作

翔泳社

Amazonの商品レビュー・口コミを見る

Atomicity (原子性)

Atomicity (原子生)Atomicity とは、トランザクションの実行結果が、以下のどちらかになる特性です。
・トランザクションに含まれるすべての操作を実行しない (ROLLBACK)
・トランザクションに含まれるすべての操作を実行する (COMMIT)

すべての操作を実行しない (ROLLBACK)

すべての操作を実行する (COMMIT)

つまり、Atomicity は処理が途中で終わることを禁止する特性です。

すべての操作を実行しない (ROLLBACK)

ROLLBACK とは、トランザクションを開始する前の状態に戻すことです。

ROLLBACK はなんらかの問題が発生した場合に、トランザクション開始前の状態に戻します。

動作確認

以下の検証用テーブルを利用して、Atomicity (原子性) の動作を確認してみます。

CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE account(name VARCHAR(20), balance INT);

INSERT INTO account VALUES('Taro',2000);

INSERT INTO account VALUES('Hanako',1000);

SELECT * FROM account;

+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    2000 |
| Hanako |    1000 |
+--------+---------+

Taro から Hanako に 500 円を送金するために、以下のトランザクション処理をします。

Taro の銀行口座から 500 円を減らす
Hanako の銀行口座に 500 円を増やす

BEGIN; #トランザクションを開始するコマンド

UPDATE account SET balance = balance - 500 WHERE name = 'Taro';

SELECT * FROM account;

+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    1500 |
| Hanako |    1000 |
+--------+---------+

ここで障害が発生し、「2. Hanako の銀行口座に 500 円を増やす」が出来ない場合を考えます。

ROLLBACK を利用して、トランザクション開始前の状態に戻します。

ROLLBACK;

SELECT * FROM account;

+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    2000 |
| Hanako |    1000 |
+--------+---------+

ROLLBACK でトランザクション開始前の状態に戻るため、Atomicity を満たします。

すべての操作を実行する (COMMIT)

COMMIT とは、トランザクション処理の変更を確定することです。

動作確認

Taro から Hanako に 500 円を送金するために、以下のトランザクション処理をします。

Taro の銀行口座から 500 円を減らす
Hanako の銀行口座に 500 円を増やす

SELECT * FROM account;

+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    2000 |
| Hanako |    1000 |
+--------+---------+

BEGIN;

UPDATE account SET balance = balance - 500 WHERE name = 'Taro';

UPDATE account SET balance = balance + 500 WHERE name = 'Hanako';

COMMIT;

SELECT * FROM account;

+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    1500 |
| Hanako |    1500 |
+--------+---------+

上記のトランザクションは COMMIT して確定いるため、ROLLBACK で戻すことはできません。

ROLLBACK;

SELECT * FROM account;

+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    1500 |
| Hanako |    1500 |
+--------+---------+

全ての変更が確定しているため、Atomicity を満たします。

絵で見てわかるOS/ストレージ/ネットワーク新装版

翔泳社

Amazonの商品レビュー・口コミを見る

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ

翔泳社

Amazonの商品レビュー・口コミを見る

Consistency (一貫性)

Consistency (一貫性)Consistency とは、トランザクション前後で整合性を満たす性質です。

Consistency はアプリケーションの内容に依存するため、アプリケーション側の責任となる。

データベース側で Consistency を保つ場合は、次のような、なんらかの制約を設定します。

PRIMARY KEY (値が一意かつ NOT NULL)
FOREIGN KEY (指定した他のテーブルの列に含まれる値のみを格納可能)
無効データ (特定の値を禁止)
ENUM および SET (指定した値のみ格納可能)

トランザクションで制約を違反した場合、違反した SQL 文に対して ROLLBACK が発生します。

通常、データ変更ステートメント (INSERT や UPDATE など) がプライマリキー、一意キー、または外部キーの制約に違反すると、エラーが発生します。InnoDB などのトランザクションストレージエンジンを使用している場合、MySQL ではステートメントが自動的にロールバックされます。
https://dev.mysql.com/doc/refman/8.0/ja/constraint-primary-key.html

PRIMARY KEY

PRIMARY KEY とは、列の値が一意かつ NULL を禁止する制約です。

動作確認

CREATE TABLE consistency1(account_id INT NOT NULL PRIMARY KEY, name VARCHAR(20));

BEGIN;

INSERT INTO consistency1 VALUES(1,'hoge');

INSERT INTO consistency1 VALUES(1,'tech');

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

PRIMARY KEY が重複した場合は ROLLBACK するため、Consistency を満たしています。

COMMIT;

SELECT * FROM consistency1;

+------------+------+
| account_id | name |
+------------+------+
|          1 | hoge |
+------------+------+

FOREIGN KEY

FOREIGN KEY とは、関連テーブルにある値のみ格納可能な制約です。

Consistency1 に存在しない会員 ID で、Consistency2 に登録禁止

動作確認

CREATE TABLE consistency2 (
foreign_account_id INT,
balance INT,
FOREIGN KEY (foreign_account_id) REFERENCES consistency1(account_id));

BEGIN;

INSERT INTO consistency2 VALUES(2,3000);

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 を満たします。

COMMIT;

SELECT * FROM consistency2;

Empty set (0.00 sec)

無効なデータ

無効なデータ とは、特定のデータの格納を禁止する制約です。

今回は UNSIGNED 属性 (負の整数を禁止) で検証します。

動作確認

CREATE TABLE consistency3(balance INT UNSIGNED);

BEGIN;

INSERT INTO consistency3 VALUES(-1);

ERROR 1264 (22003): Out of range value for column 'balance' at row 1

無効データの挿入を禁止して、Consistency を満たしています。

COMMIT;

SELECT * FROM consistency3;

Empty set (0.00 sec)

ENUM および SET

ENUM および SET とは、特定の値のみを列に格納できる制約です。

動作確認

CREATE TABLE consistency4(grade ENUM('bronze', 'silver', 'gold'));

BEGIN;

INSERT INTO consistency4 VALUES('blue');

ERROR 1265 (01000): Data truncated for column 'grade' at row 1

デフォルトの SQL モードでは、ENUM に無い値はエラーとなり、Consistency を満たします。

COMMIT;

SELECT * FROM consistency4;

Empty set (0.00 sec)

達人に学ぶDB設計徹底指南書

翔泳社

Amazonの商品レビュー・口コミを見る

SQLアンチパターン

オライリージャパン

Amazonの商品レビュー・口コミを見る

Isolation (分離性)

IsolationIsolation とは、他のトランザクション処理の影響を受けない性質です。

他のトランザクション処理の影響とは、次のリード現象を指します。

リード現象 (Read Phenomena)

トランザクションを分離しない場合に、発生するリード現象は次の 4 つです。

ダーティリード
ファジーリード
ファントムリード
ロストアップデート (ANSI/ISO standard SQL 92 では未定義)

ダーティーリード

ダーティーリードとは、COMMIT していない別のトランザクションの結果が見える現象です

ファジーリード

ファジーリードとは、別のトランザクションの更新により、同じデータを読み取っても、異なる値を取得する現象です。

トランザクションが分離されているなら、右側は COMMIT するまで同じ値を返すべきである。
(右側は COMMIT するまで、左側のトランザクションの影響を受けないことが期待される)

ファントムリード

ファントムリードとは、別のトランザクションの追加・更新・削除により、同じ検索条件でも、取得する行数が異なる現象です。

ロストアップデート

ロストアップデートとは、後から更新したトランザクションの結果に上書きされる (前のトランザクションの更新が消える) 現象です。

分離レベル

分離レベル分離レベルとは、リード現象をどの程度許容するか定義したものです。

トランザクションを並行で実行すると、他のトランザクションでリード現象が発生します。

一方で、パフォーマンスの観点から、トランザクションを並行で実行したいです。

そのため、分離レベルで、パフォーマンスと Isolation のバランスを決定します。

分離レベル	ダーティーリード	ファジーリード	ファントムリード	ロストアップデート※1
READ UNCOMMITTED	発生する	発生する	発生する	発生する
READ COMMITTED	発生しない	発生する	発生する	発生する
REPEATABLE READ	発生しない	発生しない	△※2	発生する
SERIALIZABLE	発生しない	発生しない	発生しない	発生しない

※1 ロストアップデートは ANSI/ISO SQL では定義されていない
※2 ANSI では「発生する」。MySQL の InnoDB の場合は Next Key Lock という仕組みにより「発生しない」。(参考)

なお、分離レベルは、以下のグローバル変数・セッション変数で確認可能です。

select @@global.tx_isolation, @@session.tx_isolation;

+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+

READ UNCOMMITTED

READ UNCOMMITTED とは、トランザクションが全く分離していない分離レベルです。

READ COMMITTED

READ UNCOMMITTED とは、他のトランザクションが COMMIT した結果を読み取る分離レベルです。

REPEATABLE READ

REPEATABLE READ とは、同じトランザクション内で SELECT を実行すると、常に同じ結果となることが保証されている分離レベルです。

SERIALIZABLE

SERIALIZABLE とは、直列に実行 (各トランザクションを順番に順番) した場合と、同じ結果を保証する分離レベルです。

InnoDB の SERIALIZABLE では、すべての SELECT ステートメントに共有ロックを自動で付与することで SERIALIZABLE を保証します。

SERIALIZABLE
このレベルは REPEATABLE READ と似ていますが、autocommit が無効になっている場合、InnoDB はすべてのプレーン SELECT ステートメントを SELECT ... FOR SHARE に暗黙的に変換します。 autocommit が有効な場合、SELECT は独自のトランザクションです。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html

失敗から学ぶRDBの正しい歩き方 (Software Design plus)

技術評論社

Amazonの商品レビュー・口コミを見る

リレーショナルデータベース入門: データモデル・SQL・管理システム・NoSQL (Information&Computing 116)

サイエンス社

Amazonの商品レビュー・口コミを見る

Durability（永続性）

Durability はトランザクション処理を完了（COMMIT）した時点で、その結果が失われない性質のことです。

トランザクション処理を COMMIT した結果が失われる状況は、次の２種類が考えられます。

システム障害：OS や電源の障害などにより、メモリ上のデータが消失
ストレージ障害：ストレージの破損などにより、ストレージ上のデータが消失

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 とパフォーマンスは以下のようにトレードオフの関係です。

書き出し先	Durability	パフォーマンス
メモリ	☓：電源障害でデータが消える	○：速い
ストレージ	○：電源障害でもデータが残る	☓：遅い

RDBMS では、次のチェックポイントまで可能な限りメモリ上でデータページを更新することで、パフォーマンスを向上させます。(後述しますが、Durability は低下します。)

用語	説明
バッファプール	メモリ上にあるテーブル、インデックスデータのキャッシュ
テーブルスペース	バッファプールをストレージに書き出したデータ
ダーティページ	バッファプールからテーブルスペースにまだ書き出してないページ
チェックポイント	バッファプール上の全てのダーティページを、テーブルスペースに書き出すこと

なお、各用語は RDBMS の種類に応じて以下のように読み替えてください。

MySQL	PostgreSQL	Oracle
（InnoDB）バッファプール	共有バッファ	データベースバッファキャッシュ
テーブルスペース(.ibd,ibdata1 等)	データファイル	データファイル

動作確認

delimiter //

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//

delimiter ;

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 0     |
+--------------------------------+-------+

何もトランザクション処理をしていないため、ダーティページは０です。

ここから検証を始めます。

CREATE TABLE durability(id int);

SET AUTOCOMMIT = 0;

CALL loop_insert(500000);

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

+--------------------------------+-------+
| 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 ログ」の関係を確認してください。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-architecture.html

https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/architecture-overview/

WAL の動作は以下のとおりです。

WAL により、以下のように COMMIT したトランザクション処理の Durability を実現します。

COMMIT 完了前にシステム障害発生：更新に失敗した REDO ログを捨てる
COMMIT 完了後にシステム障害発生：REDO ログを利用して、前回のチェックポイントからの差分をテーブルスペースにリカバリ (クラッシュリカバリ)

また、テーブルスペースを直接更新せずに WAL を利用して先に REDO ログを更新すると以下のようなメリットがあります。

比較対象	WAL (REDO ログを先に更新)	テーブルスペースに直接更新
COMMIT 中の障害	更新に失敗した REDO ログを捨てる	更新に失敗したテーブルスペースを捨てられない (実際のテーブルデータだから)
アクセス速度	シーケンシャルアクセスなので速い (LSN や更新レコード等を追記する)	ランダムアクセスなので遅い (実際のテーブルデータにアクセスし更新する)
書き込み回数	少ない (REDO ログだけ)	多い (更新するすべてのテーブルスペース)

動作確認

delimiter //

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//

delimiter ;

ここから検証を始めます。

SET AUTOCOMMIT = 0;

CALL loop_insert2(500000);

SHOW ENGINE INNODB STATUS\G

---
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 した結果までテーブルスペースを復旧することです。

InnoDB のクラッシュリカバリは、次のいくつかのステップで構成されます。
・テーブルスペースの検出
・Redo log アプリケーション
・不完全な transactions の Roll back
・Change buffer マージ
・Purge
https://dev.mysql.com/doc/refman/8.0/ja/innodb-recovery.html

Redo ログを利用したロールフォワードは以下のように実施されます。

クラッシュ時

このときの各記録状況は以下のとおりです。

データ名	記録状況
ログバッファ	メモリなのでデータは吹き飛びます
バッファプール	メモリなのでデータは吹き飛びます
REDO ログ	ストレージ上にあるので残ります。中身は「変更操作」と「チェックポイントレコード」です
テーブルスペース	ストレージ上にあるので、前回のチェックポイントまでの更新内容が残ります