【行ロック】JOINしたテーブルの行ロックを改めて整理する

最近業務にて障害調査を行なっていて、Joinしたテーブルにおける悲観ロックの挙動（mysql）を改めて整理したので記事にする。

調査した内容

とあるAPIの処理の中で、悲観ロックにてselect for update(行ロック)を用いてトランザクション制御をしているのだが、意図せず待ちが発生しタイムアウトエラーが起こってしまうという障害があった。
意図せず、というのは発行したSQLの取得対象に対してのみ行ロックをとっていたはずなのに、別の行を取得しようとする時にもロック解除待ちになってしまう、という意味。
発行しているSQLの中身を見ると複数のテーブルをJoinしており、その上で悲観ロックを行なっているので意図しない行も含めてロックをとってしまっていた、というのが結論だった。

Joinしたテーブルに対する悲観ロックの対象の理解が曖昧だったため、改めて整理した。
以下に簡単な具体例を示しながら悲観ロックの挙動を示す。
この辺りは理解しているようでできていない人が多いと思うので、理解を深められる良い機会になればと思う。

具体例

簡単に説明するために、2つのテーブルを用意する。
これらのテーブルをjoinし、悲観ロックにてレコード取得を行うこととする。

Productテーブル

項目名	変数名	型
商品ID	productId	int
商品名	productName	varchar(256)
価格	price	int

ProductOrderテーブル

項目名	変数名	型
注文ID	orderId	int
商品ID	productId	varchar(256)
注文金額	sum	int
購入者	userName	varchar(256)

create table Product (
    productId int,
    productName varchar(255),
    price int
);

-- インデックス貼らないと行ロックできないので
Alter table Product add index index_product_id(productId);

create table ProductOrder (
    orderId int,
    productId int, -- Product.productIdが入っている想定
    sum int,
    userName varchar(255)
);

-- インデックス貼らないと行ロックできないので
Alter table ProductOrder add index index_product_order(orderId);

insert into Product values (1, "リンゴ", 200);
insert into Product values (2, "ブドウ", 300);
insert into Product values (3, "イチゴ", 400);

insert into ProductOrder values (1, 1, 2, "もしも");
insert into ProductOrder values (2, 2, 2, "田中");
insert into ProductOrder values (3, 2, 1, "山田");
insert into ProductOrder values (4, 3, 10, "もしも");

試してみる

検証として2つのターミナルからそれぞれmysqlを立ち上げ、片方でselect for updateを行ったのちに、そのトランザクション内でもう一方からselect for updateを行った。

まずターミナルAにてselect for updateをかける。

※ProductOrderとProductをjoinし、orderId=2のレコードで行ロックをとる。

mysql> select * from ProductOrder as po left join Product as p on po.productId = p.productId where po.orderId = 2 for update;
+---------+-----------+------+----------+-----------+-------------+-------+
| orderId | productId | sum  | userName | productId | productName | price |
+---------+-----------+------+----------+-----------+-------------+-------+
|       2 |         2 |    2 | 田中     |         2 | ブドウ      | 300   |
+---------+-----------+------+----------+-----------+-------------+-------+
1 row in set (0.00 sec)

ちなみに二つのレコードをjoinした際の新テーブルは以下

mysql> select * from ProductOrder as po left join Product as p on po.productId = p.productId;
+---------+-----------+------+-----------+-----------+-------------+-------+
| orderId | productId | sum  | userName  | productId | productName | price |
+---------+-----------+------+-----------+-----------+-------------+-------+
|       1 |         1 |    2 | もしも    |         1 | リンゴ      | 200   |
|       2 |         2 |    2 | 田中      |         2 | ブドウ      | 300   |
|       3 |         2 |    1 | 山田      |         2 | ブドウ      | 300   |
|       4 |         3 |   10 | もしも    |         3 | イチゴ      | 400   |
+---------+-----------+------+-----------+-----------+-------------+-------+
4 rows in set (0.01 sec)

ターミナルBにて以下の取得を試みる

1.行ロックをかけたpo.orderId = 2で検索する

当然ロック解除待ちが起こるので取得できない。

mysql> select * from ProductOrder as po left join Product as p on po.productId = p.productId where po.orderId=2 for update;

// 返ってこない

2.行ロックをかけていない（想定）のpo.orderId = 4で検索する

mysql> select * from ProductOrder as po left join Product as p on po.productId = p.productId where po.orderId=4 for update;
+---------+-----------+------+-----------+-----------+-------------+-------+
| orderId | productId | sum  | userName  | productId | productName | price |
+---------+-----------+------+-----------+-----------+-------------+-------+
|       4 |         3 |   10 | もしも    |         3 | イチゴ      | 400   |
+---------+-----------+------+-----------+-----------+-------------+-------+
1 row in set (0.01 sec)

// 返ってきた。つまり、この行は行ロックの対象じゃない。

3.行ロックをかけたレコードと同じproductId = 2を含むpo.orderId = 3で検索する

mysql> select * from ProductOrder as po left join Product as p on po.productId = p.productId where po.orderId=3 for update;

// 返ってこない。なぜ？？

orderId = 3は今回のロック対象ではないのに、なぜロック待ちになるのか。
分かりますか？

検証からわかること

改めてだが、今回行ロックをとったSQL文は以下になる。

mysql> select * from ProductOrder as po left join Product as p on po.productId = p.productId where po.orderId = 2 for update;
+---------+-----------+------+----------+-----------+-------------+-------+
| orderId | productId | sum  | userName | productId | productName | price |
+---------+-----------+------+----------+-----------+-------------+-------+
|       2 |         2 |    2 | 田中     |         2 | ブドウ      | 300   |
+---------+-----------+------+----------+-----------+-------------+-------+
1 row in set (0.00 sec)

これはProductテーブル、ProductOrderテーブルとしては以下の行がロックされている、と言える。

• ProductOrderテーブル
  検索条件がpo.orderId = 2であるように、orderId=2の行がロックされる。

• Productテーブル
  検索したレコードはproductId=2を持っている。
  つまり、ProductテーブルとしてはproductId=2のレコードがロックされる。

結合したテーブルにおいては、productId=2を持つレコードはロック対象となる。
つまり、検索条件をpo.orderId=2にしているためにpo.orderId=3のレコードがロックされないと思いがちだが、実際には検索条件で取得するレコードの値を含む他の行もロック対象になる、ということ。

※検索条件にないorderId=3の行もロックの対象となるよ、ということ

まとめ

今回はjoinしたテーブルにおける行ロックの挙動を解説した。
今回の具体例だと簡単なSQLなのでわかりやすいが、現場では複雑なSQLに対して悲観ロックを行うケースもあると思う。
行ロック関連で立ち止まることがあれば、上記のようにSQLを紐解いてロックされる対象を見極めることが重要だと改めて感じた。