デッドロックとは、MySQL・PostgreSQLでの調べ方と防ぎ方

アプリのログに Deadlock found when trying to get lock; try restarting transaction や ERROR: deadlock detected が出た。この記事はそうしたデータベースのデッドロックについて、何が起きているのか、どう調べるのか、どう減らすのかをまとめたものです。MySQL (InnoDB) と PostgreSQL の両方を扱います。

デッドロックとは

デッドロックは、複数の処理がお互いに相手の持っているものを待ち合い、どれも先に進めなくなる状態のことです。OS やプログラミングの分野でも使う言葉ですが、この記事ではデータベースのデッドロックに絞ります。

データベースでは、トランザクションが行を更新するとき、その行に「ロック」をかけます。ロックがかかった行を別のトランザクションが更新しようとすると、先のトランザクションが終わるまで待たされます。ここまでは普通の「ロック待ち」です。

デッドロックになるのは、待ち合いが輪になったときです。いちばん単純な例を、口座の残高を移すトランザクション 2 つで示します。

順番	トランザクション A	トランザクション B
1	口座 1 の行を更新 (口座 1 をロック)
2		口座 2 の行を更新 (口座 2 をロック)
3	口座 2 の行を更新しようとして、B を待つ
4		口座 1 の行を更新しようとして、A を待つ

4 の時点で、A は B を待ち、B は A を待っています。どちらも相手が終わらない限り進めません。放っておけば永遠に終わらないので、データベースはこの輪を見つけると、どちらか一方のトランザクションを取り消して (ロールバックして) 輪をほどきます。取り消された側のアプリにはエラーが返り、もう一方はそのまま処理を続けます。

ロック待ちとデッドロックの違い

よく混同される 2 つを並べます。

	ロック待ち	デッドロック
状態	一方がもう一方を待っている	お互いに待ち合って輪になっている
待てば解決するか	相手が終われば進む	待っても解決しない
DB の扱い	タイムアウトまで待つ	検知してすぐ一方を取り消す
MySQL のエラー	1205 `Lock wait timeout exceeded`	1213 `Deadlock found when trying to get lock`
PostgreSQL	既定では待ち続ける	`deadlock detected` (SQLSTATE `40P01`)

デッドロックはデータベースがすぐに検知してほどくので、長時間の停止にはなりにくい障害です。一方、ロック待ちは相手が終わるまで続くので、長いトランザクションがあると多くの処理が止まります。ロック待ちの調べ方はロック待ちの調べ方で説明しています。

デッドロックはどのくらい問題か

MySQL のドキュメントは、デッドロックは危険なものではなく、再試行すればよいという立場です。同時に多くのトランザクションが動くデータベースでは、アプリが正しくてもデッドロックは起こりえます。そのため、アプリは「デッドロックで失敗したらトランザクションをやり直す」作りにしておく必要があります。

問題になるのは、次のような場合です。

アプリがリトライせず、エラーをそのまま利用者に返したり、データの取り込みを取りこぼしたりしている
件数が増え続けていて、リトライのたびに処理が遅くなっている
特定のバッチや画面で毎回のように起きている

ときどき起きて、リトライで吸収できているなら急ぐ必要はありません。増えているなら、原因になっている SQL の組み合わせを特定して直します。

MySQL でデッドロックを調べる

件数を確かめる

まず、どのくらいの頻度で起きているかを確かめます。InnoDB は起動してからのデッドロックの累計を数えています。

SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME = 'lock_deadlocks';

このカウンターはサーバーを再起動すると 0 に戻ります。1 時間ごとや 1 日ごとに値を記録しておき、差分を見ると増え方が分かります。

直近のデッドロックの詳細を見る

直近に起きたデッドロックの詳細は、SHOW ENGINE INNODB STATUS の LATEST DETECTED DEADLOCK の節に出ます。

SHOW ENGINE INNODB STATUS\G

出力は長いので、LATEST DETECTED DEADLOCK の節を探して読みます。読むところは次の 4 つです。

出てくる行	意味
`*** (1) TRANSACTION:` の下の SQL	トランザクション 1 が、検知の時点で実行していた SQL
`*** (1) HOLDS THE LOCK(S):`	トランザクション 1 が持っていたロック
`*** (1) WAITING FOR THIS LOCK TO BE GRANTED:`	トランザクション 1 が待っていたロック
`*** WE ROLL BACK TRANSACTION (2)`	InnoDB が取り消したトランザクション

ロックの行には、どのテーブルのどのインデックスかと、ロックの種類が出ます。

ロックの種類の表示	意味
`lock_mode X locks rec but not gap`	その行だけのロック (レコードロック)
`lock_mode X locks gap before rec`	行と行の「すき間」のロック (ギャップロック)
`lock_mode X`	行とその手前のすき間をまとめたロック (ネクストキーロック)
`... insert intention waiting`	INSERT しようとして、すき間のロックを待っている

