【MySQL】innodb_lock_wait_timeoutはメタデータロック時にはタイムアウトしない。

`innodb_lock_wait_timeout`はMySQLの設定でデフォルト50秒で設定されている設定値である。AWSのRDSなどでデフォルトのパラメーターグループからいじらず50秒で運用しているところも多い気がしている。

この前この設定値に引っかかるエラーが発生した。

```
Lock wait timeout exceeded; try restarting transaction
```

しかし運用中にmetadata lockに引っかかって50秒以上かかっているクエリとかはよく見かける。ということは全てのロックに対しての設定値ではないらしい。

自分への備忘録も兼ねてロックの種類とタイムアウト制限に該当するロックの整理をしようと思います。


## 先に要約

- `innodb_lock_wait_timeout` は行ロック時にタイムアウト
- `lock_wait_timeout` はメタデータロック時にタイムアウト

innodb_lock_wait_timeout
> 行ロックが解除されるまで InnoDB トランザクションが待機する時間の長さ (秒単位) です。デフォルト値は 50 秒です。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

lock_wait_timeout
> この変数は、メタデータロックを取得するための試行のタイムアウトを秒単位で指定します。許可される値の範囲は 1 から 31536000 (1 年) です。デフォルトは 31536000 です。

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_lock_wait_timeout

## 行ロックとは何か?

行ロックと言いましたが、ここではinnodbのロックモードのことを指しています。

