曖昧に理解してるかもと思い、自分の振り返りのために書いてます。
先日書いた記事で作ったデータで説明します。
MySQLのロック
通常、DML実行時に取得されるロックは排他ロックと共有ロックで構成されます。 最初にトランザクションでロックをかけたほうが排他ロック、 ロックがかかったデータを参照だけするためにかけたほうが共有ロックと呼ばれます。
共有ロックがかかった状態では他のトランザクションでも共有ロックはかかり（データの読み取りができ）ますが、データの書き込みはできません。
一方、他のトランザクションが同じ行をロックするのを回避するタイプのロックを排他ロックといいます。
トランザクション分離レベルに応じて、
となります。
MySQLで書き込み中データ読み取れると思ったでしょ？
そうです。
デフォルトの InnoDB 分離レベルである REPEATABLE READ では、排他ロックを持つ行をトランザクションが読み取ることを許可しています。（＝一貫性読み取りと呼ばれる方法）
ギャップロック
さて、本題に入ります。
通常データを書き込む時はそのロックの単位は行（row）で行われます。
それはMySQLでも変わらないのですが、MySQLではその行以外もロック対象になることがあります。
その一つがギャップロックです。
ギャップロックの特徴としては、
- index値を持つ行と行の間にあるギャップ
- 先頭のindex値を持つ行の前のギャップ
- 末尾のindex値を持つ行の後のギャップ
に対してロックをかけます。
なんのこっちゃと思われると思ったので、実際に試してみます。
一つ目のトランザクションでIDが1から6のレコードを抽出（for update付）しました。
この時点ではID＝5のレコードはありません。
mysql> begin; Query OK, 0 rows affected （0.00 sec） mysql> select * from test.j_pedigree where id between 1 and 6 for update; +----+--------------------+--------------------+-----------+-----------+-----------+---------+ | id | last_name | first_name | father_id | mother_id | pucci_flg | jap_flg | +----+--------------------+--------------------+-----------+-----------+-----------+---------+ | 1 | ジョージ（1世） | ジョースター | NULL | NULL | 0 | 0 | | 2 | メアリー | ジョースター | NULL | NULL | 0 | 0 | | 3 | ジョナサン | ジョースター | 1 | 2 | 0 | 0 | | 4 | エリナ | ジョースター | NULL | NULL | 0 | 0 | | 6 | ディオ | ブランドー | 5 | NULL | 0 | 0 | +----+--------------------+--------------------+-----------+-----------+-----------+---------+ 5 rows in set （0.00 sec）
二つ目のトランザクションでID＝5のレコードを追加してみます。
行単位でのロックであれば入るはずですが・・・、
mysql> begin ; Query OK, 0 rows affected （0.00 sec） mysql> insert into test.j_pedigree values(5,'ダリオ','ブランドー',null,null,0,0); ERROR 1205 （HY000）: Lock wait timeout exceeded; try restarting transaction
INSERTは待たされ、最終的にはタイムアウトしました。
1つ目のトランザクションでj_pedigree テーブルのイメージはこうなっています。
| id | データ有無 | ロック |
|---|---|---|
| 1 | あり | 排他ロック |
| 2 | あり | 排他ロック |
| 3 | あり | 排他ロック |
| 4 | あり | 排他ロック |
| 5 | 無し | 排他ロック |
| 6 | あり | 排他ロック |
データがない部分に対しても排他ロックがかかり、そこにデータを入れようとしてもロック待ちとなる。
これがギャップロックです。
フルテーブルスキャン時のロック
ちなみにフルテーブルスキャンになるようなトランザクションがかかった時、ロックはどうなっているのでしょう？（範囲は指定しています）
mysql> explain select * from test.j_pedigree where mother_id = 2 for update; +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | j_pedigree | NULL | ALL | NULL | NULL | NULL | NULL | 44 | 10.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning （0.00 sec） mysql> select * from test.j_pedigree where mother_id = 2 for update; +----+-----------------+--------------------+-----------+-----------+-----------+---------+ | id | last_name | first_name | father_id | mother_id | pucci_flg | jap_flg | +----+-----------------+--------------------+-----------+-----------+-----------+---------+ | 3 | ジョナサン | ジョースター | 1 | 2 | 0 | 0 | +----+-----------------+--------------------+-----------+-----------+-----------+---------+ 1 row in set （0.00 sec）
2つ目のトランザクションでデータを入れようとしても・・・
mysql> insert into test.j_pedigree values(5,'ダリオ','ブランドー',null,null,0,0); ERROR 1205 （HY000）: Lock wait timeout exceeded; try restarting transaction mysql> insert into test.j_pedigree values(0,'ロバート','スピードワゴン',null,null,0,0); ERROR 1205 （HY000）: Lock wait timeout exceeded; try restarting transaction mysql> insert into test.j_pedigree values(47,'羽','伴毅',null,null,0,0); ERROR 1205 （HY000）: Lock wait timeout exceeded; try restarting transaction
テーブル全体に排他ロックがかかり、データを一切入れることができません。
MySQL8からですが、performance_schema.data_locksでそれが確認できます。
mysql>select engine,object_schema,object_name,index_name,lock_type,lock_mode from performance_schema.data_locks; +--------+---------------+-------------+------------+-----------+-----------+ | engine | object_schema | object_name | index_name | lock_type | lock_mode | +--------+---------------+-------------+------------+-----------+-----------+ | INNODB | test | j_pedigree | NULL | TABLE | IX | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | PRIMARY | RECORD | X | | INNODB | test | j_pedigree | NULL | TABLE | IX | +--------+---------------+-------------+------------+-----------+-----------+ 47 rows in set （0.01 sec）
lock_mode がX/IXは排他ロックを意味しています。
ネクストキーロック
ネクストキーロックは先ほどのギャップロックと行ロックの組み合わせで、インデックスレコードに対するレコードロックと、そのインデックスレコードの前にあるギャップに対するギャップロックとを組み合わせたものです。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html
分かりづらいんで、これも実際に試してみましょう。
一つ目のトランザクション
mysql> begin; Query OK, 0 rows affected （0.00 sec） mysql> select * from test.j_pedigree where id < 5 for update; +----+--------------------+--------------------+-----------+-----------+-----------+---------+ | id | last_name | first_name | father_id | mother_id | pucci_flg | jap_flg | +----+--------------------+--------------------+-----------+-----------+-----------+---------+ | 1 | ジョージ（1世） | ジョースター | NULL | NULL | 0 | 0 | | 2 | メアリー | ジョースター | NULL | NULL | 0 | 0 | | 3 | ジョナサン | ジョースター | 1 | 2 | 0 | 0 | | 4 | エリナ | ジョースター | NULL | NULL | 0 | 0 | +----+--------------------+--------------------+-----------+-----------+-----------+---------+
IDが５未満なので4までを行ロックは取得しています。
これにギャップロックはその末尾のindex値を持つ行の後のギャップもロックを取るんで5もロックされるというわけです。
試してみます。
2つ目のトランザクション
mysql> insert into test.j_pedigree values(5,'ダリオ','ブランドー',null,null,0,0); ERROR 1205 （HY000）: Lock wait timeout exceeded; try restarting transaction
はい、やはり待たされてタイムアウトエラーとなりました。
じゃあ、IDが4未満だとどうなるの？
データは入ります。
1つ目のトランザクション
mysql> begin; Query OK, 0 rows affected （0.00 sec） mysql> select * from test.j_pedigree where id < 4 for update; +----+--------------------+--------------------+-----------+-----------+-----------+---------+ | id | last_name | first_name | father_id | mother_id | pucci_flg | jap_flg | +----+--------------------+--------------------+-----------+-----------+-----------+---------+ | 1 | ジョージ（1世） | ジョースター | NULL | NULL | 0 | 0 | | 2 | メアリー | ジョースター | NULL | NULL | 0 | 0 | | 3 | ジョナサン | ジョースター | 1 | 2 | 0 | 0 | +----+--------------------+--------------------+-----------+-----------+-----------+---------+ 3 rows in set （0.00 sec）
2つ目のトランザクション
mysql> insert into test.j_pedigree values(5,'ダリオ','ブランドー',null,null,0,0); Query OK, 1 row affected （0.00 sec）
ね？入ったでしょ。
IDは4未満なので3までが行ロックとなり、4がギャップロック対象なので5はロック対象外となり使用可能というわけです。
MySQLのロック
- テーブルロックと行（レコード）に対するロックがあります。
- ロックの強度は排他と共有の2種類があります（MySQL5.7からはその中間である共有排他（SX）ロックがありますがいったん置いときます）
- 行（row）に対するロックは次の3種類です。
これらを意識しておけば、デッドロックや更新のタイムアウトエラーが発生したときに「範囲じゃないはずなのになぁ…」というときでも「いや、待てよ？」という気づきになるかもしれませんので、MySQLを使われる方は頭の片隅に入れておいてください。
ギャップロックをMySQLで回避するには？
トランザクション分離レベルをREAD COMMITTEDに設定すればOKです。
トランザクション分離レベルは
- SET TRANSACTION構文を使って変更する
- mysqldから起動する際にオプションを指定する
- my.cnfに設定する
の三つがあります。
SET TRANSACTION構文で変更
グローバルとセッションで設定できます。
-- 設定 mysql> SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL READ COMMITTED; -- GLOBAL or SESSION
mysqld
CentOS7以降だと以下のようにsystemctlコマンドを使って起動します。
# systemctl set-environment MYSQLD_OPTS="--transaction-isolation=READ-COMMITTED" # systemctl restart mysqld.service
my.cnf
以下の一文をmy.cnfのmysqld配下に入れましょう
[mysqld] transaction-isolation = READ-COMMITTED
いずれも設定後は以下のクエリで確認するといいです。
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; #または mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
追記(降順インデックスの話)
この記事を書いた後にhmatsu47さんから教えていただいたんですが、
降順インデックスだとネクストキーロックの向きが逆になる、と。 なるほど。言われてみるとそうかも。
