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 秒です。
lock_wait_timeout
この変数は、メタデータロックを取得するための試行のタイムアウトを秒単位で指定します。許可される値の範囲は 1 から 31536000 (1 年) です。デフォルトは 31536000 です。
行ロックとは何か?
行ロックと言いましたが、ここではinnodbのロックモードのことを指しています。
innodbのロックモードは大きく分けて共有ロックと排他ロックが存在します。
(厳密にいうとインテンションか否かでも分ける必要がある。InnoDB のロックモード)
共有ロックは一般的にselectなどの参照系は許可するがupdateやdeleteなどの更新系を許可しません。 SELECT ~ LOCK IN SHARE MODEなどで指定をされます。
排他ロックは一般的にselectの参照系及びupdateやdeleteも許可しない、という説明が多いです。SELECT ~ FOR UPDATEやupdateなどの更新系で指定されます。
ですが、共有ロックと排他ロックでどの程度まで参照と更新を許可するかはトランザクション分離レベルに依存します。
MySQLのデフォルトであるREPEATABLE READの場合は排他ロックがかかっている行に対しても他のトランザクションの参照を許可します。 排他ロックの詳細
なので殆どの場合はロックが発生している場合は参照はできるが更新が許可されていない状態だと理解して問題なさそうです。
(REPEATABLE READの場合ファントムリードを許可しているので、並列でトランザクションを実行している場合に更新前のデータが表示されることも少し頭に入れておいた方が良いかもしれません。)
innodb_lock_wait_timeoutが行ロック時のタイムアウトをするか実験
innodb_lock_wait_timeout=5で設定して5秒でタイムアウトするかを実験。
[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の件数は以下のクエリで分かります。
select trx_rows_locked from information_schema.INNODB_TRX;
実行結果
+-----------------+
| trx_rows_locked |
+-----------------+
| 1 |
+-----------------+
また犯人さがしは以下のクエリを実行して、general.logと突き合わせをしましょう。
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が実行された時に トランザクションの一貫性や連続を担保するためにある仕組みです。
メタデータのロック
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秒でタイムアウトするかを実験。
[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によって仕様が異なることが多いので、運用時には確認してみることをオススメします。
確認するときは少し面倒ですが、しっかり公式ドキュメントを見つつ自分でクエリを打つのがやはり王道な感じがします。