SELECT ... LOCK IN SHARE MODEとSELECT ... FOR UPDATEの違い

MySQLドキュメントの[14.2.5 ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)](https://dev.mysql.com/doc/refman/5.6/ja/innodb-locking-reads.html)の記述は、以下のように読める。

* `SELECT ... LOCK IN SHARE MODE`は共有ロックを取得する
* `SELECT ... FOR UPDATE`は排他ロックを取得する

今回はこれを`innodb_lock_monitor`を使って確認する。
[`innodb_lock_monitor`の読み方は過去に調べた通り。](http://qiita.com/noonworks/items/dbb0e0c2f38637763549)

**（※追記）検証環境はMySQLのデフォルトである`REPEATABLE READ`。**

```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 (1, 10);

/* enable innodb_lock_monitor */
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
```

※以下の検証中、各トランザクションは`innodb_lock_monitor`データ取得後に`ROLLBACK`している。


## SELECT ... LOCK IN SHARE MODE

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

```text:TRANSACTIONのみ抜粋
---TRANSACTION 31830058, ACTIVE 4 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 1, OS thread handle 0xde4, query id 19 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31830058 lock mode IS
RECORD LOCKS space id 42754 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31830058 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5b021; asc      !;;
 2: len 7; hex 98000001580110; asc     X  ;;
 3: len 4; hex 8000000a; asc     ;;
```

`lock mode S locks rec but not gap` なので、共有ロックを取得している。


## SELECT ... FOR UPDATE

```sql
mysql> START TRANSACTION;
mysql> SELECT * FROM test WHERE id = 1 FOR UPDATE;
```

```text:TRANSACTIONのみ抜粋
---TRANSACTION 31830059, ACTIVE 12 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 1, OS thread handle 0xde4, query id 22 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31830059 lock mode IX
RECORD LOCKS space id 42754 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31830059 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5b021; asc      !;;
 2: len 7; hex 98000001580110; asc     X  ;;
 3: len 4; hex 8000000a; asc     ;;
```

`lock_mode X locks rec but not gap`なので、排他ロックを取得している。


## 非ロック読み取り

```sql
mysql> START TRANSACTION;
mysql> SELECT * FROM test WHERE id = 1;
```

```text:TRANSACTIONのみ抜粋
---TRANSACTION 31830057, ACTIVE 20 sec
MySQL thread id 1, OS thread handle 0xde4, query id 16 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31830058, sees < 31830058
```

ロックは取得していない。


## 書き込み

```sql
mysql> START TRANSACTION;
mysql> UPDATE test SET a = 110 WHERE id = 1;
```

```text:TRANSACTIONのみ抜粋
---TRANSACTION 31830060, ACTIVE 8 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0xde4, query id 25 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31830060 lock mode IX
RECORD LOCKS space id 42754 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31830060 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5b02c; asc      ,;;
 2: len 7; hex 220000029208d1; asc "      ;;
 3: len 4; hex 8000006e; asc    n;;
```

`lock_mode X locks rec but not gap`なので、排他ロックを取得している。


## まとめ

**（※追記）REPEATABLE READ でのまとめ**

<table>
<thead><tr><th colspan="2">操作</th><th>取得するロック</th></tr></thead>
<tbody>
<tr><td colspan="2">非ロック読み取り（Non-Locking Read）</td><td>なし</td></tr>
<tr><td rowspan="2">ロック読み取り（Locking Read）</td><td>SELECT ... LOCK IN SHARE MODE</td><td>共有ロック（S）</td></tr>
<tr><td>SELECT ... FOR UPDATE</td><td>排他ロック（X）</td></tr>
<tr><td colspan="2">書き込み</td><td>排他ロック（X）</td></tr>
</tbody>
</table>


## 感想

`innodb_lock_monitor`を使うと動作がしっかり確認できてよい。