ACID 特性とは?データベースのトランザクションについて

データベース

ACID 特性とは

ACID とは、データベースのトランザクション処理において、信頼性を保証するために求められる次の4つの性質の頭文字です。

  • Atomicity(原始性)
  • Consistency(一貫性)
  • Isolation(分離性)
  • Durability(永続性)

なお、トランザクション処理とは、以下のように複数の処理を begin ~ commit で囲んだ処理のことです。

begin;
なんかの処理
なんかの処理
なんかの処理
commit;

以降では、データベースとして MySQL を例に ACID 特性を説明しますが、他のデータベースでも同様の考え方です。

Atomicity(原始性)

Atomicity は、すべてのトランザクション処理の実行結果が以下のどちらかになるという性質です。

  • トランザクションに含まれるすべての操作を実行する(COMMIT)
  • トランザクションに含まれるすべての操作を実行しない(ROLLBACK)

以下に実際のデータベースを用いて説明をします。

事前準備

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);

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

まずは、「すべての操作を実行する(COMMIT)」を説明します。COMMIT は通常のデータベース操作で利用します。

トランザクション処理を行う前のテーブルは以下のとおりです。

select * from account;
+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    1000 |
| Hanako |    2000 |
+--------+---------+

上記のテーブルに対して、トランザクション処理で以下の2つの操作を行い Taro から Hanako の口座に 500 円を送金します。

  • Taro の口座から 500 円を減らす
  • Hanako の口座に 500 円を増やす
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 |
+--------+---------+

以上のようにトランザクション処理で行われる操作がすべて実行されていることが確認できます。

Atomicity 特性により、トランザクション内のすべての操作が実行されます。Taro の口座だけお金が減って、Hanako の口座にお金が振り込まれないようなことはありません。

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

次に「すべての操作を実行しない(ROLLBACK)」を説明します。ROLLBACK はデータベースサーバーに障害が発生した際に、ぐちゃぐちゃになったデータベースを元に戻すために利用したりします。

トランザクション処理を行う前のテーブルは以下のとおりです。

select * from account;
+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    1500 |
| Hanako |    1500 |
+--------+---------+

トランザクション処理を用いて Taro から Hanako の口座に 500 円を送金します。

begin;
update account set balance = balance - 500 where name = 'Taro';
update account set balance = balance + 500 where name = 'Hanako';
select * from account;
+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    1000 |
| Hanako |    2000 |
+--------+---------+

Taro の口座から 500 円減少し、Hanako の口座から 500 円増加します。

ここで、送金結果を COMMIT せずに ROLLBACK します。

rollback;
select * from account;
+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    1500 |
| Hanako |    1500 |
+--------+---------+

ROLLBACK した結果、トランザクション処理のすべての更新を実行していない状態に戻りました。

Atomicity 特性により、トランザクション内のすべての操作が実行されません。Taroの口座だけ 500 円減ったり、Hanako の口座だけ 500円増えることはありません。

Consistency(一貫性)

Consistency は、あらかじめ決めたルールをトランザクション開始と終了時に満たすことを保証する性質のことです。

制約を守る

「あらかじめ決めたルール」=「銀行口座がマイナスとなる場合は引き出せない」とします。

この Consistency を保証するためには、INT UNSIGNED と呼ばれる正の整数しか代入できない型を利用します。

事前準備

create table account2(name varchar(20), balance int unsigned);
insert into account2 values('Taro',2000);
insert into account2 values('Hanako',1000);

作成したテーブルの中身

select * from account2;
+--------+---------+
| name   | balance |
+--------+---------+
| Taro   |    2000 |
| Hanako |    1000 |
+--------+---------+

Consistency(一貫性):あらかじめ決めたルールを満たす

ここであらかじめ決めたルール(銀行口座がマイナスとなる場合は引き出せない)を破ってみます。

Taro は 2000 円しかない口座から 3000 円を引き出してみます。

begin;
update account2 set balance = balance - 3000 where name = 'Taro';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`account2`.`balance` - 3000)'

正しくエラーが出て、データベースが処理を阻止してくれることがわかりました。

Isolation(分離性)

Isolation は、トランザクション処理が他のトランザクション処理の影響を受けないこと性質のことを指します。

他のトランザクション処理の影響をどの程度許容するかによって、以下の4つの分離レベルに分かれます。

  • READ UNCOMMITTED(確定していないデータまで読み取る)
  • READ COMMITTED(確定した最新データを常に読み取る)
  • REPEATABLE READ(読み取り対象のデータを常に読み取る)
  • SERIALIZABLE(直列化可能)

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

select @@global.tx_isolation, @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+

READ UNCOMMITTED

トランザクション処理同士が全く独立していない分離レベルです。

トランザクション処理がコミットする前の変更が、別のトランザクション処理から見えてしまう「ダーティリード」と呼ばれる事象が発生します。

READ UNCOMMITTED で ダーティリードを確認

