トランザクション分離レベルの勉強記録(6) INSERTの結果

* [検証全体のまとめ](http://qiita.com/noonworks/items/8f8fe3365e7c6f05bb25)
* [書き込みの検証手順と詳細な予測](http://qiita.com/noonworks/items/f22bf1f1f7a9a677f434)

## INSERTの結果

### READ UNCOMMITTED

```sql:Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.00 sec)
```

```text:innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31837196
Purge done for trx's n:o < 31836179 undo n:o < 0 state: running but idle
History list length 524
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31837195, ACTIVE 18 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x11b4, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31837195 lock mode IX
```

* `TABLE LOCK ... lock mode IX`: テーブルロック(IX)を取得済み


### READ COMMITTED

```sql:Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.00 sec)
```

```text:innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31837708
Purge done for trx's n:o < 31837203 undo n:o < 0 state: running but idle
History list length 527
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31837707, ACTIVE 3 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x13b8, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31837707 lock mode IX
```

* `TABLE LOCK ... lock mode IX`: テーブルロック(IX)を取得済み


### REPEATABLE READ

```sql:Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.05 sec)
```

```text:innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31838220
Purge done for trx's n:o < 31837715 undo n:o < 0 state: running but idle
History list length 530
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31838219, ACTIVE 23 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x129c, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31838219 lock mode IX
```

* `TABLE LOCK ... lock mode IX`: テーブルロック(IX)を取得済み


### SERIALIZABLE

```sql:Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.01 sec)
```

```text:innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31838732
Purge done for trx's n:o < 31838227 undo n:o < 0 state: running but idle
History list length 533
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31838731, ACTIVE 6 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x12c0, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31838731 lock mode IX
```

* `TABLE LOCK ... lock mode IX`: テーブルロック(IX)を取得済み


### 行ロックを取得していない？

どの分離レベルにおいても、**テーブルロック(IX)のみで、行ロックを取得していない。**予測外の結果だ！

でもロックしないわけがない。もしかして、**ロックのタイミングの問題なのでは？**と考え、以下の作業を追加した。

1. `Tx1`で書き込みSQLを実行後、
2. 新しいウィンドウを開きトランザクション`Tx2`を開始
3. `Tx2`で**ロック(S)付き読み取り**を試みる
4. `innodb_lock_monitor`を確認

```sql:Tx2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;
```

**`Tx2`のロック(S)読み取りは待機状態**になり、その間に取得した`innodb_lock_monitor`の情報が以下のようになった。

```text:innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31837709
Purge done for trx's n:o < 31837203 undo n:o < 0 state: running but idle
History list length 527
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31837708, ACTIVE 79 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x5f0, query id 16 localhost ::1 root statistics
SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE
------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5ce0b; asc       ;;
 2: len 7; hex 8b000001750110; asc     u  ;;
 3: len 4; hex 8000000a; asc     ;;

------------------
TABLE LOCK table `til`.`test` trx id 31837708 lock mode IS
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5ce0b; asc       ;;
 2: len 7; hex 8b000001750110; asc     u  ;;
 3: len 4; hex 8000000a; asc     ;;

---TRANSACTION 31837707, ACTIVE 203 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x13b8, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31837707 lock mode IX
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837707 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5ce0b; asc       ;;
 2: len 7; hex 8b000001750110; asc     u  ;;
 3: len 4; hex 8000000a; asc     ;;
```

（REPEATABLE READの試行の中で行った結果なので、`Tx1`のトランザクションIDが`31837707`、`Tx2`のトランザクションIDが`31837708`）

各トランザクションの情報の中で、**ロック解放待ちの情報**が先に表示され、その後ロックの情報が表示される。

まず`Tx2`（トランザクションID 31837708）のロック解放待ち情報。

```
------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5ce0b; asc       ;;
 2: len 7; hex 8b000001750110; asc     u  ;;
 3: len 4; hex 8000000a; asc     ;;
```

* `RECORD LOCKS ... lock mode S locks rec but not gap waiting`: 行ロックで、レコードロック(S)を待機中
  - `0: len 4; hex 80000001; asc     ;;`: `id = 1`のレコード

続いて、同じく`Tx2`のロック情報。

```
------------------
TABLE LOCK table `til`.`test` trx id 31837708 lock mode IS
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5ce0b; asc       ;;
 2: len 7; hex 8b000001750110; asc     u  ;;
 3: len 4; hex 8000000a; asc     ;;
```

* `TABLE LOCK ... lock mode IS`: テーブルロック(IS)を取得済み
* `RECORD LOCKS ... lock mode S locks rec but not gap waiting`: 行ロックで、レコードロック(S)を待機中
  - `0: len 4; hex 80000001; asc     ;;`: `id = 1`のレコード

次に、`Tx1`（トランザクションID 31837707）のロック情報。

```
TABLE LOCK table `til`.`test` trx id 31837707 lock mode IX
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837707 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5ce0b; asc       ;;
 2: len 7; hex 8b000001750110; asc     u  ;;
 3: len 4; hex 8000000a; asc     ;;
```

* `TABLE LOCK ... lock mode IX`: テーブルロック(IX)を取得済み
* `RECORD LOCKS ... lock_mode X locks rec but not gap`: 行ロックで、レコードロック(X)を取得済み
  - `0: len 4; hex 80000001; asc     ;;`: `id = 1`のレコード

**`Tx1`がいつの間にかレコードロック(X)を取得している。**


## INSERTのまとめ

* `INSERT`自体では、**テーブルロック(IX)を取得**する。
* その後、他のトランザクションによって該当レコードのロックが試みられたときには、すでに**挿入した行のレコードロック(X)を取得**している。
* 実質的には**【予測①-INSERT】の通り**といえる？