MySQLのトランザクション分離レベル・レコードロック・ギャップロック・ネクストキーロック

## 概要
- MySQLのロックトランザクション分離レベルと、ロックの種類について挙動がわからなかったため実際に動かしてみて確認してみた


## MySQLのトランザクション分離レベル
- MYSQLのトランザクション分離レベルは、`REPEATABLE READ` デフォルトとなっている
  - ORACLEやPosgreSQLでは `READ COMMITTED` 
  - トランザクションでの処理時は、読み取り・変更時ともにロックがかかる


> SQL:1992 のトランザクション分離レベルに関しては、デフォルトの InnoDB レベルは REPEATABLE READ です。InnoDB では、SQL 標準に記載された 4 つのトランザクション分離レベル (READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE) がすべて提供されます。

- https://dev.mysql.com/doc/refman/5.6/ja/innodb-transaction-model.html


### MySQLのファントムリードの回避
- トランザクション分離レベルが `REPEATABLE READ` では通常ファントムリードが発生してしまう。
- ファントムリードを防ぐには、トランザクション分離レベルを `SERIALIZABLE` に設定して、テーブルロックを行う必要がある
    - テーブルロックを行ってしまうと、ロック解除待ちの処理が発生する可能性が増えてしまい、処理速度が遅くなる可能性が増える
- MySQLではトランザクション分離レベルが `REPEATABLE READ` でありながら、テーブルロックをおこなわずに、ファントムリードを回避できる仕組みがある


- https://dev.mysql.com/doc/refman/5.6/ja/innodb-next-key-locking.html

## ロックの種類
- https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.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'),
```

### レコードロック
- レコード（インデックスレコード）を更新しようとする際に、ロックが発生することが確認できた

```sql
mysql> begin;
mysql> update users set name = 'first' where between id = 5;
```

```sql
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）に対してロックが発生する

```sql
mysql> begin;
mysql> update users set name = 'first' where id between 9 and 11;
```

```sql
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 以降のレコードを挿入しようとすると、ネクストキーロックが発生している

```sql
mysql> begin;
mysql> update users set name = 'first' where id > 19;
```

```sql
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 に変更した場合に、ギャップロック・ネクストキーロックが発生するかどうか確認してみる

```sql
mysql> begin;
mysql> update users set name = 'first' where id > 19;
```
 
```sql
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)
```

## 参考
- https://blog.kamipo.net/entry/2013/12/03/235900
- https://dev.mysql.com/doc/refman/5.6/ja/innodb-next-key-locking.html
- https://github.com/ichirin2501/doc/blob/master/innodb.md
- https://softwarenote.info/p1067/