リレーショナルデータベースの排他制御は、複数のトランザクションが同時に同じリソース (テーブル・行) に対してアクセスしても内容の整合性が崩れないようにするために行うもの。
また、排他制御のやり方には悲観的と楽観的のふたつがある。 悲観的排他制御は、あらかじめ対象のリソースを自分以外のトランザクションから操作できないように排他ロックをかけることで処理をアトミックにして実現する。 それに対して楽観的排他制御では、リソースを操作する際にそれが別のトランザクションから変更されていないことをバージョン番号やタイムスタンプを使って確認することで実現する。 今回はこのふたつの排他制御について MySQL を使って試してみることにする。
尚、今回使用する環境は次の通り。
MySQL はバージョン 5.6.26 を使っている。
$ mysql -u root -e "show variables like 'version'" +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.6.26 | +---------------+--------+
トランザクション分離レベルはデフォルトの REPEATABLE-READ のまま。
$ mysql -u root -e "select @@tx_isolation" +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
ストレージエンジンはトランザクションをサポートしている InnoDB を使う。
$ mysql -u root -e "show variables like 'default_storage_engine'" +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+
また、別々の端末から実行している複数のトランザクションを識別する必要がある操作に関しては、クライアントのプロンプトを以下のように変更している。
mysql> prompt prompt1> PROMPT set to 'prompt1> ' prompt1> mysql> prompt prompt2> PROMPT set to 'prompt2> ' prompt2>
排他制御なし
排他制御を試す前に、それがないとどうなるかについて見ていく。 今回は複数のユーザの銀行口座を模したテーブル (accounts) を作って挙動を確認しよう。
mysql> create table accounts ( -> id Integer auto_increment, -> name Text not null, -> cash Integer not null, -> primary key (id) -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc accounts; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | text | NO | | NULL | | | cash | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
あらかじめ、動作確認に使う行 (=銀行口座) をひとつ挿入しておく。
mysql> insert into accounts (name, cash) values ('foo', 100); Query OK, 1 row affected (0.00 sec)
それでは、ここから排他制御がない場合にどういったことが起きるかを確認していく。 想定としては、同じ銀行口座 (=行) に対して複数の顧客から入金 (=トランザクション) があることにしよう。 処理内容的には、まず口座の残高 (cash カラム) を確認したうえで、それに入金額を加算した金額で行を更新 (update) することになる。
まず最初にトランザクション 1 が口座の残高を確認する。
prompt1> begin; Query OK, 0 rows affected (0.00 sec) prompt1> select * from accounts where name like 'foo'; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 100 | +----+------+------+ 1 row in set (0.00 sec)
同時にトランザクション 2 も口座の残高を確認する。
prompt2> begin; Query OK, 0 rows affected (0.00 sec) prompt2> select * from accounts where name like 'foo'; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 100 | +----+------+------+ 1 row in set (0.00 sec)
そして、トランザクション 1 が先に 50 を加算した値で銀行口座を更新した。
prompt1> update accounts set cash = 150 where name like 'foo'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
続いてトランザクション 2 は 100 を加算した値で銀行口座を更新する。 しかし、上記の処理によって対象に暗黙の排他ロックがかかるためトランザクション 2 はブロックすることになる。
prompt2> update accounts set cash = 200 where name like 'foo'; ...
処理が終わったトランザクション 1 は変更内容をコミットする。
prompt1> commit; Query OK, 0 rows affected (0.00 sec)
トランザクション 1 が変更内容をコミットしたことで排他ロックが外れてトランザクション 2 の処理も実行される。
prompt2> update accounts set cash = 200 where name like 'foo'; Query OK, 1 row affected (8.78 sec) Rows matched: 1 Changed: 1 Warnings: 0 prompt2> commit; Query OK, 0 rows affected (0.00 sec)
さて、初期の口座残高は 100 だった。 トランザクション 1 は銀行口座に 50 を、トランザクション 2 は銀行口座に 100 を入金したつもりになっているので、口座残高は 250 になっていてほしい。 しかし、実際にはトランザクション 2 の処理でトランザクション 1 の処理が上書きされてしまったため 200 になってしまっている。
mysql> select * from accounts where name like 'foo'; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 200 | +----+------+------+ 1 row in set (0.00 sec)
上記は口座残高の読み取りから書き込みまでがアトミックになっておらず、あるいは別のトランザクションによる変更にも気づくことができなかったために起きた。
悲観的排他制御
それでは次に、悲観的排他制御を使って整合性が崩れないようにしたい。
使用するテーブルは先ほどと全く同じ。
mysql> create table accounts ( -> id Integer auto_increment, -> name Text not null, -> cash Integer not null, -> primary key (id) -> ); Query OK, 0 rows affected (0.07 sec) mysql> desc accounts; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | text | NO | | NULL | | | cash | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
先ほどと同様に動作確認用の行を挿入しておく。
mysql> insert into accounts (name, cash) values ('foo', 100); Query OK, 1 row affected (0.00 sec)
そして、まずはトランザクション 1 が口座残高を読み取る。 その際、select 文に対して for update を付与するのがポイントとなる。 こうすると select 文が発行された時点で対象に排他ロックがかかる。
prompt1> begin; Query OK, 0 rows affected (0.00 sec) prompt1> select * from accounts where name like 'foo' for update; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 100 | +----+------+------+ 1 row in set (0.00 sec)
続いてトランザクション 2 も口座残高を読み取ろうとするが、対象に排他ロックがかかっているため select 文を発行した時点で処理がブロックする。
prompt2> begin; Query OK, 0 rows affected (0.00 sec) prompt2> select * from accounts where name like 'foo' for update; ...
トランザクション 1 はトランザクション 2 がブロックしている間に口座残高を更新して内容をコミットできる。
prompt1> update accounts set cash = 150 where name like 'foo'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 prompt1> commit; Query OK, 0 rows affected (0.01 sec)
トランザクション 2 はトランザクション 1 がコミットされるとブロックが解除されて処理を継続できる。 読み取った口座残高を元に 100 を加算して変更内容をコミットする。
prompt2> select * from accounts where name like 'foo' for update; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 150 | +----+------+------+ 1 row in set (13.00 sec) prompt2> update accounts set cash = 250 where name like 'foo'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 prompt2> commit; Query OK, 0 rows affected (0.00 sec)
今回は悲観的排他制御によって正しく口座残高を 250 にすることができた。
mysql> select * from accounts where name like 'foo'; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 250 | +----+------+------+ 1 row in set (0.00 sec)
排他ロックを使わない悲観的排他制御
ちなみに、上記の例に関して言えば select ... for update を使った排他ロックに頼らずとも同様の悲観的排他制御は実現できそう。 次のように現在の値に対して加算を行う update 文を使えば、値の読み取りから書き込みまでがアトミックにできるはず。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update accounts set cash = cash + 100 where name like 'foo'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from accounts where name like 'foo'; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 350 | +----+------+------+ 1 row in set (0.00 sec)
インデックスのないカラムを where 条件に指定した排他ロックには要注意
先ほどの例では、インデックスのないカラム (name) を where 条件に指定して排他ロック (select ... for update) をかけていた。 今回は例を単純化するためにそのようにしたものの、実はこれをやってしまうと MySQL が排他ロックをかけなければいけない行をあらかじめ特定することができない。 その場合、MySQL は可能性のある全てに対して排他ロックをかけるため、結果としてテーブルの全ての行に対して排他ロックがかかることになる。 すると、本来は平行して処理できるはずの全く関係ない行まで別のトランザクションからの操作をブロックしてしまうことになるので注意が必要になる。
試しにテーブルに無関係の行をひとつ挿入しておく。
mysql> insert into accounts (name, cash) values ('bar', 200); Query OK, 1 row affected (0.00 sec) mysql> select * from accounts; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 350 | | 2 | bar | 200 | +----+------+------+ 2 rows in set (0.00 sec)
トランザクション 1 から name が 'foo' のものを where 条件に指定して排他ロックをかける。
prompt1> begin; Query OK, 0 rows affected (0.01 sec) prompt1> select * from accounts where name like 'foo' for update; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 350 | +----+------+------+ 1 row in set (0.10 sec)
続いて、トランザクション 2 では name が 'foo' ではない全く無関係の 'bar' という行に対して排他ロックをかけようとする。 しかし、先ほどの排他ロックはテーブルの行すべてを対象にかけられてしまっているのでブロックする。
prompt2> begin; Query OK, 0 rows affected (0.00 sec) prompt2> select * from accounts where name like 'bar' for update; ... ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
上記の問題を防ぐため name カラムにインデックスを作成してみよう。
mysql> alter table accounts add index idx_name(name(64)); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
先ほどと同様にトランザクション 1 は name が 'foo' のものを where 条件に指定して排他ロックを取得する。
prompt1> begin; Query OK, 0 rows affected (0.00 sec) prompt1> select * from accounts where name like 'foo' for update; +----+------+------+ | id | name | cash | +----+------+------+ | 1 | foo | 350 | +----+------+------+ 1 row in set (0.10 sec)
続いてトランザクション 2 から name が 'bar' のものを where 条件に指定して排他ロックを取得する。
prompt2> begin; Query OK, 0 rows affected (0.00 sec) prompt2> select * from accounts where name like 'bar' for update; +----+------+------+ | id | name | cash | +----+------+------+ | 2 | bar | 200 | +----+------+------+ 1 row in set (0.00 sec)
今度はブロックせずにロックが取得できた！
楽観的排他制御
続いて楽観的排他制御を試してみる。
楽観的排他制御ではテーブルにバージョン番号やタイムスタンプを格納するカラムが新たに必要になる。 今回はバージョン番号を採用して version というカラムを追加した。
mysql> create table accounts ( -> id Integer auto_increment, -> version Integer not null, -> name Text not null, -> cash Integer not null, -> primary key (id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc accounts; +---------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | version | int(11) | NO | | NULL | | | name | text | NO | | NULL | | | cash | int(11) | NO | | NULL | | +---------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
動作確認用の行を追加して、バージョンは 1 で初期化しておく。 デフォルト値を設定しておいた方が楽なんだろうな。
mysql> insert into accounts (version, name, cash) values (1, 'foo', 100); Query OK, 1 row affected (0.00 sec)
まずはトランザクション 1 が口座残高と現在のバージョン番号を確認する。
prompt1> begin; Query OK, 0 rows affected (0.00 sec) prompt1> select * from accounts where name like 'foo'; +----+---------+------+------+ | id | version | name | cash | +----+---------+------+------+ | 1 | 1 | foo | 100 | +----+---------+------+------+ 1 row in set (0.00 sec)
続いてトランザクション 2 も口座残高と現在のバージョン番号を確認する。
prompt2> begin; Query OK, 0 rows affected (0.00 sec) prompt2> select * from accounts where name like 'foo'; +----+---------+------+------+ | id | version | name | cash | +----+---------+------+------+ | 1 | 1 | foo | 100 | +----+---------+------+------+ 1 row in set (0.00 sec)
トランザクション 1 が先んじて先ほど確認した口座残高に 50 を加算した内容で update 文を発行する。 この際ポイントとなるのは where 条件で先ほど確認したバージョン番号を指定している点と、バージョン番号を新しいものに更新している点だ。
prompt1> update accounts set version = 2, cash = 150 where name like 'foo' and version = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
続いてトランザクション 2 も先ほど確認した口座残高に 100 を加算した内容で update 文を発行する。 しかし、トランザクション 1 の update 文によって暗黙の排他ロックが行にかかっているためブロックする。
prompt2> update accounts set version = 2, cash = 200 where name like 'foo' and version = 1; ...
トランザクション 1 は正常に変更内容のコミットを完了する。
prompt1> commit; Query OK, 0 rows affected (0.00 sec)
トランザクション 1 のかけていた排他ロックが commit によって解除されるのでトランザクション 2 の処理が継続する。 しかし、トランザクション 1 がバージョン番号を更新した後なのでトランザクション 2 の発行した update 文は where の条件に引っかかるものがなく Rows matched: 0 になっている。 つまり、これで自分が変更しようとした行が既に別のトランザクションによって自分の知っている状態から変更されてしまっていることに気づくことができたというわけ。
prompt2> update accounts set version = 2, cash = 200 where name like 'foo' and version = 1; Query OK, 0 rows affected (12.56 sec) Rows matched: 0 Changed: 0 Warnings: 0
この時点で、トランザクション 2 は一旦変更内容を rollback で破棄する。
prompt2> rollback; Query OK, 0 rows affected (0.00 sec)
そして、同じ処理をもう一度やり直す。
prompt2> begin; Query OK, 0 rows affected (0.00 sec) prompt2> select * from accounts where name like 'foo'; +----+---------+------+------+ | id | version | name | cash | +----+---------+------+------+ | 1 | 2 | foo | 150 | +----+---------+------+------+ 1 row in set (0.00 sec) prompt2> update accounts set version = 3, cash = 250 where name like 'foo' and version = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 prompt2> commit; Query OK, 0 rows affected (0.00 sec)
これで銀行口座は整合性を崩すことなく 250 に更新できた。
mysql> select * from accounts where name like 'foo'; +----+---------+------+------+ | id | version | name | cash | +----+---------+------+------+ | 1 | 3 | foo | 250 | +----+---------+------+------+ 1 row in set (0.00 sec)
まとめ
今回は悲観的排他制御と楽観的排他制御というふたつの排他制御を MySQL を使って実際に試してみた。 各排他制御の戦略について再確認しておくと、悲観的排他制御はあらかじめ対象のリソースに排他ロックをかけることで、他のトランザクションに割り込まれることを防ぐ。 それに対して、楽観的排他制御では自分が操作しようとしたリソースが他のトランザクションによって変更されていることに気づくことで、自身が割り込んでしまうことを防ぐことになる。
