【MySQL】SELECT MAX(id) FOR UPDATEでID採番すると同時実行でデッドロックする理由を調べてみた
はじめに
IDをSELECT MAX(id) FOR UPDATEで採番した場合、並列実行でデッドロックが発生することがわかりました。
デッドロックといえば、二つのテーブルに対して異なる順序でロックを取る場合に発生するものだという知識はあったものの、同じ手順を同時に実行しただけでなぜデッドロックが発生するのか直感的にわかりませんでした。
そこで、どのような仕組みでデッドロックが発生するのか調べてみました。
前提
- DBはMySQL 8.4.11
- トランザクション分離レベルはデフォルト（REPEATABLE READ）
- SQLクライアント（DBeaver）を使用した検証
準備
検証に必要なテーブルを作成し、データを登録します。IDと名前だけのシンプルなテーブルです。
CREATE TABLE zz_demo (id int NOT NULL, name varchar(20), PRIMARY KEY(id));
INSERT INTO zz_demo VALUES (1,'a'),(2,'b'),(3,'c');
デッドロックの発生手順
DBeaverでSQLエディタを二つ開きます。念のために以下のクエリを両方のエディタで実行し、セッションが別であることを確認します。
SELECT CONNECTION_ID();
私の環境では、それぞれ4024と3994が返ってきました。以下、この二つをセッションA、セッションBと呼びます。
まず、セッションAで以下を実行します。
BEGIN;
SELECT MAX(id) FROM zz_demo FOR UPDATE;
3が返ってきます。
続いてセッションBでも上記のクエリを実行します。すると待機中になります。
この状態でセッションAに戻り、以下のクエリを実行します。
INSERT INTO zz_demo (id, name) VALUES (4, 'd');
実行すると以下のエラーが発生します。
SQLエラー [1213] [40001]: Deadlock found when trying to get lock; try restarting transaction
手順を表にまとめます。
| セッションA | セッションB | 結果 | |
|---|---|---|---|
| 1 | BEGIN; SELECT MAX(id) FROM zz_demo FOR UPDATE; |
IDの最大値（3）が返る |
|
| 2 | BEGIN; SELECT MAX(id) FROM zz_demo FOR UPDATE; |
待機中となる | |
| 3 | INSERT INTO zz_demo (id, name) VALUES (4, 'd'); | デッドロック発生 |
なぜ発生したか
まず、MySQLのロックの種類を整理します。
- テーブルレベルのロック
- インテンションロック
- 「これからレコードに対するロックを取得する」意図を示すためのロック
- テーブルロックを取得する側が全レコードをチェックして行ロックの有無を確認しなくて良くなる
- インテンションロック
- レコードレベルのロック
- レコードロック
- 取得しようとするレコードに対するロック
- 例えば
WHERE id = 10とした場合に、idが10の行に対して他のトランザクションの操作を制御
- ギャップロック
- レコードとレコードの間の隙間（ギャップ）に対するロック
- 例えば
WHERE id BETWEEN 1 AND 10とした場合に、1から10の間の隙間をロック - ファントムリード（他のトランザクションが範囲内に新しい行をINSERTすることで、同じクエリを二回実行した際に結果が変わる現象）を防ぐ
- ネクストキーロック
- レコードロックと、そのレコードの直前のギャップに対するギャップロック
- 例えばidが1、5、10のレコードがあるテーブルに対して
WHERE id = 10とした場合、idが10の行および5から10の間の隙間をロック
- レコードロック
ギャップロックは、InnoDB特有のロックです。今回デッドロックが発生したのも、このロックが原因でした。
各操作により、どのようなロックが取得されるのかをまとめます。
| セッションA | セッションB | 取得するロック | |
|---|---|---|---|
| 1 | BEGIN; SELECT MAX(id) FROM zz_demo FOR UPDATE; |
・最大値（id = 3）に対するレコードロック ・最大値レコードとsupremum（テーブル末尾の仮想レコード）の間のギャップロック |
|
| 2 | BEGIN; SELECT MAX(id) FROM zz_demo FOR UPDATE; |
・最大値レコードとsupremum（テーブル末尾の仮想レコード）の間のギャップロック ⇒ギャップロックは共存可能なため取得できる ・最大値（id = 3）に対するレコードロックはセッションAが保有するためブロックされる |
|
| 3 | INSERT INTO zz_demo (id, name) VALUES (4, 'd'); | ・挿入インテンションロックを取得しようとするが、セッションBがギャップロックを保持しているためブロックされる |
セッションBはセッションAが保有するレコードロックを待機し、セッションAはセッションBが保有するギャップロックを待機するため、デッドロックが発生します。
詳細なトランザクションの状況は以下のクエリで確認できます。
SHOW ENGINE INNODB STATUS
以下のような結果が表示されます。(2)がセッションA、(1)がセッションBとなっています。この番号は単純に識別用の番号なので、順番を示しているわけではありません。
*** (1) TRANSACTION:
TRANSACTION 11781, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 3994, OS thread handle 126420756932160, query id 9807 192.168.127.1 spdm_local optimizing
/* ApplicationName=DBeaver 25.1.3 - SQLEditor <Script-63.sql> */ SELECT MAX(id) FROM zz_demo FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table dev_spdm.zz_demo trx id 11781 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table dev_spdm.zz_demo trx id 11781 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000002e01; asc . ;;
2: len 7; hex 82000000d6012a; asc *;;
3: len 1; hex 63; asc c;;
*** (2) TRANSACTION:
TRANSACTION 11780, ACTIVE 132 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 4024, OS thread handle 126420887082560, query id 9811 192.168.127.1 spdm_local update
/* ApplicationName=DBeaver 25.1.3 - SQLEditor <Script-62.sql> */ INSERT INTO zz_demo (id, name) VALUES (4, 'd')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table dev_spdm.zz_demo trx id 11780 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000002e01; asc . ;;
2: len 7; hex 82000000d6012a; asc *;;
3: len 1; hex 63; asc c;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table dev_spdm.zz_demo trx id 11780 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
(2) HOLDS THE LOCK(S)のセクションで、セッションAがレコードロックおよびギャップロックを取得できていることがわかります。
以下の行にsupremumと表示されています。supremumは末尾の仮想レコードなので、実質的にギャップロックです。
0: len 8; hex 73757072656d756d; asc supremum;;
レコードロックは以下の部分です。
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000002e01; asc . ;;
2: len 7; hex 82000000d6012a; asc *;;
3: len 1; hex 63; asc c;;
(1) HOLDS THE LOCK(S)のセクションで、セッションBも同様にギャップロックを取得できていることがわかります。
0: len 8; hex 73757072656d756d; asc supremum;;
そして、(1) WAITING FOR THIS LOCK TO BE GRANTEDのセクションで、セッションBがレコードロックを待機していることがわかります。
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 120 page no 4 n bits 72 index PRIMARY of table dev_spdm.zz_demo trx id 11781 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000002e01; asc . ;;
2: len 7; hex 82000000d6012a; asc *;;
3: len 1; hex 63; asc c;;
補足：ID採番の方法について
上記の通り、SELECT MAX(id) FOR UPDATE方式ではデッドロックが発生することがわかりました。IDを安全に採番するには、以下のような方法があります。
(1) AUTO_INCREMENTを設定
テーブル定義の変更が可能であれば、最もシンプルな方法です。IDの採番を手動でする必要がなく、INSERT時に自動で採番されます。
- メリット
- 同時実行でも重複しない
- アプリケーション側に採番ロジックが不要
- デメリット
- データ移行時に既存の
AUTO_INCREMENTの値との競合を考慮する必要がある innodb_autoinc_lock_modeの設定でロックの挙動が変わりうる
- データ移行時に既存の
(2) カウンター用テーブルを作成
AUTO_INCREMENTを設定できない場合の代替手段です。MySQL公式ドキュメントでは、以下のパターンが紹介されています。
-- シーケンステーブル作成
CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);
-- シーケンス番号生成
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
- メリット
LAST_INSERT_ID()はコネクション単位で値を保持するため、同時実行しても他セッションが採番した値を取得することがない- 公式ドキュメントに記載された実装パターンであり、実績・信頼性がある
- デメリット
- 専用テーブルの管理が必要になる
- カウンター行が常に更新対象になるため、高頻度アクセス時にボトルネックになりうる
(3) GET_LOCK('name')によるアドバイザリーロック
アドバイザリーロックとは、DBが自動的に管理するロックとは異なり、アプリケーションが明示的に取得や解放を指示するロックです。
SELECT MAX(id) FOR UPDATE方式は、テーブルの行やギャップに対してロックをかけるため、意図しない広い範囲がロックされるという問題がありました。GET_LOCK('name')では、「nameという名前を誰か一人だけが取得できる」という状態を作ります。
- メリット
- DBのレコードロックやギャップロックに依存せず、任意の粒度でロック制御が可能
- ロック名は任意の文字列なので、用途ごとに独立したロックを作れる
- デメリット
- トランザクションと連動しないため、ロールバックしてもロックは自動解放されず、
RELEASE_LOCK()やtry-finallyでの確実な解放処理が必要 - コネクションが切断されるまでロックが保持されるため、アプリケーションのクラッシュ等でロック解放漏れが発生するリスクがある
- 通常のDBロックと異なり、MySQLのデッドロック検知の対象外となる
- トランザクションと連動しないため、ロールバックしてもロックは自動解放されず、
(4) 連番ではなくUUIDなどを使用
連番ではなく、UUIDを使用して一意性を担保する方法もあります。
- メリット
- 採番のための中央管理が不要となり、分散環境でも競合なくID生成が可能
- アプリケーション側で生成できるため、DBへの往復が減る
- デメリット
- 可読性が低い
- UUID v4はランダム性が高いため、B-Treeインデックスの挿入位置が分散し、ページ分割が頻発してINSERT性能が低下する可能性がある
- UUID v7など時系列でソート可能なUUIDを使用することでこれを軽減できます
- インデックスサイズが大きくなり、ストレージ・メモリ効率が悪化する
連番を使用したい場合、
- テーブル定義が変更できるのであれば、
AUTO_INCREMENT AUTO_INCREMENTが使用できない場合はMySQL公式にあるシーケンス採番
をまずは検討することをお勧めします。
おわりに
MySQLの経験がなかったため、最大値レコードを取得することでレコードロックとギャップロックの両方が取得されるという点は知りませんでした。
トランザクションの詳細を調査し、なぜ同じ操作を同時に実行しただけでデッドロックが発生するのかという点もわかりました。
「同じクエリを同時実行しただけでデッドロックが起きる」というのは直感に反する現象ですが、InnoDBの内部でどのようなロックが取得されているかをSHOW ENGINE INNODB STATUSで確認することで、原因の特定がしやすくなると感じました。同様の事象に遭遇した際は、まずロックの詳細を確認することをお勧めします。
この記事がどなたかの参考になれば幸いです。