innodbのロックモードは大きく分けて`共有ロック`と`排他ロック`が存在します。
(厳密にいうとインテンションか否かでも分ける必要がある。[InnoDB のロックモード](https://dev.mysql.com/doc/refman/5.6/ja/innodb-lock-modes.html))

共有ロックは一般的に`select`などの参照系は許可するが`update`や`delete`などの更新系を許可しません。 `SELECT ~ LOCK IN SHARE MODE`などで指定をされます。
排他ロックは一般的に`select`の参照系及び`update`や`delete`も許可しない、という説明が多いです。`SELECT ~ FOR UPDATE`やupdateなどの更新系で指定されます。

ですが、`共有ロック`と`排他ロック`でどの程度まで参照と更新を許可するかはトランザクション分離レベルに依存します。
MySQLのデフォルトである`REPEATABLE READ`の場合は`排他ロック`がかかっている行に対しても他のトランザクションの参照を許可します。 [排他ロックの詳細](https://dev.mysql.com/doc/refman/5.6/ja/glossary.html#glos_exclusive_lock)

なので殆どの場合はロックが発生している場合は参照はできるが更新が許可されていない状態だと理解して問題なさそうです。
(`REPEATABLE READ`の場合ファントムリードを許可しているので、並列でトランザクションを実行している場合に更新前のデータが表示されることも少し頭に入れておいた方が良いかもしれません。)

## innodb_lock_wait_timeoutが行ロック時のタイムアウトをするか実験
`innodb_lock_wait_timeout=5`で設定して5秒でタイムアウトするかを実験。

```cnf:my.cnf
[mysqld]
innodb_lock_wait_timeout = 5
```

innodb_lock_wait_timeoutの確認方法

```
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 5     |
+--------------------------+-------+
1 row in set (0.01 sec)
```

トランザクションAで`id=1`に対してupdateをかけて排他ロックをかけてcommitしない状態にします。

```
START TRANSACTION;
update testuser set user_name = 'test' where id = 1;
```

トランザクションBで同時に`id=1`に対してupdateをかけると5秒でタイムアウトをします。

```
update testuser set user_name = 'warikomi' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #5秒後にtimeout
```

上記のことから `innodb_lock_wait_timeout`は行ロックに依存していることが分かりました。

なおロックの範囲はインデックスに依存をします。一般論で言えばupdate文を実行するときは、pkなどのユニークキーなどで実行をすることが好ましいです。

またlockの件数は以下のクエリで分かります。

```sql
select trx_rows_locked from information_schema.INNODB_TRX;
```

実行結果

```
+-----------------+
| trx_rows_locked |
+-----------------+
|               1 |
+-----------------+
```

また犯人さがしは以下のクエリを実行して、general.logと突き合わせをしましょう。

```sql
select t_b.trx_mysql_thread_id blocking_id,
         t_w.trx_mysql_thread_id requesting_id,
         p_b.HOST blocking_host,
         p_w.HOST requesting_host,
         l.lock_table lock_table,
         l.lock_index lock_index,
         l.lock_mode lock_mode,
         p_w.TIME seconds,
         p_b.INFO blocking_info,
         p_w.INFO requesting_info
  from information_schema.INNODB_LOCK_WAITS w,
       information_schema.INNODB_LOCKS l,
       information_schema.INNODB_TRX t_b,
       information_schema.INNODB_TRX t_w,
       information_schema.PROCESSLIST p_b,
       information_schema.PROCESSLIST p_w
  where w.blocking_lock_id = l.lock_id
    and w.blocking_trx_id = t_b.trx_id
    and w.requesting_trx_id = t_w.trx_id
    and t_b.trx_mysql_thread_id = p_b.ID
    and t_w.trx_mysql_thread_id = p_w.ID
  order by requesting_id,
           blocking_id;
```
実行結果

```
+-------------+---------------+---------------+-----------------+----------------------+------------+-----------+---------+---------------+---------------------------------------------------------+
| blocking_id | requesting_id | blocking_host | requesting_host | lock_table           | lock_index | lock_mode | seconds | blocking_info | requesting_info                                         |
+-------------+---------------+---------------+-----------------+----------------------+------------+-----------+---------+---------------+---------------------------------------------------------+
|           3 |             4 | localhost     | localhost       | `test`.`testuser`    | PRIMARY    | X         |       3 | NULL          | update testuser set user_name = 'warikomi' where id = 1 |
+-------------+---------------+---------------+-----------------+----------------------+------------+-----------+---------+---------------+---------------------------------------------------------+
```


## メタデータロックとは何か？

テーブルの定義情報に対してロックをかけている。基本的にはDDLが実行された時に トランザクションの一貫性や連続を担保するためにある仕組みです。
[メタデータのロック](https://dev.mysql.com/doc/refman/5.6/ja/metadata-locking.html)
metadata lockが散見される理由は順番にロックをかけて、途中でDDLによる排他ロックがかかり、それ以降の共有ロックが全て競合するからです。(私見)

私がよく開発環境等でみるパターンは、以下のパターンです。

### 1. 開発者が`testuser`に対して一生終わらないようなスロークエリを実行する。
この時に`testuser`に対してmetadata lockが設定される。(共有ロック)

```
mysql> select count(*) from testuser,testuser testuser2,testuser testuser3; #単純結合でo(N^3)
```

### 2. `testuser`に対して `alter table testuser rename testuser2;`などのDDLを実行する。
この時にmetadata lock(排他ロック)が設定されて競合が発生する。

```
alter table testuser rename testuser2; #実行結果が返ってこない
```

### 3. 2で実行したクエリが `Waiting for table metadata lock`で競合していることがわかる

`show processlist`で確認する。

```
mysql> show processlist;
+----+---------+-----------+---------+---------+------+---------------------------------+---------------------------------------------------------------------+
| Id | User    | Host      | db      | Command | Time | State                           | Info                                                                |
+----+---------+-----------+---------+---------+------+---------------------------------+---------------------------------------------------------------------+
|  2 | root    | localhost | NULL    | Query   |    0 | starting                        | show processlist                                                    |
|  3 | test    | localhost | test    | Query   |  117 | Sending data                    | select count(*) from testuser,testuser testuser2,testuser testuser3 |
|  4 | test    | localhost | test    | Query   |   12 | Waiting for table metadata lock | alter table testuser rename testuser2                               |
+----+---------+-----------+---------+---------+------+---------------------------------+---------------------------------------------------------------------+
```

### 4. `testuser`に対して単純な select文を発行する。 
この時に`testuser`に対してmetadata lockが設定される(共有ロック)ので、2のmetadata lockと競合する。

```
select * from testuser limit 1;
```

`show processlist`で確認すると `Waiting for table metadata lock`で待たされていることがわかる。

```
mysql> show processlist;
+----+---------+-----------+---------+---------+------+---------------------------------+---------------------------------------------------------------------+
| Id | User    | Host      | db      | Command | Time | State                           | Info                                                                |
+----+---------+-----------+---------+---------+------+---------------------------------+---------------------------------------------------------------------+
|  2 | root    | localhost | NULL    | Query   |    0 | starting                        | show processlist                                                    |
|  3 | test    | localhost | test    | Query   |  300 | Sending data                    | select count(*) from testuser,testuser testuser2,testuser testuser3 |
|  4 | test    | localhost | test    | Query   |  195 | Waiting for table metadata lock | alter table testuser rename testuser2                               |
|  6 | test    | localhost | test    | Query   |    4 | Waiting for table metadata lock | select * from testuser limit 1                                      |
+----+---------+-----------+---------+---------+------+---------------------------------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)
```

## lock_wait_timeoutがメタデータロック時のタイムアウトをするか実験

上の例ではデフォルトの31536000 (1 年) まで待ちますが、今回は実験として`lock_wait_timeout=5`で設定して5秒でタイムアウトするかを実験。

```cnf:my.cnf
[mysqld]
lock_wait_timeout = 5
```

lock_wait_timeoutの確認方法

```
mysql> show variables like 'lock_wait_timeout';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| lock_wait_timeout | 5     |
+-------------------+-------+
1 row in set (0.00 sec)
```

スロークエリを実行

```
mysql> select count(*) from testuser,testuser testuser2,testuser testuser3; #単純結合でo(N^3)
```

実行終了しないうちにDDLと単純な select文の実行。

DDLは5秒でタイムアウト

```
mysql> alter table testuser rename testuser2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction # 5秒でtimeout
```

DDLのあとに実行をすると、DDLがタイムアウトした時に競合が解消されるので、実行結果が返ってくる。

```
mysql> select * from testuser limit 1;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | warikomi  |
+----+-----------+
1 row in set (2.62 sec)
```

## まとめ

トランザクションとロックの関係性は各M/Wによって仕様が異なることが多いので、運用時には確認してみることをオススメします。
確認するときは少し面倒ですが、しっかり公式ドキュメントを見つつ自分でクエリを打つのがやはり王道な感じがします。