データベースを使っていてロックの存在を知らない方はいないと思いますが、実際にプログラムからデータベースを操作する時に明示的にロックを意識することはほとんどありません。
たいていの場合、トランザクションだけ気をつけていたらDBMSが適切にロックを取得＆開放してくれます。
私もデッドロックなどロック関連の障害が発生した場合に調査のために学習するのですが、対応が終わってしばらくしたら細かい仕様は忘れてしまいますw
毎回忘れるのはもったいないので、ドキュメントを読み直して自分なりにまとめておこうと思い、この記事を書くことにしました。
当初、1つの記事にまとめようと思いましたが、書いているうちにボリュームが大きくなってしまったので下記に分割することにしました。
この記事は「ロックを確認するためのテーブルや設定」です。
- ロックを確認するためのテーブルや設定 (この記事)
- テーブルレベルロック
- 行レベルロック: 共有ロック(S) / 排他ロック(X)
- 行レベルロック: インテンションロック
- 行レベルロック: レコードロック / ギャップロック / ネクストキーロック / 他
MySQLバージョン
執筆時点(2021年9月)の最新バージョン、8.0.26を利用します。
検証方法
今回の検証はmysqlコマンドを使って行います。
トランザクションを開始するためにBEGINコマンドを使います。
トランザクションはCOMMIT or ROLLBACKを実行するまで継続されます。
ロックを確認するためのテーブルたち
ロック情報を確認するために使えるテーブルがいくつか用意されています。
今回は下記の3つを使います。
- data_locks
- metadata_locks
- processlist
data_locks
こちらは現在保持されているロック情報を取得できます。
例えば、hogesテーブルのid=1のレコードを排他ロック(X)した場合は下記のように取得できます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from hoges where id = 1 for update;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 1 | 1 | 1 | hoge1 | 2021-09-22 07:42:03.146892 | 2021-09-22 07:42:03.146892 |
+----+---------+-----+-------+----------------------------+----------------------------+
1 row in set (0.00 sec)
mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+---------------+-------------+-----------+
| hoges | TABLE | IX | GRANTED | NULL |
| hoges | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
カラム名や値からなんとなく意味が理解できると思いますが、data_locksの2つ目のレコードがhogesテーブルのid=1(LOCK_DATE)のレコードを排他ロック(X)していることを示しています。
このテーブルはとても見やすくて使い勝手が良いのですが、テーブルレベルロックは取得できないという欠点があります。
下記の通りテーブルロックしているのですが、data_locksには何も表示されません。
mysql> LOCK TABLES hoges WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
Empty set (0.00 sec)
テーブルレベルロックを確認するために他のテーブルを使います。
metadata_locks
こちらはメタデータのロックを取得できます。テーブルロックはこちらで確認します。
mysql> LOCK TABLES hoges WRITE;
Query OK, 0 rows affected (0.00 sec)
select OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID, OWNER_EVENT_ID from performance_schema.metadata_locks where OBJECT_SCHEMA = 'app_development' AND OBJECT_TYPE='TABLE';
+-------------+----------------------+-------------+-----------------+----------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+----------------------+-------------+-----------------+----------------+
| hoges | SHARED_NO_READ_WRITE | GRANTED | 89 | 39 |
+-------------+----------------------+-------------+-----------------+----------------+
1 rows in set (0.00 sec)
上記から、hogesに対してはSHARED_NO_READ_WRITEのロックを取得していることがわかります。
LOCK_TYPEは様々なタイプがあるようですが、各タイプの説明が公式ドキュメントに見当たらないので名前や挙動から推測しています。
processlist
こちらはスレッドで現在実行されているコマンドを確認することができます。
ロック待ちが発生しているか？などを確認することができます。
例えば、テーブルロックによりupdate fugas set name = 'name11' where id = 1が待ち状態になっている場合は下記のように出力されます。
mysql> select ID, COMMAND, STATE, INFO from performance_schema.processlist where id = 19;
+----+---------+---------------------------------+-----------------------------------------------+
| ID | COMMAND | STATE | INFO |
+----+---------+---------------------------------+-----------------------------------------------+
| 19 | Query | Waiting for table metadata lock | update fugas set name = 'name11' where id = 1 |
+----+---------+---------------------------------+-----------------------------------------------+
1 row in set (0.01 sec)
ロックタイムアウト時間の設定
動作検証をするとき、ロックされていることをタイムアウトすることで確認します。
そのため、タイムアウトまでの時間を確認しておきます。
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'lock_wait_timeout';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set (0.01 sec)
innodb_lock_wait_timeoutはレコードレベルのロックで待たされている時のタイムアウト時間でデフォルト50秒です。
テーブルレベルのロックの場合はlock_wait_timeoutが参照されます。31536000秒という巨大な時間が設定されています。
検証の時に長時間タイムアウト待ちするのは面倒なので両方とも1秒に設定します。
mysql> SET innodb_lock_wait_timeout=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set lock_wait_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 1 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'lock_wait_timeout';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| lock_wait_timeout | 1 |
+-------------------+-------+
1 row in set (0.00 sec)
次回
ロックを確認するためのテーブルや設定が確認できたので、次の記事では「テーブルレベルロック」を検証していきます。