Zenn
アルダグラム Tech BlogPublicationへの投稿
💀

UPDATE IN SELECT によるデッドロックが発生しなくなった件

kasumi
2023/10/26に公開
MySQL
Ruby
tech

こんにちは。アルダグラムでエンジニアしている森下霞です。

弊社では、MySQL のデータベース と Ruby on Rails を使用しています。

先日、モニタリングで UPDATE IN SELECT のクエリでデッドロックの発生に気づき、調査し、修正ができたため、デッドロックのデバッグ方法と解決策を紹介したいと思います。

背景

今回の問題は、アニメサービスを例に使って説明します。アニメは以下のテーブルで保存します。

CREATE TABLE anime (
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    genre VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE INDEX index_anime_on_genre ON anime (genre);

テストデータを用意します。

INSERT INTO anime (title, genre, sort_order)
VALUES 
    ('title_1', 'genre_1', 3),
    ('title_2', 'genre_2', 3),
    ('title_3', 'genre_1', 0),
    ('title_4', 'genre_1', 1),
    ('title_5', 'genre_1', 2),
    ('title_6', 'genre_2', 1),
    ('title_7', 'genre_2', 2),
    ('title_8', 'genre_2', 0);

アニメはメインページでジャンルごとに最大の3本のアニメは順番で並んでいます。そのため、新しいアニメが追加される際、sort_order は更新されます。その処理でデッドロックが発生していました。

デッドロックが発生するクエリを特定する

まずは、デッドロックを引き起こすコードの RAW SQL を取り出してみます。

ソートの更新処理の rspec を実行し、SQL ログを確認しました。長いトランザクションの中で、いくつかの SELECT、INSERT、UPDATE が行っていました。

デッドロックを引き起こすのは、行ロックを取得しようとする DML クエリです。

  • UPDATE
  • INSERT
  • DELETE
  • SELECT FOR UPDATE

などです。

とりあえず、取り出した SQL から上記のクエリだけを残しました。

次は、ローカル環境でデッドロックの再現を試みました。二つのタブで DB に接続します。

tab_1　# mysql -u user -p
mysql_1> 

tab_2　# mysql -u user -p
mysql_2>

次、両方のタブにトランザクションを開きます。

mysql_1> BEGIN;
mysql_2> BEGIN;

二つのトランザクションのそれぞれで、取り出した SQLを実施してみます。

mysql_1> INSERT ...;
mysql_2> INSERT ...;

mysql_1> INSERT ...;
mysql_2> INSERT ...;

mysql_1> UPDATE ...;
mysql_2> UPDATE ...;

mysql_1> UPDATE ...;
mysql_2> UPDATE ...;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ロールバックします。

mysql_1> ROLLBACK;
mysql_2> ROLLBACK;

デッドロックの再現ができたので、次はデッドロックの原因となるクエリを抽出しました。
結果は、以下の通りです。

mysql_1> INSERT INTO anime (title, genre, sort_order)
    -> VALUES ('title_9', 'genre_2', 0);
Query OK

mysql_2> INSERT INTO anime (title, genre, sort_order)
    -> VALUES ('title_10', 'genre_1', 0);
Query OK

mysql_1> UPDATE anime
    ->　　　　　　  -- 順番を下げる
    ->     SET anime.sort_order = 0
    ->     WHERE anime.id IN (
    ->         SELECT id FROM (
    ->	　　　　         -- 古くなったアニメを取り出す
    ->             SELECT anime.id
    ->             FROM anime
    ->             WHERE anime.genre = 'genre_2' AND
    ->                   (sort_order > 0)
    ->             ORDER BY anime.sort_order DESC
    ->             OFFSET 3
    ->     ) AS __active_record_temp);
<待ち中>

mysql_2> UPDATE anime
    ->     SET anime.sort_order = 0
    ->     WHERE anime.id IN (
    ->         SELECT id FROM (
    ->             SELECT anime.id
    ->             FROM anime
    ->             WHERE anime.genre = 'genre_1' AND
    ->                   (sort_order > 0)
    ->             ORDER BY anime.sort_order DESC
    ->             OFFSET 3
    ->         ) AS __active_record_temp);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

デッドロックが発生する理由

おそらく、上記のクエリがデッドロックを引き起こした原因は、複数のトラザクションが異なる順番に同じ行にロックをかけようとしたためです。

具体的には、一方のトランザクションが行 A にロックをかけて、行 B にアクセスしようとしてロックをかけようとしました。その間に、もう一方のトランザクションがまずは行 B にロックをかけて、すでにロックされている行 A にアクセスしようとしてロックをかけようとしました。結果として、どちらのトランザクションも先に進むことができず、デッドロックが発生してしまいました。

実は、上記のクエリでデッドロックが起こると驚きました。一般的なデッドロックの例では、トランザクションは異なる順番で行にアクセスする場合をイメージしていました。

mysql_1> BEGIN;
mysql_2> BEGIN;

mysql_1> UPDATE anime SET sort_order = 1 WHERE title = 'title_1';
Query OK
mysql_2> UPDATE anime SET sort_order = 0 WHERE title = 'title_2';
Query OK

mysql_1> UPDATE anime SET sort_order = 0 WHERE title = 'title_2';
<待ち中>
mysql_2> UPDATE anime SET sort_order = 1 WHERE title = 'title_1';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql_1> ROLLBACK;
mysql_2> ROLLBACK;

ですが、上記の INSERT と UPDATE IN SELECT のクエリが更新しようとする行は被らないはずです。どうしてデッドロックになってしまいますか？

解決方法を考える

デッドロックが引き起こさないようにするのに、クエリを書き換えるのは一番良いでしょう。

ところが、上記のクエリを直す方法が最初はなかなか思いつかなく、別の方法を検討しました。

トランザクションを短くするか、無くす

注意：トランザクションを完全に無くすという選択肢は、データ整合性の問題につながります。じっくり検討してからこの選択肢にしましょう。

今回の INSERT と UPDATE の周りのトランザクションをなくせばいいのでしょうか。

ソートのアップデートは、新しいアニメが追加されたあとでも可能なので、解決方法としてはありと思います。

しかし、他の処理もいくつかがトランザクションに入っていたので、影響範囲が広そうで、できれば別の方法で行きたいと考えていました。

処理を１箇所でまとめて行う

デッドロックは、二つ以上のトランザクションが同じ行をロックする際に起こります。では、そもそもソートの処理を一つのジョブでまとめれば、UPDATE するところがただ１箇所になり、デッドロックが発生しなくなります。

ただし、この方法も大きめのリファクタリングが必要なので、調査を進みました。

名前付きロックをかける

MySQL では、行ロック以外に、テーブルロック、名前付きロックなどのロックがあります。

名前付きロックは、ある言葉にロックをかける方法です。今回のクエリを考えると、以下の形になります。

-- 処理の前
-- 100秒のタイムアウトでロックをかけようとする
SELECT GET_LOCK('update_anime_sort_order', 100); 
-- 処理の後
SELECT RELEASE_LOCK('update_anime_sort_order');

上記の２行を入れるだけで、トランザクションが必ず列に並んで、同時に同じ行にアクセスしなくなり、デッドロックが発生しなくなります。

ただし、RELEASE_LOCK を明確に行わない限り、トランザクションが終わっていても、ロックがかけてあるままに残ります。それで、他のトランザクションはソートに関わる処理を実行できなくなります。

MySQL のドキュメントによると、セッションが終わる際にロックもリリースされます。しかし、Ruby on Rails 上で connection pool がセッションを長く保管していることがあることに気づき、この方法はまだ検討が必要と判断しました。

参考：https://dev.mysql.com/doc/refman/8.0/ja/locking-functions.html

やっぱりDMLの順番をやり直してみる

もう一回クエリを詳しくみていきました。

ロックの状況を調べてみます。そのため、SHOW ENGINE INNODB STATUS のクエリを使います。SHOW ENGINE INNODB STATUS の結果は長いですが、TRANSACTIONS のセクションに注目します。

INSERT 後は、特に何もおかしくないと思いました。

mysql_1> INSERT INTO anime (title, genre, sort_order)
    -> VALUES ('title_9', 'genre_2', 0);
Query OK
mysql_2> INSERT INTO anime (title, genre, sort_order)
    -> VALUES ('title_10', 'genre_1', 0);
Query OK

mysql_1> SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 35474, ACTIVE 12 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 437, OS thread handle 278339835648, query id 111253 localhost user
---TRANSACTION 35473, ACTIVE 11 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 435, OS thread handle 278877787904, query id 111254 localhost user starting

UPDATE 中に、ロック待ちの状態が見れます。

mysql_1> UPDATE anime
    ->     SET anime.sort_order = 0
    ->     WHERE anime.id IN (
    ->         SELECT id FROM (
    ->             SELECT anime.id
    ->             FROM anime
    ->             WHERE anime.genre = 'genre_2' AND
    ->                   (sort_order > 0)
    ->             ORDER BY anime.sort_order DESC
    ->             OFFSET 3
    ->     ) AS __active_record_temp);
<待ち中>

mysql_2> SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 35474, ACTIVE 34 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 437, OS thread handle 278339835648, query id 111256 localhost user starting
---TRANSACTION 35473, ACTIVE 33 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1136, 11 row lock(s), undo log entries 1
MySQL thread id 435, OS thread handle 278877787904, query id 111255 localhost user Creating sort index
UPDATE anime 
    SET anime.sort_order = 0 
    WHERE anime.id IN (
        SELECT id FROM (
	    SELECT anime.id 
	    FROM anime 
	    WHERE anime.genre = 'genre_2' AND 
	        　　　(sort_order > 0) 
	    ORDER BY anime.sort_order DESC 
	    OFFSET 3
        ) AS __active_record_temp)
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1473 page no 3 n bits 88 index PRIMARY of table anime trx id 35473 lock mode S waiting

デッドロックの発生後に、最後のデッドロックの情報が見れます。

mysql_2> UPDATE anime
    ->     SET anime.sort_order = 0
    ->     WHERE anime.id IN (
    ->         SELECT id FROM (
    ->             SELECT anime.id
    ->             FROM anime
    ->             WHERE anime.genre = 'genre_1' AND
    ->                   (sort_order > 0)
    ->             ORDER BY anime.sort_order DESC
    ->             OFFSET 3
    ->         ) AS __active_record_temp);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql_2> SHOW ENGINE INNODB STATUS;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-25 05:50:42 0x40ce58d700
*** (1) TRANSACTION:
TRANSACTION 35473, ACTIVE 42 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 1
MySQL thread id 435, OS thread handle 278877787904, query id 111260 localhost user Creating sort index
UPDATE anime 
    SET anime.sort_order = 0 
    WHERE anime.id IN (
        SELECT id FROM (
            SELECT anime.id 
            FROM anime 
            WHERE anime.genre = 'genre_2' AND 
		　　　(sort_order > 0) 
	    ORDER BY anime.sort_order DESC 
            OFFSET 3
    ) AS __active_record_temp)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1473 page no 3 n bits 88 index PRIMARY of table anime trx id 35473 lock mode S waiting
<........>

*** (2) TRANSACTION:
TRANSACTION 35474, ACTIVE 43 sec starting index read
mysql tables in use 2, locked 2
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 437, OS thread handle 278339835648, query id 111261 localhost user updating
UPDATE anime 
    SET anime.sort_order = 0 
    WHERE anime.id IN (
        SELECT id FROM (
            SELECT anime.id 
            FROM anime 
            WHERE anime.genre = 'genre_1' AND 
		　　　(sort_order > 0) 
	    ORDER BY anime.sort_order DESC 
	    OFFSET 3
    ) AS __active_record_temp)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1473 page no 3 n bits 88 index PRIMARY of table anime trx id 35474 lock_mode X locks rec but not gap
<........>

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1473 page no 3 n bits 88 index PRIMARY of table anime trx id 35474 lock_mode X waiting
<........>

*** WE ROLL BACK TRANSACTION (2)

ロックの状態を見た結果、UPDATE クエリのロック対象行が実際に必要なものよりも多いと思いました。対象行は 4 行なのに、11 行にロックがかかっています。

それで、UPDATE クエリの EXPLAIN を見ていきます。

mysql> EXPLAIN 
       UPDATE anime 
       SET anime.sort_order = 0 
       WHERE anime.id IN (
            SELECT id FROM (
                SELECT anime.id 
                FROM anime 
                WHERE anime.genre = 'genre_2' AND 
		    　　　(sort_order > 0) 
		ORDER BY anime.sort_order DESC 
		OFFSET 3
       ) AS __active_record_temp);
+----+--------------------+------------+------------+----------------+----------------------+----------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type        | table      | partitions | type           | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                                              |
+----+--------------------+------------+------------+----------------+----------------------+----------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | UPDATE             | anime      | NULL       | index          | NULL                 | PRIMARY              | 4       | NULL  |   10 |   100.00 | Using where                                        |
|  2 | DEPENDENT SUBQUERY | <derived3> | NULL       | index_subquery | <auto_key0>          | <auto_key0>          | 4       | func  |    2 |   100.00 | Using index                                        |
|  3 | DERIVED            | anime      | NULL       | ref            | index_anime_on_genre | index_anime_on_genre | 403     | const |    4 |    33.33 | Using index condition; Using where; Using filesort |
+----+--------------------+------------+------------+----------------+----------------------+----------------------+---------+-------+------+----------+----------------------------------------------------+

rows の数字を見ます。10 です。

それで、デッドロック時に以下の状態が起こっているかと考えられます。

解決策

必要の 4 行だけにロックをかけるのに書き換えないかと考えて、色々試した結果、JOINに書き換えました。ロックの状態を見てみましょう。

mysql> EXPLAIN 
    　  　UPDATE anime 
       JOIN (
            SELECT anime.id 
	    FROM anime 
	    WHERE anime.genre = 'genre_2' AND 
	        　　　(sort_order > 0) 
	    ORDER BY anime.sort_order DESC
	    OFFSET 3
       ) anime_to_update
       ON anime_to_update.id = anime.id
       SET anime.sort_order = 0;

+----+-------------+------------+------------+--------+----------------------+----------------------+---------+--------------------+------+----------+-----------------------------+
| id | select_type | table      | partitions | type   | possible_keys        | key                  | key_len | ref                | rows | filtered | Extra                       |
+----+-------------+------------+------------+--------+----------------------+----------------------+---------+--------------------+------+----------+-----------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                 | NULL                 | NULL    | NULL               |    2 |   100.00 | NULL                        |
|  1 | UPDATE      | anime      | NULL       | eq_ref | PRIMARY              | PRIMARY              | 4       | anime_to_update.id |    1 |   100.00 | NULL                        |
|  2 | DERIVED     | anime      | NULL       | ref    | index_anime_on_genre | index_anime_on_genre | 403     | const              |    4 |    33.33 | Using where; Using filesort |
+----+-------------+------------+------------+--------+----------------------+----------------------+---------+--------------------+------+----------+-----------------------------+

4 行にしか関わりません！

デッドロックは？

mysql_1> UPDATE anime 
	 JOIN (
             SELECT anime.id 
	     FROM anime 
	     WHERE anime.genre = 'genre_2' AND 
	           (sort_order > 0) 
	     ORDER BY anime.sort_order DESC 
	     OFFSET 3
         ) anime_to_update
         ON anime_to_update.id = anime.id
         SET anime.sort_order = 0;
Query OK

mysql_2>UPDATE anime 
	 JOIN (
             SELECT anime.id 
	     FROM anime 
	     WHERE anime.genre = 'genre_1' AND 
	           (sort_order > 0) 
	     ORDER BY anime.sort_order DESC 
	     OFFSET 3
         ) anime_to_update
         ON anime_to_update.id = anime.id
         SET anime.sort_order = 0;
Query OK

デッドロックが解消されました！

ActiveRecord での上記のクエリについて

デッドロックが発生したクエリ、Rails 上で以下になっていました。

Anime.
  where(genre: genre).
  where('sort_order > 0').
  order(sort_order: :desc).
  offset(3).
  update_all(sort_order: 0)

JOIN に書き換え方はいくつかあります。

Raw SQL

subquery = Anime.
  　　        　　where(genre: genre).
	    where('sort_order > 0').
	    order(sort_order: :desc).
	    offset(3)

Anime.
    joins("INNER JOIN (#{subquery.to_sql}) AS anime_to_update ON anime.id = anime_to_update.id").
    update_all(sort_order: 0)

Arel

anime = Arel::Table.new(:anime)
anime_to_update = Arel::Table.new(:anime_to_update)

subquery = anime.
            project(anime[:id]).
            where(anime[:genre].eq(genre).
            and(anime[:sort_order].gt(0))).
            order(anime[:sort_order].desc).
            skip(3)

join_condition = anime[:id].eq(anime_to_update[:id])
join = anime.join(subquery.as('anime_to_update')).on(join_condition)

Anime.
    joins(join.join_sources).
    update_all(sort_order: 0)

今回、Arelが行数が多くてわかりづらいので、Raw SQLにしました。

結果・まとめ

アプリで DB にクエリを発行する際、デッドロックが起きないような設計を考えておきましょう。

できる限り、長いトランザクションを避けた方が良いでしょう。

ActiveRecord などの ORM でクエリを書く際、RAW SQL を確認し、多めのテストデータで EXPLAIN でパフォマンスや関わる行数を確認しましょう。

デッドロックが発生してしまった際、以下の手順でいきましょう。

  1. SHOW ENGINE INNODB STATUS の LATEST DETECTED DEADLOCK を調べる
  2. アプリから RAW SQL を取り出す
  3. 取得したSQLをローカル環境で二つのタブで実行してみる
  4. デッドロックが引き起こすクエリを特定する
  5. クエリの EXPLAIN と SHOW ENGINE INNODB STATUS を見る

解決方法としては、以下を考えられます。

  1. DML の順番をやり直す
  2. 名前付きロックをかける
  3. トランザクションを短くするか、無くす
  4. 処理を1箇所でまとめて行う

以上、この記事はデッドロックの問題に悩んでいる方に少しでも力になればと幸いです。

もっとアルダグラムエンジニア組織を知りたい人、ぜひ下記の情報をチェックしてみてください！

アルダグラム Tech Blog
アルダグラム Tech BlogPublication

株式会社アルダグラムのTech Blogです。 世界中のノンデスクワーク業界における現場の生産性アップを実現する現場DXサービス「KANNA」を開発しています。 採用情報はこちら herp.careers/v1/aldagram0508/

Discussion