概要
- MySQLのロックトランザクション分離レベルと、ロックの種類について挙動がわからなかったため実際に動かしてみて確認してみた
MySQLのトランザクション分離レベル
- MYSQLのトランザクション分離レベルは、
REPEATABLE READデフォルトとなっている- ORACLEやPosgreSQLでは
READ COMMITTED - トランザクションでの処理時は、読み取り・変更時ともにロックがかかる
- ORACLEやPosgreSQLでは
SQL:1992 のトランザクション分離レベルに関しては、デフォルトの InnoDB レベルは REPEATABLE READ です。InnoDB では、SQL 標準に記載された 4 つのトランザクション分離レベル (READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE) がすべて提供されます。
MySQLのファントムリードの回避
-
トランザクション分離レベルが
REPEATABLE READでは通常ファントムリードが発生してしまう。 -
ファントムリードを防ぐには、トランザクション分離レベルを
SERIALIZABLEに設定して、テーブルロックを行う必要がある- テーブルロックを行ってしまうと、ロック解除待ちの処理が発生する可能性が増えてしまい、処理速度が遅くなる可能性が増える
-
MySQLではトランザクション分離レベルが
REPEATABLE READでありながら、テーブルロックをおこなわずに、ファントムリードを回避できる仕組みがある -
https://dev.mysql.com/doc/refman/5.6/ja/innodb-next-key-locking.html
ロックの種類
レコードロック（インデックスロック）
- 実際はレコードに対するロックではなく、インデックスレコードに対するロック
ギャップロック
- インデックスの行の前後にあるギャップ（インデックス間）のロック
- ギャップにロックをかけることで、インサートによるファントムリードが発生することを防止している
- トランザクション分離レベルが
REPEATABLE READの時にのみロックがかかる
ネクストキーロック
- レコードロックと、インデックスロックを組み合わせたもの
- トランザクション分離レベルが
REPEATABLE READ時のみロックがかかる
ロックの挙動を確認してしてみる
テストデータの作成
- 主キーであるidを 5,10,15,20 とした4レコードを作成
- それぞれのレコード（インデックス）にはギャップが存在している
- 例）5~10,10~15,15~20
mysql> create database sample character set utf8mb4;
mysql> create table users (id int auto_increment primary key, name varchar(40));
mysql> insert into users values
(5, 'a'),
(10, 'b'),
(15, 'c'),
(20, 'd'),
レコードロック
- レコード（インデックスレコード）を更新しようとする際に、ロックが発生することが確認できた
mysql> begin;
mysql> update users set name = 'first' where between id = 5;
mysql> begin;
mysql> update users set name = 'second' where id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ギャップロック
- 範囲指定を行うことで、インデックスレコード間のギャップ（今回の場合はid 5~10 と 10~15）に対してロックが発生する
mysql> begin;
mysql> update users set name = 'first' where id between 9 and 11;
mysql> begin;
-- ロックがかかる
mysql> insert into users values (14, 'second');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- ロックがかかる
mysql> insert into users values (6, 'second');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- ロックはかからない
mysql> insert into users values (16, 'second');
Query OK, 1 row affected (0.00 sec)
-- ロックはかからない
mysql> insert into users values (4, 'second');
Query OK, 1 row affected (0.00 sec)
ネクストキーロック
- id 20 以降のレコードを挿入しようとすると、ネクストキーロックが発生している
mysql> begin;
mysql> update users set name = 'first' where id > 19;
mysql> begin;
-- ネクストキーロック
mysql> insert into users values (21, 'second');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- インデックスロック
mysql> update users set name = 'second' where id = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- ギャップロック
mysql> insert into users values (18, 'second');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- ロックはかからない
mysql> insert into users values (14, 'second');
Query OK, 1 row affected (0.00 sec)
ISOLATION LEVEL を READ COMMITTED に変更 してみる
- ISOLATION LEVEL を READ COMMITTED に変更した場合に、ギャップロック・ネクストキーロックが発生するかどうか確認してみる
mysql> begin;
mysql> update users set name = 'first' where id > 19;
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> begin;
-- ネクストキーロックは発生しない
mysql> insert into users values (21, 'second');
Query OK, 1 row affected (0.00 sec)
-- インデックスロックの発生
mysql> update users set name = 'second' where id = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- ギャップロックは発生しない
mysql> insert into users values (18, 'second');
Query OK, 1 row affected (0.00 sec)