1つ目の端末で、分離レベル READ UNCOMMITTED とし、テーブルを確認

set session transaction isolation level read uncommitted;
begin;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| Taro             |    2000 |
| Hanako           |    1000 |
+------------------+---------+

2つ目の端末で、分離レベル READ UNCOMMITTED としてテーブルを更新

use test;
set session transaction isolation level read uncommitted;
begin;
update account set name='read uncommitted' where name='Taro';

1つ目の端末にて、再びテーブルを確認

select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| Hanako           |    1000 |
+------------------+---------+

1つ目の端末でトランザクションをまだ COMMIT していないにも関わらず、2つ目の端末で1つ目の端末のトランザクション処理の結果が反映されています。これをダーティリードと言います。

後処理

必ず両方の端末で COMMIT してトランザクションを終了します。後の検証に影響が出ます。

commit;

READ COMMITTED

他のトランザクションが COMMIT した結果だけを見ることが可能です。

そのため、同じ SELECT 文を実行した場合でも、別のトランザクションが COMMIT した場合に結果が異なる「ノンリピータブルリード」(ファジーリードとも言う)と呼ばれる事象が発生します。

READ COMMITTED でノンリピータブルリード確認

1つ目の端末がデータベースを更新し COMMIT する前と後で、2つ目の端末において「ノンリピータブルリード」が発生していることを確認します。

1台目の端末で分離レベルを READ COMMITTED としてテーブルを確認

set session transaction isolation level read committed;
begin;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| Hanako           |    1000 |
+------------------+---------+

2つ目の端末でデータベースのテーブルを更新

set session transaction isolation level read committed;
begin;
update account set name='read committed' where name='Hanako';

トランザクション処理を COMMIT 前

select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| Hanako           |    1000 |
+------------------+---------+

2つ目の端末のトランザクション処理の結果がまだ反映されていないことがわかります。そのため、分離レベルが READ COMMITTED の場合はダーティーリードを防げます。

トランザクション処理を COMMIT 後

commit;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| read committed   |    1000 |
+------------------+---------+

別のトランザクション処理の COMMIT の影響を受け、1回目と全く同じ SELECT 文を発行しているにも関わらず、トランザクション処理の結果が変わってしまいました。これがノンリピータブルリードです。

REPEATABLE READ

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

RREPEATABLE READ で SELECT 文が同じ結果となることを確認

1台目の端末で分離レベルを REPEATABLE READ にしてテーブルを確認

set session transaction isolation level repeatable read;
begin;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| read committed   |    1000 |
+------------------+---------+

次に2つ目の端末でデータベースのテーブルを更新

set session transaction isolation level repeatable read;
begin;
update account set name='repeatable read' where name='read committed';

トランザクション処理を COMMIT 前

select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| read committed   |    1000 |
+------------------+---------+

先程と同じく、まだ COMMIT されていないので、ダーティーリードを防いでいます。

トランザクション処理を COMMIT 後

commit;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| read committed   |    1000 |
+------------------+---------+

別のトランザクションがCOMMITしても、1つ目の端末がトランザクション取得時のテーブル内容を SELECT しているため、ノンリピータブルリードを防いでいることがわかります。

COMMIT した側の端末

COMMIT した側の端末では、テーブルが更新されていることを確認できます。

select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
|repeatable read  |    1000 |
+------------------+---------+

ファントムリード

ファントムリードとは、トランザクションAで行を追加 or 削除し COMMIT する前に、トランザクション B から追加、削除した結果が見える現象のことです。

MyMySQL の InnoDB の RREPEATABLE READ では、Next Key Lock という仕組みでファントムリードは発生しません。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.7 ネクストキーロックによるファントム問題の回避

ロストアップデート

ロストアップデートとは、別のトランザクションの更新がなかったことになることです。

MySQL の InnoDB の RREPEATABLE READ では、ファントムリードが発生しない代わりに、MVCC に起因してロストアップデートが発生します。

ロストアップデートの確認

1つ目の端末でテーブルを確認

set session transaction isolation level read uncommitted;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    2000 |
| repeatable read  |    1000 |
+------------------+---------+

1つ目の端末で balance の値を変数 @x に格納するトランザクションを実施

begin;
select balance into @x from account where name='read uncommitted';

2つ目の端末でテーブルを更新するトランザクション処理を実施

set session transaction isolation level read uncommitted;
begin;
update account set balance=5000 where balance=2000;
commit;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    5000 |
| repeatable read  |    1000 |
+------------------+---------+

1つ目の端末で balance の値(@x)を2倍にする

update account set balance = @x * 2 where name = 'read uncommitted';
commit;
select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    4000 |
| repeatable read  |    1000 |
+------------------+---------+

4000(= 2000 * 2)となってしまいました。つまり、2つ目の端末のトランザクション処理で更新した 5000 という結果が失われたということになります。

これを回避するためには、きちんとテーブルロックを取得しましょう。そうすれば、1つ目の端末のトランザクション処理が終了後、2つ目の端末のトランザクション処理が実施されます。

