🐙

テーブルが空の状態での意図しないロック発生について

2023/02/05に公開

前回、MySQLのトランザクション分離レベルについてまとめるでまとめた後に「テーブルが空の状態でロック取得するとどうなる？」という疑問が生まれたので、実際に事象再現して見ていきたいと思います。

環境

# mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

テーブルだけ作って、レコードは0件の状態にします。

CREATE TABLE IF NOT EXISTS lockdb.products
(
	id                INT         NOT NULL PRIMARY KEY,
	name              VARCHAR(50) NOT NULL,
	amount            INT         NOT NULL
);

トランザクション分離レベルは、デフォルトのままです。

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.01 sec)

動作確認

ここからはターミナルA,Bを用意して、手元でSQL流して動作を見ていきます。

まずはAで排他ロックを獲得します。
レコードが0件なのでインデックススキャンの結果は0件です。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id FROM products WHERE id = 1 FOR UPDATE;
Empty set (0.00 sec)

次にBでproductsテーブルにデータを挿入します。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO products VALUES(1, "apple", 50);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ロック解除待ちでタイムアウトしました。
Aのスキャン結果は0件なのに誰がロックしているのでしょうか🤔

誰がロックしているか確認するためにロック状況を見たいと思います。

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
|       116 | products    | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|       116 | products    | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
2 rows in set (0.00 sec)

LOCK_TYPE=TABLEとなっているものがあります。
つまり、誰かがテーブルロックをしているのがわかります。
ロックを獲得している誰かとはAのことで、Aの空振りのインデックススキャン+FOR UPDATEの結果がテーブルロックを引き起こしていることがわかります。

ではAのトランザクションを終了してロックを解放します。

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
Empty set (0.00 sec)

テーブルロックがなくなったようなので、再度Bでデータを挿入します。

mysql> INSERT INTO products VALUES(1, "apple", 50);
Query OK, 1 row affected (0.00 sec)

mysql> select * from products;
+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  1 | apple |     50 |
+----+-------+--------+
1 row in set (0.00 sec)

今度は成功しました。

なぜ、テーブルロックが起こるのか?

MySQLのREPEATABLE-READにはインデックスとインデックス間にロックする仕組み(ギャップロック)があります。
今回はテーブル全体にインデックスがないので、インデックス間にロックをかけようとするとテーブルロックになってしまうと考えるのが自然なのかもしれないです。

回避するには?

ロック取得をからぶらないように実装する
アプリでリトライする
分離レベルを変える

この3つの方法が考えられます。

最後に

今回、SELECT ... FOR UPDATE -> INSERT INTO ...という処理を実装していた時になぜか並行で実行すると、ごく稀に発生する事象に直面したため、原因追及した結果になります。

そして結論として、データが0件のテーブルに対してロックをかけるとテーブルロックが発生するという知見が得られました。

環境

動作確認

なぜ、テーブルロックが起こるのか?

回避するには?

最後に

Discussion