読むときに気をつけたいのは、出てくる SQL が「検知の時点で実行していたもの」だけだという点です。ロックを取った SQL は、同じトランザクションの中でもっと前に実行されていることがよくあります。HOLDS THE LOCK(S) のロックをどの SQL が取ったのかは、アプリのコードを読んで突き合わせる必要があります。

インデックス名が PRIMARY ではなく別のインデックスになっていたり、gap や insert intention が出ていたりしたら、次の節で説明するギャップロックが関係している可能性が高いです。

すべてのデッドロックをログに残す

SHOW ENGINE INNODB STATUS に出るのは直近の 1 件だけです。何件も起きていると、前のものは上書きされます。すべてを残すには innodb_print_all_deadlocks を有効にします (既定は OFF)。

SET GLOBAL innodb_print_all_deadlocks = ON;

有効にすると、デッドロックのたびに同じ形式の詳細が MySQL のエラーログに書かれます。Amazon RDS ではパラメータグループで有効にします。エラーログはコンソールで読めるほか、CloudWatch Logs に送る設定もできます。MySQL のドキュメントは、調査が終わったら無効に戻すよう勧めています。

典型的なデッドロックのパターン

更新の順番が逆になっている

最初の表の例がこれです。同じ 2 つの行 (または 2 つのテーブル) を、あるトランザクションは A → B の順に、別のトランザクションは B → A の順に更新すると起こります。

在庫の引き当てで、注文に含まれる商品の行を注文の順番どおりに UPDATE している、というのがよくある形です。注文 1 が「商品 3、商品 7」、注文 2 が「商品 7、商品 3」の順に処理すると、同時に走ったときにデッドロックになります。

「なければ INSERT」でギャップロックが絡む

MySQL の InnoDB に特有で、分かりにくいのがこのパターンです。InnoDB の既定の分離レベル (REPEATABLE READ) では、SELECT ... FOR UPDATE で存在しない行を探すと、その行が入るはずの「すき間」にロックがかかります。このギャップロックは、複数のトランザクションが同じすき間に同時に持てます。

主キーに 10 と 20 の行だけがあるテーブルで、次の順に実行すると起こります。

順番	トランザクション A	トランザクション B
1	`SELECT * FROM t WHERE id = 15 FOR UPDATE;` (行はない。10〜20 のすき間をロック)
2		`SELECT * FROM t WHERE id = 16 FOR UPDATE;` (同じすき間をロック。衝突しない)
3	`INSERT INTO t (id) VALUES (15);` (B のギャップロックを待つ)
4		`INSERT INTO t (id) VALUES (16);` (A のギャップロックを待つ)

「行があるか確かめて、なければ作る」という処理や、SELECT MAX(id) ... FOR UPDATE で次の番号を採番する処理で起きやすい形です。SHOW ENGINE INNODB STATUS には locks gap before rec や insert intention waiting が出ます。

対策としては、存在の確認と INSERT を分けずに一意制約に任せて重複エラーを受ける、採番は AUTO_INCREMENT やシーケンスに任せる、といった方法があります。分離レベルを READ COMMITTED にすると、検索のときのギャップロックはかからなくなります (一意性のチェックと外部キーのチェックでは残ります)。ただし分離レベルの変更はほかの読み取りの結果にも影響するので、アプリ全体で問題がないか確かめてから行ってください。

インデックスがなく、ロックの範囲が広い

UPDATE や DELETE の WHERE に使える列にインデックスがないと、InnoDB はたくさんの行を読み、読んだ行にロックをかけます。結果として、本来ぶつからないはずのトランザクション同士がぶつかります。デッドロックの出力に出てくるテーブルで、更新の条件に合うインデックスがあるかを EXPLAIN で確かめてください。EXPLAIN の読み方は MySQL の EXPLAIN の見方を参照してください。

PostgreSQL でデッドロックを調べる

件数を確かめる

PostgreSQL では、データベースごとのデッドロックの累計が pg_stat_database にあります。

SELECT datname, deadlocks
FROM pg_stat_database
WHERE datname = current_database();

MySQL と同じく累計なので、定期的に記録して差分を見ます。

ログで詳細を見る

PostgreSQL がデッドロックを検知すると、取り消された側のセッションに次のようなエラーが返り、サーバーのログにも書かれます。

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
        Process 12346 waits for ShareLock on transaction 67891; blocked by process 12345.

DETAIL には、どのプロセスがどのプロセスを待っていたかが出ます。サーバーのログには、それぞれのプロセスが実行していた SQL も出るので、アプリに返るエラーよりもログの方が手がかりが多くなります。MySQL と同じく、出てくるのは検知の時点の SQL で、ロックを取った SQL はそれより前にあることが多い点に注意してください。

