トランザクション分離レベルの勉強記録(5) 書き込みの検証手順と詳細な予測

* [検証全体のまとめ](http://qiita.com/noonworks/items/8f8fe3365e7c6f05bb25)

「予測①　書き込みは、どの分離レベルでも同様に排他ロックを取得する」の検証手順と、詳細な予測。

## 検証手順

**各トランザクション分離レベルで、トランザクション内の書き込み操作が排他ロックを取得しているか**を確認する。

1. ターミナルを開き、`SET SESSION TRANSACTION ISOLATION LEVEL`を設定する
2. トランザクション`Tx1`を開始する
3. `Tx1`で書き込みSQLを実行する
4. `innodb_lock_monitor`を確認する
   - `Tx1`が排他ロックを取得しているか？
   - その範囲は？
5. `Tx1`を`ROLLBACK`する
6. 1～5の繰り返し

```sql:準備
CREATE DATABASE TIL;
USE TIL;
CREATE TABLE test (
  id INTEGER NOT NULL DEFAULT 0,
  a INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);
INSERT INTO test (id, a) VALUES (2, 20);
INSERT INTO test (id, a) VALUES (3, 30);
INSERT INTO test (id, a) VALUES (6, 30);
INSERT INTO test (id, a) VALUES (7, 10);
```

上記SQLでテーブル`test.TIL`に以下の4行が準備される。

|  id  |  a  |
|:----:|:---:|
|   2  |  20 |
|   3  |  30 |
|   6  |  30 |
|   7  |  10 |

```sql:innodb_lock_monitorの有効化・無効化
/* 有効化 */
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
/* 無効化 */
DROP TABLE innodb_lock_monitor;
```

※`innodb_lock_monitor`の出力はかなりの量になるので、途中でMySQLをサーバーを停止して`mysql_error.log`ファイルをリネームする等、適宜対応する。

```sql:トランザクション分離レベルの変更（セッション単位）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```

```sql:トランザクション分離レベルの確認
SHOW VARIABLES LIKE 'tx_isolation';
```



## 検証に使用するSQLと予測

### INSERT

```sql:INSERT_1
INSERT INTO test (id, a) VALUES (1, 10);
```

【予測①-INSERT】
どの分離レベルにおいても挿入した行のレコードロック(X)を取得する。


### UPDATE

```sql:UPDATE_1
UPDATE test SET a = 200 WHERE id = 2;
```
```sql:UPDATE_2
UPDATE test SET a = 200 WHERE id = 2 OR id = 6;
```
```sql:UPDATE_3
UPDATE test SET a = 200 WHERE id = 2 OR id = 5;
```
```sql:UPDATE_4
UPDATE test SET a = 200 WHERE id = 2 OR id = 9;
```

* `UPDATE_1`や`UPDATE_2`だけを見れば、実際に更新する行のレコードロック(X)で十分である
* `UPDATE_3`や`UPDATE_4`のような場合、**存在しないレコードはロックできない**
  - **他のトランザクションによって、条件に該当するレコードが挿入される**可能性がある（そしてその行は更新されない）
  - 少なくともSERIALIZABLEはこれを許可しない
  - **`id >= （条件の値）`となる最初のレコードのネクストキーロック(X)**を取得すれば挿入を防げる

【予測①-UPDATE_1-4】
SERIALIZABLEは**実際に更新するレコードのネクストキーロック(X)**と、**`id >= （条件の値）`となる最初のレコードのネクストキーロック(X)**を取得する。他の分離レベルもおそらく同じ。
弊害として**WHERE句の条件に該当しないのに挿入できなくなる**レコードがある。

* `UPDATE_3`のロックによって`id = 4`のレコードも挿入できなくなる
* `UPDATE_4`のロックによって`id = 8`および`id > 9`のレコードも挿入できなくなる



```sql:UPDATE_5
UPDATE test SET a = 200 WHERE id <= 2;
```
```sql:UPDATE_6
UPDATE test SET a = 200 WHERE id <= 4;
```
```sql:UPDATE_7
UPDATE test SET a = 200 WHERE id <= 9;
```

* `UPDATE_5`だけを見れば、実際に更新する行のネクストキーロック(X)で十分である
* `UPDATE_6`や`UPDATE_7`のような場合、**存在しないレコードはロックできない**
  - **他のトランザクションによって、条件に該当するレコードが挿入される**可能性がある（そしてその行は更新されない）
  - 少なくともSERIALIZABLEはこれを許可しない
  - **`id >= （条件の値）`となる最初のレコードのネクストキーロック(X)**を取得すれば挿入を防げる

【予測①-UPDATE_5-7】
SERIALIZABLEは**実際に更新するレコードのネクストキーロック(X)**と、**`id >= （条件の値）`となる最初のレコードのネクストキーロック(X)**を取得する。他の分離レベルもおそらく同じ。
弊害として**WHERE句の条件に該当しないのに挿入できなくなる**レコードがある。

* `UPDATE_6`のロックによって`id = 5`のレコードも挿入できなくなる
* `UPDATE_7`のロックによって`id > 9`のレコードも挿入できなくなる



```sql:UPDATE_8
UPDATE test SET a = 200 WHERE id >= 2;
```
```sql:UPDATE_9
UPDATE test SET a = 200 WHERE id >= 5;
```
```sql:UPDATE_10
UPDATE test SET a = 200 WHERE id >= 9;
```

* 実際に更新する行のネクストキーロック(X)だけだと
  - **他のトランザクションによって、条件に該当するレコードが挿入される**可能性がある（そしてその行は更新されない）
  - 少なくともSERIALIZABLEはこれを許可しない
  - **`supremum`のネクストキーロック(X)**を取得すれば挿入を防げる

【予測①-UPDATE_8-10】
SERIALIZABLEは**実際に更新するレコードのネクストキーロック(X)**と、**`supremum`のネクストキーロック(X)**を取得する。他の分離レベルもおそらく同じ。
弊害として**WHERE句の条件に該当しないのに挿入できなくなる**レコードがある。

* `UPDATE_9`のロックによって`id = 4`のレコードも挿入できなくなる
* `UPDATE_10`のロックによって`id = 8`のレコードも挿入できなくなる



```sql:UPDATE_11
UPDATE test SET a = 300 WHERE a = 30;
```
```sql:UPDATE_12
UPDATE test SET a = 300 WHERE a = 100;
```

* 検索条件である`a`にインデックスがないので、**全レコードをスキャン**することになる
* レコードロック(X)だけだと
  - **他のトランザクションによって、条件に該当するレコードが挿入される**可能性がある（そしてその行は更新されない）
  - 少なくともSERIALIZABLEはこれを許可しない
  - **ネクストキーロック(X)**を取得すれば挿入を防げる
* レコードすべてをロックする？もしくは、実際に更新するレコードだけをロックする？
  - 実際に更新する行のロックだけだと**他のトランザクションによって、条件に該当しなかったレコードが更新され、条件に該当するようになる**可能性がある（そしてその行は更新されない）
  - 少なくともSERIALIZABLEはこれを許可しない
  - **スキャンしたレコードすべてのロック**を取得すれば防げる

【予測①-UPDATE_11-12】
SERIALIZABLEは**全行のネクストキーロック(X)**と**`supremum`のネクストキーロック(X)**を取得する。他の分離レベルもおそらく同じ。
弊害として**WHERE句の条件に該当しなくも、すべてのレコードが挿入および更新できなくなる**。


### DELETE

```sql:DELETE_1
DELETE FROM test WHERE id = 2;
```
```sql:DELETE_2
DELETE FROM test WHERE id = 2 OR id = 6;
```
```sql:DELETE_3
DELETE FROM test WHERE id = 2 OR id = 5;
```
```sql:DELETE_4
DELETE FROM test WHERE id = 2 OR id = 9;
```
```sql:DELETE_5
DELETE FROM test WHERE id <= 2;
```
```sql:DELETE_6
DELETE FROM test WHERE id <= 4;
```
```sql:DELETE_7
DELETE FROM test WHERE id <= 9;
```
```sql:DELETE_8
DELETE FROM test WHERE id >= 2;
```
```sql:DELETE_9
DELETE FROM test WHERE id >= 5;
```
```sql:DELETE_10
DELETE FROM test WHERE id >= 9;
```
```sql:DELETE_11
DELETE FROM test WHERE a = 30;
```
```sql:DELETE_12
DELETE FROM test WHERE a = 100;
```

【予測①-DELETE】
`UPDATE`の対応する番号と同じ。



## 結果へのリンク

* [`INSERT`の結果](http://qiita.com/noonworks/items/3733d76405892b598323)