MySQLの排他ロックの挙動を確認する
MySQLの排他ロックについて、業務では使っているもののふわっとした理解だったので調べたメモを残しておく
ACIDモデル
トランザクションのことを調べているとよく目にするACIDというワード。こちらもふわっとしてたのでまとめてみる。
ACIDについてはMySQLの公式ドキュメントで以下のように説明されていた
ACID モデルは、ビジネスデータおよびミッションクリティカルなアプリケーションで重要となる信頼性の側面が強調されたデータベース設計原則のセットです。
信頼性の高いトランザクションシステムを構築するにあたって大切な原則、という感じか。
ACIDは4つの原則の頭文字を取った略語で、その内訳は以下のようになる。
Atomicity(原子性)
トランザクション内の処理が、全て実行されるか全て実行されないかを保証すること。一つのトランザクションそのものが原子的であるべき、ということ。
Consistency(一貫性)
トランザクション開始時点から終了時点まで、常に同じ状態のデータを参照することを保証すること、という理解。トランザクションの処理中に扱うデータが外部の影響を受けたらよくないよね、一貫性がなくなるよね、という感じ。
Isolation(独立性)
Consistencyとも似てる気がするけど、これは扱うデータが他のトランザクションから独立していることを保証すること。
Durability(永続性)
トランザクションの結果が失われないことを保証すること。
トランザクション分離レベル
トランザクション分離レベルとは、RDBMSで発生しえる不都合な読み込み(ダーティリード、ファジーリード、ファントムリード)をどこまで許容するかの線引きのこと。
MySQLでデフォルトの設定はREPEATABLE READだが、MySQLのREPEATABLE READはUPDATEとDELTEではREAD COMMITTEDの挙動をする。
設定の確認
mysql> SELECT @@GLOBAL.transaction_isolation; +--------------------------------+ | @@GLOBAL.transaction_isolation | +--------------------------------+ | REPEATABLE-READ | +--------------------------------+
以下の検証ではトランザクション分離レベルの設定がREPEATABLE-READであることを前提に進める。
調査環境
$ mysql --version mysql Ver 8.0.13 for osx10.13 on x86_64 (Homebrew)
準備
まずは検証で使うテーブルとデータを用意する。適当にuserのデータを作る。
mysql> create table users (id int unsigned not null auto_increment, name varchar(255) not null, is_active boolean not null, primary key(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> desc users; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | is_active | tinyint(1) | NO | | NULL | | +-----------+------------------+------+-----+---------+----------------+ mysql> insert into users (id, name, is_active) values (1, "一郎", 1), (2, "二郎", 1), (3, "三郎", 1), (10, "太郎", 1), (20, "たかし", 1); mysql> select * from users; +----+-----------+-----------+ | id | name | is_active | +----+-----------+-----------+ | 1 | 一郎 | 1 | | 2 | 二郎 | 1 | | 3 | 三郎 | 1 | | 10 | 太郎 | 1 | | 20 | たかし | 1 | +----+-----------+-----------+
排他ロック(SELECT ~ FOR UPDATE)
排他ロックでは、ロックされたレコードは他での単純なSELECT文以外を許容しない。
つまり、排他ロックがかかったレコードに対しては、他のトランザクションからUPDATEやDELETEができない。ロックを取ることもできない。
レコードロック
-- 他のトランザクションからSELECT ~ FOR UPDATE Transaction A> begin; Transaction B> begin; A> select * from users where id=1 for update; -- Aでロック取得 1 row in set (0.00 sec) B> select * from users where id=1; -- Bからシンプルなselectは可能 1 row in set (0.00 sec) B> select * from users where id=1 for update; -- ここで止まる A> commit; B> 1 row in set (6.01 sec) -- Aのコミット後にBで結果が返ってくる -- UPDATE A> begin; B> begin; A> select * from users where id=1 for update; -- Aでロック取得 1 row in set (0.00 sec) B> UPDATE users SET is_active=0 where id=1; -- ここで止まる A> select * from users where id=1; +----+--------+-----------+ | id | name | is_active | +----+--------+-----------+ | 1 | 一郎 | 1 | +----+--------+-----------+ A> commit; B> Query OK, 2 rows affected (20.69 sec) -- Aのコミット後にBで結果が返ってくる B> commit; A> select * from users where id=1; -- Bのコミット後 +----+--------+-----------+ | id | name | is_active | +----+--------+-----------+ | 1 | 一郎 | 0 | +----+--------+-----------+ -- DELTE A> begin; B> begin; A> select * from users where id=1 for update; 1 row in set (0.00 sec) B> delete from users where id=1; -- ここで止まる A> commit; B> Query OK, 2 rows affected (20.69 sec) -- Aのコミット後にBで結果が返ってくる Rows matched: 3 Changed: 2 Warnings: 0 A> begin; A> select * from users where id=1; -- BのコミットはまだなのでAのレコードはまだ存在する +----+--------+-----------+ | id | name | is_active | +----+--------+-----------+ | 1 | 一郎 | 0 | +----+--------+-----------+ 1 row in set (0.00 sec) B> commit; A> select * from users where id=1; -- Bはコミットされたが、その前にAのトランザクションが開始したのでレコードはまだ存在している(Isolation(隔離性)の担保) +----+--------+-----------+ | id | name | is_active | +----+--------+-----------+ | 1 | 一郎 | 1 | +----+--------+-----------+ 1 row in set (0.00 sec) A> begin; A> select * from users where id=1; -- Bのコミット後に始まったトランザクションではid=1のレコードは存在しない Empty set (0.00 sec)
ギャップロック
ギャップロックとは、インデックスのレコードとレコードの間にかかるロックのこと。ファントムリードを防ぐのが目的。
A> begin; A> select * from users where id=4 for update; -- 空振りだがギャップロックがかかる Empty set (0.00 sec) B> begin; B> insert into users (id, name, is_active) values (5, 'しんご', 1); -- ここで止まる A> commit; B> Query OK, 1 row affected (31.32 sec) -- Aのコミット後にBで結果が返ってくる
なおprimary keyでの一行指定だけじゃなく範囲指定でも同様に存在しないレコードにロックがかかる。
ネクストキーロック
インデックスレコードのロックと、そのレコードの直前にあるギャップのロックを組み合わせたもの。
公式の説明
ネクストキーロックは、インデックス行ロックとギャップロックを組み合わせたものです。InnoDB は、テーブルインデックスを検索またはスキャンするときに、生成されたインデックスレコード上に共有ロックまたは排他ロックを設定するという方法で、行レベルロックを実行します。したがって、行レベルロックは、実際にはインデックスレコードロックです。さらに、あるインデックスレコードに対するネクストキーロックによって、そのインデックスレコードの前の「ギャップ」も影響を受けます。つまり、ネクストキーロックは、インデックスレコードロックと、そのインデックスレコードの前のギャップに対するギャップロックとを組み合わせたものです。
とりあえず今はそういうもんだと思っておく。実際にネクストキーロックがかかるか試してみる
A> select * from users; +----+-----------+-----------+ | id | name | is_active | +----+-----------+-----------+ | 1 | 一郎 | 1 | | 2 | 二郎 | 1 | | 3 | 三郎 | 1 | | 10 | 太郎 | 1 | | 20 | たかし | 1 | +----+-----------+-----------+ A> select * from users where id >= 11 and id <= 19 for update; -- 11~19でギャップロックがかかる B> update users set is_active=0 where id=20; -- ここで待たされるのでid=20に排他ロックがかかってる。これがネクストキーロックっぽい
ちなみにこれid範囲指定のロックでネクストキーロックを取れたけど、レコード指定で試してみたらギャップロックは取れたけどネクストキーロックは取れなかった...
このあたりちゃんと調べたい。
ちなみに共有ロックは
共有ロックは他のトランザクションのupdate, delete, 排他ロック取得を許容しない。
ただ、共有ロックが取得されたレコードの共有ロックを他のトランザクションでも取得することは可能。
今回は排他ロックの挙動を調べる回なので検証はしなかった。業務で必要になったら調べる。
参考にさせていただきました
MySQLのデフォルトトランザクション分離レベルはREPEATABLE READだけど… - Qiita
[RDBMS][SQL]トランザクション分離レベルについて極力分かりやすく解説 - Qiita
MySQL InnoDBのロックの挙動 - sambaiz-net
MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.6 InnoDB のレコード、ギャップ、およびネクストキーロック