SERIALIZABLE

並列処理した複数のトランザクション処理が、時間的に重なりが無くコミットしたものと同じ結果が同じとなることが保証されます。(つまり前のトランザクション処理が終わるまで次のトランザクション処理が実行できない場合と同じ結果)

同時にトランザクション処理が実行されない場合と同じ結果なので、ダーティーリード、ノンリピータブルリード、ファントムリード、ロストアップデートが発生しません。

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

共有ロックとは?ロックの種類

トランザクションでは、他のトランザクションが同じ行を操作できないようにデータをロックします。

ロックは、強度によって次の2種類が存在します。

参照(SELECT)変更(UPDATE)ステートメント
共有ロック可能不可能SELECT ... LOCK IN SHARE MODE
排他ロック不可能不可能SELECT ... FOR UPDATE

SELECT 文で共有ロックが取得されることを確認

1つ目の端末で現在のテーブルを確認

select * from account;
+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    4000 |
| repeatable read  |    1000 |
+------------------+---------+

2つ目の端末でテーブルを更新

set session transaction isolation level serializable;
begin;
update account set balance=3000 where balance=1000;

1つ目の端末でテーブルを SELECT ステートメントで確認

SELECT ステートメントの処理が進まないことが確認できます。

set session transaction isolation level serializable;
begin;
select * from account;

これは、以下のように共有ロックが取得できなかったためです。

  1. 2つ目の端末で UPDATE ステートメントにより、排他ロックが取得した
  2. 1つ目の端末で SERIALIZABLE の機能により、SELECT ステートメントで共有ロックを取得しようとした
  3. しかし既に2つ目の端末で行の排他ロックが取得されているため、1つ目の端末はロックの解除待ちとなった

そのため、2つ目の端末で COMMIT を実施し、排他ロックを解除してやると、1つ目の端末で共有ロックが取得できるため処理が進みます。

2つ目の端末で COMMIT を実施する

COMMIT;

1つ目端末の状況を確認

2つ目の端末で COMMIT した後、1つ目の SELECT 文が実行できていることがわかります。

+------------------+---------+
| name             | balance |
+------------------+---------+
| read uncommitted |    4000 |
| repeatable read  |    3000 |
+------------------+---------+

分離レベルのまとめ

  • ダーティーリード:COMMITされていないものが見える
  • ノンリピータブルリード:同じ SELECT ステートメントで UPDATE された結果が表示
  • ファントムリード:同じ SELECT ステートメントで追加 or 削除された結果が表示
  • ロストアップデート:他のトランザクション処理の UPDATE 結果が消える
分離レベルダーティーリードノンリピータブルリード※1ファントムリードロストアップデート
READ UNCOMMITTEDあるあるあるある
READ COMMITTEDないあるあるある
REPEATABLE READないない△※2ある
SERIALIZABLEないないないない

※1 ファジーリードとも言う

※2 一般的には「ある」。MySQL の InnoDB の場合は Next Key Lock という仕組みにより「ない」。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.7 ネクストキーロックによるファントム問題の回避

Durability(永続性)

Durability はトランザクション処理の完了通知を受けた時点で、トランザクション処理の結果が失われないことを表します。

トランザクション処理を永続化する方法には、メモリにあるデータをストレージにトランザクションログ(バイナリログ)=ファイルとして書き出す方法があります。

これは、各記憶装置には以下の特性があるからです。

  • メモリにあるデータは電源を切ると失われる
  • ストレージデバイスにファイルとして書き出したデータは電源を切っても残る

各記憶装置の特性の詳細については以下の記事をご覧ください。

メモリからバイナリログに永続化

Durability を確認するために、トランザクション処理の終了前後を確認してみます。

事前準備:バイナリログの場所の確認

バイナリログをの場所を確認します。

show global variables like "datadir";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
SHOW MASTER STATUS;
binlog.000078

バイナリログは /var/lib/mysql/binlog.000078 であることがわかりました。

トランザクション処理を COMMIT する前

トランザクション処理でデータベースを更新し、トランザクション処理を終了(COMMIT)しない状態でデータベースを確認します。

begin;
insert into account values('hogetech',2000);

シェルからバイナリログに上記のSQLが反映されているか確認します。

sudo mysqlbinlog --no-defaults /var/lib/mysql/binlog.000078|grep hogetech

何も表示されていないため、バイナリログに反映されていないことがわかります。

トランザクション処理を COMMIT した後

先程のトランザクション処理を終了します。

commit;

シェルからバイナリログに上記のSQLが反映されているか確認します。

sudo mysqlbinlog --no-defaults /var/lib/mysql/binlog.000078|grep hogetech
insert into account values('hogetech',2000)

メモリからファイル(バイナリログ)にトランザクション処理が永続化されていることがわかります。

これにより、電力障害等でサーバーがダウンしてもバイナリログからデータベースを復旧可能となります。

0

コメント