PostgreSQL は、ロックを deadlock_timeout (既定 1 秒) 待ったところでデッドロックかどうかを調べます。この値を大きくすると無駄な検査が減る代わりに、デッドロックのエラーが返るのが遅くなります。ふつうは変える必要はありません。

log_lock_waits を有効にすると (既定は off)、deadlock_timeout より長くロックを待ったセッションもログに書かれます。デッドロックの手前で起きている長いロック待ちが分かるので、調査のときに役立ちます。

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

PostgreSQL にはギャップロックがないので、前の節の「なければ INSERT」のパターンは同じ形では起きません。PostgreSQL で多いのは、更新の順番が逆になるパターンです。外部キーのある子テーブルへの INSERT が親の行にロックをかけることもあるので、出力に出てくるテーブルが親子関係にないかも見てください。

デッドロックを防ぐ・減らす

デッドロックをゼロにするのは難しいですが、減らすことはできます。MySQL と PostgreSQL のドキュメントが勧めている方法を、具体的な形にして並べます。

ロックを取る順番をそろえる

PostgreSQL のドキュメントが、デッドロックへのいちばんの備えとして挙げているのがこれです。複数の行を更新するときは、主キーの昇順に並べてから更新する、複数のテーブルを更新するときは、テーブルの順番をアプリ全体で決めておく、といったルールにします。在庫の例なら、注文の商品を商品 ID の順に並べ替えてから引き当てます。同じような更新の処理をあちこちに書かず、1 つの関数にまとめておくと順番を守りやすくなります。

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

ロックはトランザクションが終わるまで保持されます。外部 API の呼び出しや重い計算をトランザクションの中で行わず、DB の更新だけをまとめて、すぐにコミットします。

ロックをかける範囲を小さくする

更新の条件に合うインデックスを用意して、読む行を減らします。古いデータを読んでも困らない SELECT には、FOR UPDATE や FOR SHARE をつけないようにします。

最初から必要な強さのロックを取る

PostgreSQL のドキュメントは、トランザクションの中で同じ対象に最初にかけるロックを、あとで必要になるいちばん強いものにするよう勧めています。共有ロックで読んでから排他ロックに上げようとすると、2 つのトランザクションが同時にそれを行ったときにデッドロックになります。

アプリ側のリトライの書き方

上のことをしても、デッドロックは起こりえます。デッドロックで取り消されたトランザクションをやり直す処理を、アプリに入れておきます。

気をつけたいのは、やり直すのは失敗した SQL 1 つではなく、トランザクション全体だという点です。デッドロックで取り消されると、そのトランザクションの中で行った変更はすべて元に戻っています。失敗した SQL だけを再実行すると、前半の変更が抜け落ちます。

import random
import time

MAX_RETRIES = 3

def run_with_retry(conn, work):
    for attempt in range(1, MAX_RETRIES + 1):
        try:
            with conn.transaction():   # BEGIN 〜 COMMIT をまとめて行う
                return work(conn)      # トランザクションの中の処理をすべてやり直す
        except Exception as e:
            # MySQL はエラー番号 1213、PostgreSQL は SQLSTATE 40P01。
            # 取り出し方はドライバによって違います。
            if not is_deadlock(e) or attempt == MAX_RETRIES:
                raise
            time.sleep(random.uniform(0.05, 0.2) * attempt)

すぐにやり直すと同じ相手とまたぶつかることがあるので、少し待ってから再試行します。待ち時間に乱数を入れると、2 つのトランザクションが同じ間隔でぶつかり続けるのを避けられます。回数には上限を設け、上限に達したらエラーを返してログに残します。

リトライの前に、外部へのメール送信や決済の API 呼び出しのような、DB の外の副作用がトランザクションの中に入っていないかを確かめてください。やり直すと 2 回実行されてしまいます。

この確認を続けるには

デッドロックは、件数が増えたことに気づければ、原因の SQL を特定して直せます。lock_deadlocks や pg_stat_database.deadlocks を cron で定期的に記録し、前回からの増え方が大きければ通知する、監視ツールで同じ値を取ってアラートを設定する、といった方法で続けられます。リリースの直後やバッチの時間帯に増えることが多いので、増えた時刻と処理の予定を突き合わせられるようにしておくと調査が早くなります。

AI に任せる方法もあります。Scandock は、接続文字列を登録すると AI がデータベースの構成を読んで監視項目を決め、値の推移や過去の通知の結末を踏まえて異常かどうかを判断するサービスです。異常と判断すると、読み取り専用のクエリで関連するデータを調べ、原因・影響・対処案をつけて通知します。監視と調査は読み取り専用のユーザーで行います。

デッドロックとは、MySQL・PostgreSQLでの調べ方と防ぎ方